transfer Data From Acces: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

How do I allow an end user make modifications to a rdl that I created using Report Designe

Hi,
I have several reports ( .rdl) that I created using report designer, how can
I let the end user make modifications to the text in some of these reports.
As I am unable to use Report Builder to modify reports ( .rdl ) created with
Report designer, what are my options. is there a way I can embed the report
designer within my c# winform application. I have tried converting/creating
a report builder version but with no success..
Thanks in advance..On Mar 2, 3:09 pm, "Rob Dob" <robdob20012...@.yahoo.com> wrote:
> Hi,
> I have several reports ( .rdl) that I created using report designer, how can
> I let the end user make modifications to the text in some of these reports.
> As I am unable to use Report Builder to modify reports ( .rdl ) created with
> Report designer, what are my options. is there a way I can embed the report
> designer within my c# winform application. I have tried converting/creating
> a report builder version but with no success..
> Thanks in advance..
Hi there...if you are running SSRS 2005 I would make the report using
the report builder if at all possible then give the user rights to
change it. There probably are other ways of doing this but I think
this might be the easiest...|||On Mar 2, 5:21 pm, "sullins602" <ben.sull...@.gmail.com> wrote:
> On Mar 2, 3:09 pm, "Rob Dob" <robdob20012...@.yahoo.com> wrote:
> > Hi,
> > I have several reports ( .rdl) that I created using report designer, how can
> > I let the end user make modifications to the text in some of these reports.
> > As I am unable to use Report Builder to modify reports ( .rdl ) created with
> > Report designer, what are my options. is there a way I can embed the report
> > designer within my c# winform application. I have tried converting/creating
> > a report builder version but with no success..
> > Thanks in advance..
> Hi there...if you are running SSRS 2005 I would make the report using
> the report builder if at all possible then give the user rights to
> change it. There probably are other ways of doing this but I think
> this might be the easiest...
Also, depending on the text that needs to be changed, you could allow
certain text report parameters to be filled in by the user and use the
parameter results to set the text in the report.
Regards,
Enrique Martinez
Sr. SQL Server Developer

How do i add my database to "data sources"?

In Lesson 9 as you can see here (at 16:50), a connection is created against his .mdf database, and the dataset is shown in the "Data Source" window/panel.
He can then simply drag the data source onto the form, and controls are created and bound automaticly - or drag them as a datagridview to the form.

That is what i want to do, but i have some problems...

I have set up a connection to my SQL Express database, and a dataset is created without problems. The problem is that this dataset does not show up in "Data Source". Actually, i have never seen anything in that panel before, in any of the data sources i have added!

What am i doing wrong? In the menu i click "Data" --> Add new data source --> finish the "wizard". Then i click "Data" --> Show Data sources --> It's EMPTY!

Does this also happen if you just connect to a regular database, not through .mdf file? When i tried it with regular database it works, but although "Data Source" panel is not empty, it contains only DataSources names which are empty inside and not possible to drag onto designer. Is it what you mean by "empty"? If so, then you can click on "Server Explorer" and drag and drop your dataset from there. Then your data sources are going to get populated also.|||

I am trying to connect to a regular database, stored on a different computer running MSSQL Express. Connection and everything is okay.

By "empty", i mean completely empty - theres nothing in the panel - no items at all.

See this screenshot:

http://dritbra.com/temp/datasource.png

As you can see on the screenshot, the items appears as soon as something else than the form designer is active...

Btw, when viewing the server explorer i can't drag the items to the form... The mouse has this "not allowed" icon.

|||So theres really no one who can even confirm that the data set is supposed to appear in the data source panel, available for "drag and drop"?|||

Ok, this appears to be a known issue - probably occuring because i have had a beta installed (allthough i have completely reinstalled final version two times on this computer).

SOLUTION:

OPEN:
C:\Documents and Settings\<UserName>\Application Data\Microsoft\VisualStudio\8.0\DataBindingSetting\WinFormControls.xml

ADD THIS:

How do i add my database to "data sources"?

That is what i want to do, but i have some problems...

What am i doing wrong? In the menu i click "Data" --> Add new data source --> finish the "wizard". Then i click "Data" --> Show Data sources --> It's EMPTY!

I am trying to connect to a regular database, stored on a different computer running MSSQL Express. Connection and everything is okay.

By "empty", i mean completely empty - theres nothing in the panel - no items at all.

See this screenshot:

http://dritbra.com/temp/datasource.png

As you can see on the screenshot, the items appears as soon as something else than the form designer is active...

Btw, when viewing the server explorer i can't drag the items to the form... The mouse has this "not allowed" icon.

|||So theres really no one who can even confirm that the data set is supposed to appear in the data source panel, available for "drag and drop"?|||

Ok, this appears to be a known issue - probably occuring because i have had a beta installed (allthough i have completely reinstalled final version two times on this computer).

SOLUTION:

OPEN:
C:\Documents and Settings\<UserName>\Application Data\Microsoft\VisualStudio\8.0\DataBindingSetting\WinFormControls.xml

ADD THIS:

How do I add a single column heading row in the detail section?

Could somebody please tell me how to do this?
I've created a report with the wizard that lists Managers. You toggle
a Manager and a detail section of employees opens for that Manager. I
want to put a row of column names at the top of the detail section when
it's opened (not showing when the detail is closed). I've succeeded in
adding a row that shows above each detail record, but that's not what I
want. I just want a single row of column headings.
I've tried looking everywhere, including the Help system, and I can't
find how to do this. Probably simple, right?
Thanks, JulieHi Julie,
Highlight the Managers group, right click and select Insert Row Below.
That will add another row for that group, but since the group only gets
repeated once for each manager this is what you want. Toggle this new
row on the Manager and you're good to go.
take care,
Michelle|||Michelle, thank you, that's what I've been trying to do. That just
leaves me to figure out how to hide this new detail header when the
Manager group is not toggled. Setting the visibility of the detail
header to False makes it invisible all the time. I want it to become
visible when the Manager is toggled, and only for any Manager(s)
toggled.
Michelle wrote:
> Hi Julie,
> Highlight the Managers group, right click and select Insert Row
Below.
> That will add another row for that group, but since the group only
gets
> repeated once for each manager this is what you want. Toggle this
new
> row on the Manager and you're good to go.
> take care,
> Michellesql

Friday, March 23, 2012

How could this have happened ??

Hi everybody!

Something strange happened to one of my SQL Databases.
One of my User Defined Funcitions has lost ALL its roles permissions!

I created this UDF a long time ago and I assigned permissions to some specific Roles to run the UDF. It worked fine until today. All of a sudden there were no permissions for any role for that UDF. I am the only Admin for the DB.

I reassigned the roles permissions and it seems to be working fine now.
How could this happen ? has anyone here experienced something like this before ?

Thanks!Basic questions - How often is it accessed ? Have you noticed any other settings modified - I am thinking of sql injection here - it is a remote possibility that someone could have breached your system. Have you done any replication/copy database/copy objects between other servers ?|||Originally posted by rnealejr
Basic questions - How often is it accessed ? Have you noticed any other settings modified - I am thinking of sql injection here - it is a remote possibility that someone could have breached your system. Have you done any replication/copy database/copy objects between other servers ?

It is accessed on a daily basis (about 25 users). Intranet users only (no external logins). Also, no replication/db copy between other servers.|||Why don't you post the udf...|||You are sure you are the only admin? Try running

sp_helpsrvrolemember sysadmin
and
sp_helpsrvrolemember securityadmin

If any other names show up in these, you may have a problem. Also check on sp_helpdb (your database) who is listed as the owner of the database? And check sp_helprolemember db_owner as well. Any of these folks could tamper with permissions. Some of them could drop/rebuild the function, which strips permissions.

To check if the procedure has been dropped/rebuilt. Run
select crdate
from sysobjects
where name = 'functionname'

If crdate (creation date) is recent, you may have your culprit there.|||Or it's the Miracle thing again....|||Is the server on 34th street, Brett? ;-) I would hate to hear that SQL security is as good as say...MS Word.

http://www.techworld.com/news/index.cfm?fuseaction=displaynews&NewsID=845|||Nice article - Just call me Kris ... Kringle that is.

Seriously, as already mentioned check to see if other admins exist.|||Originally posted by MCrowley
Is the server on 34th street, Brett? ;-) I would hate to hear that SQL security is as good as say...MS Word.

http://www.techworld.com/news/index.cfm?fuseaction=displaynews&NewsID=845

Cool...as or I like to say...Microsoft Swiss Cheese

and as far as miracles go...

I've known too many developers who don't realize it's their fault...

It's gotta be the computer...or It's a Miracle

How did all that data dissapear from the table...I didn't do ANYTHING...|||Oh and btw...I bet the udf was dropped and recompiled...

takers?|||That's about where I would start looking, Brett.|||"I didn't truncate that table" - oops :-)

Brett - you are correct about that one - I actually had a developer truncate a table - to only ask what happened... However, dropping permissions is more of a cognitive action - I least I hope ... Especially since this udf is accessed daily, you would have a shorter time span to recollect your thoughts about what you did that might have affected it. I still would not be surprised if it is a microsoft miracle because I have seen this behavior before (but only with replicated objects).

Dropping and recompiling would do it - however, the create date would have changed.|||[CTRL-E]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_MI]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_MI]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION udf_MI
(@.x varchar(256))
RETURNS char(1)
AS
BEGIN
DECLARE @.y char(1)
SELECT @.x = REPLACE(@.x,'.','')
SELECT @.y = CASE WHEN PATINDEX('% [a-zA-Z] %', @.x) > 0
THEN SUBSTRING(@.x,PATINDEX('% [a-zA-Z] %',@.x)+ 1,1)
ELSE NULL
END
RETURN @.y
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ooooooooooopppppppppppppppppppppssssssssssssssssss|||Originally posted by MCrowley
You are sure you are the only admin? Try running

sp_helpsrvrolemember sysadmin
and
sp_helpsrvrolemember securityadmin

If any other names show up in these, you may have a problem. Also check on sp_helpdb (your database) who is listed as the owner of the database? And check sp_helprolemember db_owner as well. Any of these folks could tamper with permissions. Some of them could drop/rebuild the function, which strips permissions.

To check if the procedure has been dropped/rebuilt. Run
select crdate
from sysobjects
where name = 'functionname'

If crdate (creation date) is recent, you may have your culprit there.

Yep! crdate is yesterday's date, that's when i found out that the permissions were dropped! (I originally created this function months ago!).

Is there a way to find out who dropped it, if any ?|||Since you are the only admin, YOU dropped it! Eh? ;)

Seriously, you may be the only admin, but does anybody else have a login with dbo priveleges?

blindman|||Originally posted by blindman
Since you are the only admin, YOU dropped it! Eh? ;)

Seriously, you may be the only admin, but does anybody else have a login with dbo priveleges?

blindman

I am the only Admin .. and UNLESS I was drunk (doubtful since I don't drink) or I was walking while asleep (which is also doubtful :o) .. )

Seriously, I am sure I have not dropped the function. I am still puzzled!|||Well, maybe it is time you have a drink :-). Is it identical to your original script ... Is it stored in a script ? If you did not drop/recreate the udf, then I would expect differences between the two. Also, as blindman already mentioned start to look for users that would have access to drop/recreate the udf.|||Originally posted by rnealejr
Well, maybe it is time you have a drink :-). Is it identical to your original script ... Is it stored in a script ? As blindman already mentioned start to look for users that would have access to drop/recreate the udf.

END OF THE DILEMMA!

This might sound obvious to some of you. But it sure is new to me (with my very limited experience with SQL Server).
What happened is that I modified the User Defined function yesterday. And that (for some reason that I still don't understand) deleted all the permissions. Or more accurately it dropped and recreated the function.

Now to make sure that was the case, I created a test function and test stored procedure. When I modified the stored procedure nothing was lost (the permissions were still there) but when I modified the function, all permissions disappeared (function dropped/recreated automatically).

Is this something that SQL server does by definition or is it strange to you guys too ?

Thanks!|||Holy-moly !!! That was critical information you left out - that you were modified the function !!! Anytime you modify something and that thing is altered from its normal behavior means that what you did caused it. Kudos to mcrowley for mentioning it for investigation.

How were you modifying the udf ?|||You must have been modifying the create script version of the udf that included a drop as illustrated by brett. alter function perserves the permissions.|||Originally posted by rnealejr
You must have been modifying the create script version of the udf that included a drop as illustrated by brett. alter function perserves the permissions.

Yes, I am using the create script version of the udf. But it does not include a drop. Here is what my udf looks like:

---
CREATE FUNCTION UFName (@.SomeVar int)
RETURNS @.SomeTab TABLE (--some fields)

AS
BEGIN
-- some code ..

RETURN
END
----

--|||If you did not first drop the udf then sql server would raise an error that this object name already existed in the database. If your scripting options are set to remove the referenced object, then you will be dropping/creating the udf. Anyway, in the future use alter function.|||"If you did not first drop the udf then sql server would raise an error that this object name already existed in the database."...unless the modifed script was created by a different login. Check to make sure that you don't have multiple copies of the function.

Also, if you modify the code by opening the UDF directly through Enterprise manager, you will see the code as you presented it, and Enterprise Manager will execute the create statement when you exit and save, but it will also execute a drop beforehand as well as record and reapply any permissions assigned to the function. If you modify the procedure through, say, query analyzer, you have to perform these tasks yourself.

Next time you need to modify it, script the UDF through Enterprise Manager's scripting tool, making sure to check the boxed to include code for dropping and for permissions. Then modify the scripted code and execute it, and then you are back in business.

blindman|||The problem is that multistatement table-valued functions do not behave properly when altered. If you alter a multistatement table-valued function, you will lose the permissions assigned to that udf. When editing a udf in em, you are performing an alter function not create - so the same behavior will occur. For all other udfs, table and scalar - the permissions are not affected.|||That's a new one on me! Gotta try that out.

Any idea why the UDF's would be handled differently than stored procedures?

blindman|||I just ran a test for the situation described, and sure enough, - rnealejr is right!|||Originally posted by Brett Kaiser
Or it's the Miracle thing again....

A sign from Edgar!

I always DROP

Then I get off the floor and order another one...|||rneal,

How did you find this? Is it mentioned in any documentation? It's strange enough the it doesn't apply to scalar functions, but if it only applies to multi-statement table functions and not simple select table functions then that is really odd.

blindman|||I don't think it matters how he found it, the point is it's there and no more ALTER should be done against them until M$ fixes it :)|||It matters to me, how he found it, because if there's some SQL Server documentation from Microsoft that I'm not aware of and that my have information that can help me prevent future problems, then I'd like to see it.

But hey, I'm just like that...|||Originally posted by blindman
It matters to me, how he found it, because if there's some SQL Server documentation from Microsoft that I'm not aware of and that my have information that can help me prevent future problems, then I'd like to see it.

But hey, I'm just like that...

Are you kidding? MS publishing APARS?

Please every "undocumented feature" is found by discovery...there's got to be thousands...|||You contradict yourself again. But if it really matters, follow your own advice, - experiment!

[blinds off]
create function...
go
grant select on...to guest
go
alter function...
go
setuser 'guest'
go
select * from <function>
go
setuser
go
[blinds on]|||You are so clueless, rdjabarov.

[jabbers on]

The idea is to see if there is anything ELSE in the documentation that might be important to know. It's a concept called "being prepared" that persons involved in IT administration have to be aware of. If you want to advance in this field, you need will need to actively seek out new sources of knowledge.

[jabbers off]|||Not again...

It's neither...

MS just doesn't do a good enough job in this area...|||FYI

This is a known bug within sql server and is expected to be corrected with the next release. More microsoft miracles.

Also, as a reminder, lets keep the forum civil - I don't want to have to remove my belt ... :-)|||Originally posted by rnealejr
FYI

This is a known bug within sql server and is expected to be corrected with the next release. More microsoft miracles.

Also, as a reminder, lets keep the forum civil - I don't want to have to remove my belt ... :-)

Well...

if you're gonna play moderator...then who is JonathanB...and why is a web developer moderating a SQL Sever forum?|||And how the H_LL did the Eagles pull that off?|||This is not the first time blindmand is playing ugly. But we're all used to it by now. So we'll just ignore it ... again :)|||As in life, if you think someone is being inappropriate - just ignore it. That will normally take care of it, otherwise affirming the response will just aggravate the situation.

Anyway, more information about the real issue for this post. I just talked to ms and it will be fixed in yukon (not the next sp). Also, I requested that a kb article be created for this bug. If a kb article is entered I will notify dbforums. Also, this bug was reported after sp3 was released.

One item of note is that if you run profiler, you will notice the error:

Error: 208, Severity: 16, State: 1

When you alter a multi-statement udf (this does not appear for the other udfs).|||For the Eagles ... Every game this weekend was awesome. I can not remember a time when so many teams have been so evenly matched.|||Brett - Each forum has moderators. But there are a couple of moderators who span all forums. We have recently had a couple of advertising/spam issues - if you see it, please send me a message. Developer, dba, sa ... Am there, doing it.|||That's sad, especially considering the fact that Beta 1 of Yukon barely has any functional modules, and BOL can hardly be called finished.|||I was disappointed as well since another sp release will probably beat the yukon release. I am more bothered by the fact that they did not submit it as a kb article.|||Originally posted by rnealejr
Brett - Each forum has moderators. But there are a couple of moderators who span all forums. We have recently had a couple of advertising/spam issues - if you see it, please send me a message. Developer, dba, sa ... Am there, doing it.

Hey thanks...

All good games...AND history...

first ever playoff game w/out a punt...

I was hoping for an Indy Packer SB...I still can't believe the Pacl blew it...

should never have gone in to OT...|||When Favre threw that interception in OT, I could have just kicked my tv ... That was the worst decision I have ever seen him make - he might as well just punted the darn ball.|||Originally posted by rnealejr
When Favre threw that interception in OT, I could have just kicked my tv ... That was the worst decision I have ever seen him make - he might as well just punted the darn ball.

You a cheese head?

Where are you from?

Being a Giant Fan (all sympathy cards and wishes welcome) I wanted ANYONE to knock off philly...

Bussy of mine is from cheesland though...looked like he wanted to cry last night...

Hey some good football on tap next weekend...|||No - seeing a great quarterback like Favre throw a ball so recklessly in the air had me befuddled.|||Originally posted by rnealejr
No - seeing a great quarterback like Favre throw a ball so recklessly in the air had me befuddled.

I think the receiver didn't read the blitz...

he was suppose to come off his route...

Still...they were running it down their throat...stick with what works...

But come on 4th and 26?

http://www.nj.com/eagles/times/index.ssf?/base/sports-0/10739020319570.xml|||Playoffs are full of miracles - the immaculate reception, hail mary, the catch ...|||wow, football, men are talking...where the margarita mixer?|||Originally posted by ms_sql_dba
wow, football, men are talking...where the margarita mixer?

Why do I get a specific gender slant?

I know...let's talk about Ice dancing...

Wednesday, March 21, 2012

How come my SQL Server Job won't run?

Hi - I just created a job in SQL Server using Management Studio. This is the
first time I've tried to build a job. The job failed to execute at the
scheduled time and it fails when I start it manually. Looking at the Job
History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
failed."
I tried to pick a different user from the Run as drop down in the Job step
properties window, but the drop down is empty. Does anyone know what might
be going on here?
--
Thanks,
JeffHi Jeff,
What is your job supposed to do? On the job history expand the job steps and
look for additional error messages every step. If applicable also check the
SQL Server error log.
You can also try the Advanced page on the Job Step section and specify an
output file. Then run the job again and see if you can find some additional
error information there.
Perhaps you do not need to change the 'Run as' section until you know what
the problem is.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Jeff Stroope" wrote:
> Hi - I just created a job in SQL Server using Management Studio. This is the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what might
> be going on here?
> --
> Thanks,
> Jeff|||Jeff,
I just ran into someone getting the same error message. Are you by any
change running something like the following TSQL step?
exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
In the other case, it seems that shelling out and then logging back in as a
Trusted Connection raised the error.
In their case, changing the SQL Server service from running as Local System
to running as a domain login resolved the problem. (Of course, you need to
have or create a domain account with the proper permissions.)
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> Hi - I just created a job in SQL Server using Management Studio. This is
> the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what
> might
> be going on here?
> --
> Thanks,
> Jeff|||Hi Ben - the job is supposed to run a stored procedure. The job only has one
step, which is "execute sp_storedProcedure". I'll try the output file and
see what happens.
--
Thanks,
Jeff
"Ben Nevarez" wrote:
> Hi Jeff,
> What is your job supposed to do? On the job history expand the job steps and
> look for additional error messages every step. If applicable also check the
> SQL Server error log.
> You can also try the Advanced page on the Job Step section and specify an
> output file. Then run the job again and see if you can find some additional
> error information there.
> Perhaps you do not need to change the 'Run as' section until you know what
> the problem is.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "Jeff Stroope" wrote:
> > Hi - I just created a job in SQL Server using Management Studio. This is the
> > first time I've tried to build a job. The job failed to execute at the
> > scheduled time and it fails when I start it manually. Looking at the Job
> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> > failed."
> >
> > I tried to pick a different user from the Run as drop down in the Job step
> > properties window, but the drop down is empty. Does anyone know what might
> > be going on here?
> > --
> > Thanks,
> >
> > Jeff|||Thanks Russell - no, I'm just trying execute a stored procedure that queries
some tables in a database on a linked server and then populates tables on the
job server.
--
Thanks,
Jeff
"Russell Fields" wrote:
> Jeff,
> I just ran into someone getting the same error message. Are you by any
> change running something like the following TSQL step?
> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
> In the other case, it seems that shelling out and then logging back in as a
> Trusted Connection raised the error.
> In their case, changing the SQL Server service from running as Local System
> to running as a domain login resolved the problem. (Of course, you need to
> have or create a domain account with the proper permissions.)
> RLF
>
> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> > Hi - I just created a job in SQL Server using Management Studio. This is
> > the
> > first time I've tried to build a job. The job failed to execute at the
> > scheduled time and it fails when I start it manually. Looking at the Job
> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> > Login
> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> > step
> > failed."
> >
> > I tried to pick a different user from the Run as drop down in the Job step
> > properties window, but the drop down is empty. Does anyone know what
> > might
> > be going on here?
> > --
> > Thanks,
> >
> > Jeff
>
>|||Jeff,
OK, but you still have an execution context issue since you are going to
another server. A couple more questions:
1. Who is the job owner of the SQL Agent job? Your login? The 'sa'
account? NT AUTHORITY\SYSTEM? A domain login? A SQL Server login?
2. How do you have your security set on the linked server? Check the
security tab of the linked server definition. Local login impersonation?
The login's current security context? A specific login?
You might be able to manipulate the linked server definition to get this to
work for you. But, as is, I suspect that the local system account is at the
root of the problem for the job you have defined.
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:FA4BED9F-1877-4B01-ADA5-3F1B49027EC9@.microsoft.com...
> Thanks Russell - no, I'm just trying execute a stored procedure that
> queries
> some tables in a database on a linked server and then populates tables on
> the
> job server.
> --
> Thanks,
> Jeff
>
> "Russell Fields" wrote:
>> Jeff,
>> I just ran into someone getting the same error message. Are you by any
>> change running something like the following TSQL step?
>> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
>> In the other case, it seems that shelling out and then logging back in as
>> a
>> Trusted Connection raised the error.
>> In their case, changing the SQL Server service from running as Local
>> System
>> to running as a domain login resolved the problem. (Of course, you need
>> to
>> have or create a domain account with the proper permissions.)
>> RLF
>>
>> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
>> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
>> > Hi - I just created a job in SQL Server using Management Studio. This
>> > is
>> > the
>> > first time I've tried to build a job. The job failed to execute at the
>> > scheduled time and it fails when I start it manually. Looking at the
>> > Job
>> > History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
>> > Login
>> > failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
>> > step
>> > failed."
>> >
>> > I tried to pick a different user from the Run as drop down in the Job
>> > step
>> > properties window, but the drop down is empty. Does anyone know what
>> > might
>> > be going on here?
>> > --
>> > Thanks,
>> >
>> > Jeff
>>|||Hi,
Did you find the solution for this. Even I am facing similar problem.
I am trying to configure a linked server with the following settings
sp_addlinkedserver 'Linked Server Name', 'SQL Server'
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'Domain\LoginName'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
If I run the query against this linked server I get the perfect result.
Now I have scheduled a Job which runs on sa account. It always throws the
error:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
Let me know on what I am doing wrong.|||Praveen,
What you have said is, if the linked server is accessed by login
'Domain\LoginName' then log in remotely as 'DatabaseUserName' using
'DatabaseUSerPassword'. If the linked server is accessed by any other
login, then that login's security context.
If I understood correctly, you said that the job runs as 'sa', which is a
local SQL Server login and has no domain rights. You messages refer to NT
AUTHORITY\SYSTEM and 'DOMAIN\MACHINENAME', to which you do not seem to have
granted any special rights to the 'Linked Server Name' server.
Also, it appears that the SQL Server service is running as the local system
account, but a best practice is to run the SQL Server service as a domain
account and not as local system. You can see that a domain account would be
handier for accessing anything not on the local server. (If I properly
understood your setup.)
Perhaps you could add:
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'sa'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
FWIW,
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:67D35848-9BB7-49E8-B128-B26E040F7D8E@.microsoft.com...
> Hi,
> Did you find the solution for this. Even I am facing similar problem.
> I am trying to configure a linked server with the following settings
> sp_addlinkedserver 'Linked Server Name', 'SQL Server'
> sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
> , @.useself = 'FALSE'
> , @.locallogin = 'Domain\LoginName'
> , @.rmtuser = 'DatabaseUserName'
> , @.rmtpassword = 'DatabaseUSerPassword'
>
> If I run the query against this linked server I get the perfect result.
> Now I have scheduled a Job which runs on sa account. It always throws the
> error:
> Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
> 'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
> Let me know on what I am doing wrong.
>|||Russell,
Now I have removed all the logins and had defaulted to DatabaseUserName and
DatabaseUserPassword for all the logins.
What I did was [this is in SQL Server 2005]
1. Right click Linked Servers -> New Linked Server
2. General tab:
Linked Server: Name of the SQL Server. Let us call this as 'Linked Server'
Server type: SQL Server
3. Security tab:
No Local Server Logins were added. Add selected the option "Be made using
this security context" in the list of options. Remote Login and Password are
DatabaseUserName and DatabaseUserPassword.
This setup I believe that regardless of the access account from the source
machine [machine from which i am querying the linked server] it always trys
to connect as DatabaseUserName to the linked server.
This works perfectly fine on any query made to the linked server. I can
access table objects, sysobjects of Linked Server from the Source Machine.
The same queries if I make them in the Stored Procedure and schedule to
execute the SP as a Job doesn't works.
Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
The difference between direct query and the job is that in direct query is
through the Window authentication mode, which is "Domain\UserName" account
whereas through job in Source machine sa account.
With the setup of Linked server I believe it should always go through the
DatabaseUserName and DatabasePassword credentials.
What is the wrong here?|||Praveen,
Yes, with a hardwired remote Login and Password I would expect all access
to be through that login. What now? Well, you are now getting the message:
Server 'LinkedServerName is not configured for RPC.
Please look at the Linked Server Properties, the Server Options tab. Make
sure the RPC settings are TRUE.
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:2C20479D-7A59-4910-8647-A334E8EF4A95@.microsoft.com...
> Russell,
> Now I have removed all the logins and had defaulted to DatabaseUserName
> and
> DatabaseUserPassword for all the logins.
> What I did was [this is in SQL Server 2005]
> 1. Right click Linked Servers -> New Linked Server
> 2. General tab:
> Linked Server: Name of the SQL Server. Let us call this as 'Linked
> Server'
> Server type: SQL Server
> 3. Security tab:
> No Local Server Logins were added. Add selected the option "Be made
> using
> this security context" in the list of options. Remote Login and Password
> are
> DatabaseUserName and DatabaseUserPassword.
> This setup I believe that regardless of the access account from the source
> machine [machine from which i am querying the linked server] it always
> trys
> to connect as DatabaseUserName to the linked server.
> This works perfectly fine on any query made to the linked server. I can
> access table objects, sysobjects of Linked Server from the Source Machine.
> The same queries if I make them in the Stored Procedure and schedule to
> execute the SP as a Job doesn't works.
> Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
> configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
> The difference between direct query and the job is that in direct query is
> through the Window authentication mode, which is "Domain\UserName" account
> whereas through job in Source machine sa account.
> With the setup of Linked server I believe it should always go through the
> DatabaseUserName and DatabasePassword credentials.
> What is the wrong here?
>

How come my SQL Server Job won't run?

Hi - I just created a job in SQL Server using Management Studio. This is the
first time I've tried to build a job. The job failed to execute at the
scheduled time and it fails when I start it manually. Looking at the Job
History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
failed."
I tried to pick a different user from the Run as drop down in the Job step
properties window, but the drop down is empty. Does anyone know what might
be going on here?
Thanks,
Jeff
Hi Jeff,
What is your job supposed to do? On the job history expand the job steps and
look for additional error messages every step. If applicable also check the
SQL Server error log.
You can also try the Advanced page on the Job Step section and specify an
output file. Then run the job again and see if you can find some additional
error information there.
Perhaps you do not need to change the 'Run as' section until you know what
the problem is.
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Jeff Stroope" wrote:

> Hi - I just created a job in SQL Server using Management Studio. This is the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what might
> be going on here?
> --
> Thanks,
> Jeff
|||Jeff,
I just ran into someone getting the same error message. Are you by any
change running something like the following TSQL step?
exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
In the other case, it seems that shelling out and then logging back in as a
Trusted Connection raised the error.
In their case, changing the SQL Server service from running as Local System
to running as a domain login resolved the problem. (Of course, you need to
have or create a domain account with the proper permissions.)
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> Hi - I just created a job in SQL Server using Management Studio. This is
> the
> first time I've tried to build a job. The job failed to execute at the
> scheduled time and it fails when I start it manually. Looking at the Job
> History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM.
> Login
> failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The
> step
> failed."
> I tried to pick a different user from the Run as drop down in the Job step
> properties window, but the drop down is empty. Does anyone know what
> might
> be going on here?
> --
> Thanks,
> Jeff
|||Hi Ben - the job is supposed to run a stored procedure. The job only has one
step, which is "execute sp_storedProcedure". I'll try the output file and
see what happens.
Thanks,
Jeff
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Jeff,
> What is your job supposed to do? On the job history expand the job steps and
> look for additional error messages every step. If applicable also check the
> SQL Server error log.
> You can also try the Advanced page on the Job Step section and specify an
> output file. Then run the job again and see if you can find some additional
> error information there.
> Perhaps you do not need to change the 'Run as' section until you know what
> the problem is.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
>
> "Jeff Stroope" wrote:
|||Thanks Russell - no, I'm just trying execute a stored procedure that queries
some tables in a database on a linked server and then populates tables on the
job server.
Thanks,
Jeff
"Russell Fields" wrote:

