April 25, 2005 at 7:28 am
We have a problem with a stored procedure which inserts a row into a local table then calls a remote stored procedure to insert a row into a table on the remote server.
The relevant bits of the procedure do this:
BEGIN TRAN
INSERT INTO localtable VALUES (......
EXEC @rc = SendMail @parameters -- SendMail issues the RPC
IF @rc <> 0
ROLLBACK TRAN RETURN
COMMIT TRAN
The procedure works fine on one Server but hangs on another. Both Severes are configured identically as far as I can see (Windows 2000, SQL 2000 SP3a, both have the remote server set up). The procedure also works on the second server if we comment out the BEGIN, ROLLBACK & COMMIT statements (this may be a clue to the problem but I still can't see it).
The server that works is being wiped and used for something else next week so I have a few days to find the answer!
April 25, 2005 at 9:06 am
RPC means many different things to different people, you might want to show the code in your "SendMail" proc. (like specifically, by RPC do you mean a stored proc on another SQL Server called explicitly within your local code in this manner "EXEC server.db.owner.proc"?)
Likely its a configuration thing with DTC, but I go to great lengths never to use the DTC so, someone else may need to help you there.
April 25, 2005 at 9:24 am
Yes, the RPC sets up parameters and calls the remote procedure in the form 'EXEC server.db.owner.proc', passing a mix of varchar and numeric params.
April 25, 2005 at 10:01 am
Well then, how far have you gone looking into if DTC is configured properly? The setup, checking the log(s), tracing, etc.
April 26, 2005 at 2:15 am
The DTC Service is running but the associated Message Queuing service hasn't been installed for some reason - this is almost certainly the problem. I will check with the guy who built the Server.
A recent editorial about sysadmin rights is pertinent here. Our Windows Admin colleagues look after the OS side of things but we often find problems with the Servers they give us -bits missing, permissions too tight for SQL Server, accounts missing from local groups, wrong RAID configuration etc. I'm sure we, the DBAs, would do a much better job but the real difficulty is persuading them that this is so.
April 26, 2005 at 9:14 am
By any chance, have you check to make sure the Distributed Transaction Coordinator is started on the second remote server ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply