Link to home
Create AccountLog in
Avatar of tonypatton21
tonypatton21

asked on

DTS - Linked Server Query in Transformation Task Fails

Hello, I'm moving a DTS package from one server (where it works, 2k box) to a second server which is a 2k5 box. A transformation task within the package has a query involving a linked server, which is also set up on the new server. The query works from a Query window but fails in the package with the following error:

Could not find server 'LAW' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

LAW linked server exists in the  sys.servers of the 2nd server, and as I said, the query works from a query window on the 2nd server as well. Not sure what's going on or where to look next. Help!!
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> LAW linked server exists in the  sys.servers of the 2nd server, and as I said, the query works from a query window on the 2nd server as well.

As you mentioned, when package is executed under your user account in SSMS it works..
But it was executed under some other user account and that's why you received the error..

Kindly check the user account the package is running..
Check the permissions on the linked server.  If the Agent account does not have needed permissions, then the package will fail when automated.
Avatar of tonypatton21
tonypatton21

ASKER

When I have the package opened in the DTS Designer, it's failing there. What user would I need to check permissions for?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That makes sense that it's a permissions issue.  Any idea what permissions I am missing specifically? The id has basic public permissions in master. Not sure what else it needs.  Thanks for the help thus far guys.
You can find out pretty easily:
Execute the same Stored Procedure on the same box and as the same user that fails from the DTS Package.
Just grant EXECUTE privileges on the stored procedure to that particular user so that it will work..