> Jeff,
> I just ran into someone getting the same error message. Are you by any
> change running something like the following TSQL step?
> exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'
> In the other case, it seems that shelling out and then logging back in as a
> Trusted Connection raised the error.
> In their case, changing the SQL Server service from running as Local System
> to running as a domain login resolved the problem. (Of course, you need to
> have or create a domain account with the proper permissions.)
> RLF
>
> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
>
>
|||Jeff,
OK, but you still have an execution context issue since you are going to
another server. A couple more questions:
1. Who is the job owner of the SQL Agent job? Your login? The 'sa'
account? NT AUTHORITY\SYSTEM? A domain login? A SQL Server login?
2. How do you have your security set on the linked server? Check the
security tab of the linked server definition. Local login impersonation?
The login's current security context? A specific login?
You might be able to manipulate the linked server definition to get this to
work for you. But, as is, I suspect that the local system account is at the
root of the problem for the job you have defined.
RLF
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:FA4BED9F-1877-4B01-ADA5-3F1B49027EC9@.microsoft.com...[vbcol=seagreen]
> Thanks Russell - no, I'm just trying execute a stored procedure that
> queries
> some tables in a database on a linked server and then populates tables on
> the
> job server.
> --
> Thanks,
> Jeff
>
> "Russell Fields" wrote:
|||Hi,
Did you find the solution for this. Even I am facing similar problem.
I am trying to configure a linked server with the following settings
sp_addlinkedserver 'Linked Server Name', 'SQL Server'
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'Domain\LoginName'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
If I run the query against this linked server I get the perfect result.
Now I have scheduled a Job which runs on sa account. It always throws the
error:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
Let me know on what I am doing wrong.
|||Praveen,
What you have said is, if the linked server is accessed by login
'Domain\LoginName' then log in remotely as 'DatabaseUserName' using
'DatabaseUSerPassword'. If the linked server is accessed by any other
login, then that login's security context.
If I understood correctly, you said that the job runs as 'sa', which is a
local SQL Server login and has no domain rights. You messages refer to NT
AUTHORITY\SYSTEM and 'DOMAIN\MACHINENAME', to which you do not seem to have
granted any special rights to the 'Linked Server Name' server.
Also, it appears that the SQL Server service is running as the local system
account, but a best practice is to run the SQL Server service as a domain
account and not as local system. You can see that a domain account would be
handier for accessing anything not on the local server. (If I properly
understood your setup.)
Perhaps you could add:
sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
, @.useself = 'FALSE'
, @.locallogin = 'sa'
, @.rmtuser = 'DatabaseUserName'
, @.rmtpassword = 'DatabaseUSerPassword'
FWIW,
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:67D35848-9BB7-49E8-B128-B26E040F7D8E@.microsoft.com...
> Hi,
> Did you find the solution for this. Even I am facing similar problem.
> I am trying to configure a linked server with the following settings
> sp_addlinkedserver 'Linked Server Name', 'SQL Server'
> sp_addlinkedsrvlogin @.rmtsrvname = 'Linked Server Name'
> , @.useself = 'FALSE'
> , @.locallogin = 'Domain\LoginName'
> , @.rmtuser = 'DatabaseUserName'
> , @.rmtpassword = 'DatabaseUSerPassword'
>
> If I run the query against this linked server I get the perfect result.
> Now I have scheduled a Job which runs on sa account. It always throws the
> error:
> Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
> 'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).
> Let me know on what I am doing wrong.
>
|||Russell,
Now I have removed all the logins and had defaulted to DatabaseUserName and
DatabaseUserPassword for all the logins.
What I did was [this is in SQL Server 2005]
1. Right click Linked Servers -> New Linked Server
2. General tab:
Linked Server: Name of the SQL Server. Let us call this as 'Linked Server'
Server type: SQL Server
3. Security tab:
No Local Server Logins were added. Add selected the option "Be made using
this security context" in the list of options. Remote Login and Password are
DatabaseUserName and DatabaseUserPassword.
This setup I believe that regardless of the access account from the source
machine [machine from which i am querying the linked server] it always trys
to connect as DatabaseUserName to the linked server.
This works perfectly fine on any query made to the linked server. I can
access table objects, sysobjects of Linked Server from the Source Machine.
The same queries if I make them in the Stored Procedure and schedule to
execute the SP as a Job doesn't works.
Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
The difference between direct query and the job is that in direct query is
through the Window authentication mode, which is "Domain\UserName" account
whereas through job in Source machine sa account.
With the setup of Linked server I believe it should always go through the
DatabaseUserName and DatabasePassword credentials.
What is the wrong here?
|||Praveen,
Yes, with a hardwired remote Login and Password I would expect all access
to be through that login. What now? Well, you are now getting the message:
Server 'LinkedServerName is not configured for RPC.
Please look at the Linked Server Properties, the Server Options tab. Make
sure the RPC settings are TRUE.
RLF
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:2C20479D-7A59-4910-8647-A334E8EF4A95@.microsoft.com...
> Russell,
> Now I have removed all the logins and had defaulted to DatabaseUserName
> and
> DatabaseUserPassword for all the logins.
> What I did was [this is in SQL Server 2005]
> 1. Right click Linked Servers -> New Linked Server
> 2. General tab:
> Linked Server: Name of the SQL Server. Let us call this as 'Linked
> Server'
> Server type: SQL Server
> 3. Security tab:
> No Local Server Logins were added. Add selected the option "Be made
> using
> this security context" in the list of options. Remote Login and Password
> are
> DatabaseUserName and DatabaseUserPassword.
> This setup I believe that regardless of the access account from the source
> machine [machine from which i am querying the linked server] it always
> trys
> to connect as DatabaseUserName to the linked server.
> This works perfectly fine on any query made to the linked server. I can
> access table objects, sysobjects of Linked Server from the Source Machine.
> The same queries if I make them in the Stored Procedure and schedule to
> execute the SP as a Job doesn't works.
> Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
> configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.
> The difference between direct query and the job is that in direct query is
> through the Window authentication mode, which is "Domain\UserName" account
> whereas through job in Source machine sa account.
> With the setup of Linked server I believe it should always go through the
> DatabaseUserName and DatabasePassword credentials.
> What is the wrong here?
>

