March 5, 2007 at 3:15 pm
Hi,
I'm hoping that someone can lead me in the right direction. I have an update trigger on a table. When the trigger runs, it calls a stored procedure on a linked server to get some information, and then does some other things. This was running before, but we migrated our database server to a new one.
The trigger is hanging up when called because of an update statement on the table. If I run the stored procedure manually, i.e. not from within the trigger, it runs fine. I think I've eliminated security issues: I am using the same user for manually running the stored procedure and for running the update statement which causes the update trigger to run.
If I comment out the linked server stored procedure call from within the trigger, the trigger runs fine. If I run the stored procedure across the linked server, that also runs fine. It just doesn't run when calling the linked server's stored procedure from within the trigger.
I've looked in the current activity in Enterprise Manager on the linked server, and it does show a connection from the calling server, but the details dialog is empty. I also ran profiler on the server that is being called out to, and it shows no connections.
The server that was replaced was not the one with the update trigger. It was the one that is being called by the server with the update trigger. I added that server as a linked server on one that has the update trigger. I tested the procedure call manually through the linked server and it worked. It just doesn't work through the trigger.
Any help would be greatly appreciated.
Thanks,
Seth
March 5, 2007 at 4:20 pm
Are you certain that the permissions on the linked server are correct? When you run the SP manually, it may be that your personal credentials (or whatever you've used to connect via query analyser) are being used to execute the SP and that when the trigger tries to execute it, it does not use an account with the right permissions. I usually add a specific login to linked servers which can then be strictly controlled on the linked server and therefore the account specified will need to have execute permissions for the SP on the linked server.
It seems the most likely cause since you have replaced the linked server rather than the originating server.
Steve
March 5, 2007 at 8:57 pm
Steve,
I found the following article:
http://www.mcse.ms/archive/index.php/t-1425692.html
It seems to have moved me farther along the way (I now get an error message as opposed to a hung query). The error is:
Server: Msg 8501, Level 16, State 1, Procedure tUpdateStuff, Line 56
MSDTC on server 'SecondServer' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure tUpdateStuff, Line 56
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Any more clues you can give?
Thanks for your help so far.
Seth
March 5, 2007 at 9:36 pm
Did you read the error message?
MSDTC on server 'SecondServer' is unavailable.
It's not a clue. It's a big fat point.
_____________
Code for TallyGenerator
March 6, 2007 at 9:20 am
Sergiy-
If you can't be polite, why even respond? Its my understanding this forum is about helping one another. Especially, when someone is new to sqlservercentral.com we should try responding with help not sarcasm.
March 6, 2007 at 9:54 am
Hi Seth,
I think there is still some work that needs to happen on the configuration of the DTC on your remote server.Try looking at this http://support.microsoft.com/kb/817064/
March 8, 2007 at 1:46 pm
Jeff,
Thanks for the link. It turned out that it was the DTC on the Windows 2003 Server. I am running Windows 2000 on one server and 2003 on the other server. I had to set the 2003 server to "No Authentication Required":
http://support.microsoft.com/kb/899191/
It's now working smoothly. Thanks for your help!
Seth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply