Cannot use @@ERROR for distributed query

• gbn

  SSCertifiable

  Points: 6429

  More actions

  September 19, 2002 at 8:33 am

  #64263

  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

• Antares686

  SSC Guru

  Points: 125444

  More actions

  September 20, 2002 at 5:07 am

  #436960

  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)

• gbn

  SSCertifiable

  Points: 6429

  More actions

  September 20, 2002 at 6:29 am

  #436967

  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