Monday, March 19, 2012

How can you get the current database name via T-SQL?

I have stored some commonly used sprocs and user-defined functions I have created in a separate db.

Some of these functions work with the current db sysobjects table, for instance, and need to "know" the database that I am calling the sproc/function from.

Is there anyway to return the current database name via T-SQL to assign to a local variable which I can then include in the procedure/function execute statement?

Bill

Use the system function: db_name().

SELECT db_name()

|||

Code Snippet

select db_name()

|||

Thanks so much. I knew is was probably simple, just couldn;t find it in the T-SQL help.

Monday, March 12, 2012

How can we avoid somebody to access the MDF data by doing User Instance connection?

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

Hi,

AFAIK their is no way to restrict access to MDF/LDF files physically... refer below thread which has discussion the same issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

Hemantgiri S. Goswami

|||

Alright, but what about the User Instance? Is there a way to prevent somebody of getting connected on a server using our MDF file and the User Instance option into his connection string?

|||No, you wil lhave to do this via NTFS permissions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, February 24, 2012

How can I update the col value using extended stored procedure

Hi,
I have created extended stored procedure. In that I need to update the value
of the perticular column of the table and return the same value. Is that
possible using extended stored procedure.
Please help me to solve this problem. If you have any sample code plz send
me, it would be more helpful to me.
Regards
VeeruI can think of 2 strategies:
1) If you only have to update a single value at the end of your processing
inside the XP code, you are best of returning this as a output parameter or
set of output parameters and use this inside a regular SP to do an INSERT,
UPDATE or DELETE
2) If you need to do this within the XP, you need to create a loop back
connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
DELETE on the loopback connection. There is a ODBC sample for setting up a
loop back connection using ODBC that ships with SQL Server. The rest is
normal ODBC programming of which there are many samples flying around.
GertD@.SQLDev.Net
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
connect back to SQL Server from an XP and execute SQL commands.
http://msdn.microsoft.com/library/d...
o1f.asp
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||Hi Thanks for your responce.
The documentation in the below path you have sent saying that the sample is
available at x:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz sen
d
if you have that.
"Mike C#" wrote:

> SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
> connect back to SQL Server from an XP and execute SQL commands.
> http://msdn.microsoft.com/library/d...r />
_2o1f.asp
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||Hi Gert E.R. Drapers,
Thanks for your response.
I need to implement first point what you mentioned in your post below in XP
code. Can you please guide me with sample. I just started learning and
working on SQL Server 2005. We are using OLEDB not ODBC.
Regards
Veeru
"Gert E.R. Drapers" wrote:

> I can think of 2 strategies:
> 1) If you only have to update a single value at the end of your processing
> inside the XP code, you are best of returning this as a output parameter o
r
> set of output parameters and use this inside a regular SP to do an INSERT,
> UPDATE or DELETE
> 2) If you need to do this within the XP, you need to create a loop back
> connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
> DELETE on the loopback connection. There is a ODBC sample for setting up a
> loop back connection using ODBC that ships with SQL Server. The rest is
> normal ODBC programming of which there are many samples flying around.
> GertD@.SQLDev.Net
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||BTW if you're using SQL 2005, then don't use extended stored proc's, as they
are depracated. Use the SQLCLR to create assemblies.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>|||It should be in C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib, assuming you installed on the
C: drive. They are installed by the SQL 2000 installer.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>

Sunday, February 19, 2012

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names> ) REFERENCES <other table name> (<column names> ) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Friday, March 30, 2012

How do I allow an end user make modifications to a rdl that I created using Report Designe

How do i add my database to "data sources"?

How do i add my database to "data sources"?

How do I add a single column heading row in the detail section?

Friday, March 23, 2012

How could this have happened ??

Wednesday, March 21, 2012

How come my SQL Server Job won't run?

How come my SQL Server Job won't run?

Monday, March 19, 2012

How can you get the current database name via T-SQL?

Monday, March 12, 2012

How can we avoid somebody to access the MDF data by doing User Instance connection?

Friday, February 24, 2012

How can I update the col value using extended stored procedure

Sunday, February 19, 2012

How can i update all table relationships in one go?

How can i update all table relationships in one go?

How can i update all table relationships in one go?

transfer Data From Acces

Blog Archive

About Me