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!!
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
Raja Jegan R

>> 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..
Brendt Hess

Check the permissions on the linked server.  If the Agent account does not have needed permissions, then the package will fail when automated.
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
tonypatton21

ASKER
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.
Anthony Perkins

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.
Raja Jegan R

Just grant EXECUTE privileges on the stored procedure to that particular user so that it will work..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.