February 13, 2012 at 11:22 am
We have a DTS that runs on our server but calls a stored proc on the remote server. Stored proc makes use of a linked server (where the process (DTS) resides)
Even though, the remote server doesn't have any linked server setup, it doesn't scream.
But when we convert the package to SSIS, the package failes at the call to stored proc saying it doesn't find any reference to linked server and add it.
Any reason, why would DTS wouldn't scream but SSIS would?
February 13, 2012 at 1:42 pm
So, in SSIS, you are using an ExecuteSQL task to initiate a stored proc on a different server, is that correct?
Have you configured a connection to the other server?
If not, please explain in more detail how you have set this up in SSIS.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 13, 2012 at 1:47 pm
Yes That is correct, I use Execute SQl task to execute the stored proc.
Through the SSIS, a service account is used to access the remote box. What do you mean configuring the other server?
February 13, 2012 at 2:02 pm
The ExecuteSQL task requires a connection. Where is that connection pointing?
Can you also post the full text of the error message which you are getting?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 13, 2012 at 2:42 pm
Connection is pointing to remote server, which is configured to connect through service account.
Stored proc on remote server references a linked server to the calling server (where the package resides)..But the linked server back to calling server is not setup in the remote server.
Error message:
Could not find server 'CallingServer' 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.
Please note, with the same configuration, DTS works even without adding the linked server on the remote server.
February 13, 2012 at 2:53 pm
SQL_Surfer (2/13/2012)
Connection is pointing to remote server, which is configured to connect through service account.Stored proc on remote server references a linked server to the calling server (where the package resides)..But the linked server back to calling server is not setup in the remote server.
Error message:
Could not find server 'CallingServer' 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.
Please note, with the same configuration, DTS works even without adding the linked server on the remote server.
Hmm, should work.
If you log in to the remote server via SSMS using the same (service account) credentials, can you execute the stored proc OK?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 13, 2012 at 3:01 pm
No. Same error.
Question is: why NO error regarding linked server in DTS but error in SSIS?
February 13, 2012 at 3:08 pm
Don't know (been years since I last used DTS and I've forgotten how it works!)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 13, 2012 at 3:10 pm
Another thing to note is that DTS uses a sql login but SSSI uses a service account. But that still doesn't explain why it wouldn't error out (can't find linked server....) through DTS but SSIS
February 13, 2012 at 3:17 pm
SQL_Surfer (2/13/2012)
Another thing to note is that DTS uses a sql login but SSSI uses a service account. But that still doesn't explain why it wouldn't error out (can't find linked server....) through DTS but SSIS
Maybe the SQL login has additional rights - you need to check the linked server setup.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 13, 2012 at 3:18 pm
There isn't any linked server setup and DTS works but SSIS doesn't
February 13, 2012 at 11:57 pm
SQL_Surfer (2/13/2012)
There isn't any linked server setup and DTS works but SSIS doesn't
My guess is that DTS is pointing at a different server.
If there isn't a linked server, then DTS can't use it. (DTS and SSIS actually don't do anything, they just launch a stored procedure).
Last time I checked DTS didn't contain any magic...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2012 at 11:55 am
Interesting problem. Agree with Koen (previous poster) that dts is using a script to connect to the linked server.
All the posters here believe its a connection issue to the server you are trying to run the stored proc on.
SSMS would be a good place to start using the service account you have set-up, leave ssis out of the picture till you can access the data source you are trying to connect to. If you can successfully connect to the data source in ssms (using the service account) you would have probably solved the problem. My guess is its a security setting of some sort. If you have a db admin it would probably be a good idea to hash this out with him.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply