September 19, 2002 at 8:33 am
Hi
Our developers have a stored proc that runs a distributed transaction.
Part of the transaction is INSERT foo INTO remoteserver.db.owner.table ...
If this fails, the local @@ERROR is obviously not set, but how do they read the remote error.
Using SELECT * FROM OPENQUERY (RemoteServer, 'SELECT @@ERROR') does not work, as we've only allowed the peasant user with bare priviliges to connect. It gives an error "RemoteServer is not a recognized OPTIMIZER LOCK HINTS option"
Any ideas, without using stored procedures?
Cheers, Shawn
September 20, 2002 at 5:07 am
Actually the local @@ERROR should be set. Did you try it?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 20, 2002 at 6:29 am
Thanks for your reply
It turns out that SET XACT_ABORT ON had been issued, it was the only way to use BEGIN DISTRIBUTED TRANS, thus stoppng processing of the entire batch (including parent sp).
I've had words and traded a few insults and punches with the developers, and they're changing the processing to ensure all INSERTs etc are local, without the need for a XACT_ABORT. Any remote calls that can't be worked out are going to be via sps, and so they can use return variables rather than @@ERROR.
A victory for DBAs everywhere, but the war is not yet won.
Cheers
Shawn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply