September 4, 2014 at 9:59 pm
Hi,
I have a SQL Server procedure running on 2012 with two insert statements. Procedure runs on local DB. Statements in procedure are enclosed in a transaction. First insert statement inserts data into a table on local DB and Second insert statement inserts data into a table on remote DB via Linked Server as distributed transaction. Local DB is on Server 01 and Remote DB is on Server 02.
As two inserts are part of distributed transaction, Atomicity is maintained which is good i.e. commit both inserts or rollback everything. But if second insert fails, then error is not captured. When second insert fails, DTC kills the procedure before even executing the first statement in CATCH block. Due to this, errors are not captured in distributed transaction.
If i remove distributed transaction, i.e. run both inserts in the procedure without enclosing between BEGIN TRAN and COMMIT, error is captured when second insert fails. But the problem is, first insert is already committed in local DB. In this case, Atomicity is not maintained.
How can i build the procedure with two inserts - one on local DB and other on Remote DB - to maintain both Atomicity and to capture errors if arise?
Appreciate your responses..
September 8, 2014 at 12:41 am
The error handling has to be done both at the remote server and at the local server. From MSDTC's perspective, the two or more servers (involved in a distributed transaction) may not handle errors in an identical manner (even between two different versions of SQL Server). Even worse (no offense intended), one server could be Oracle and the other server could be SQL Server. In short, MSDTC was not designed solely for SQL Server's use.
Untested by me, but if Server 02 is a SQL Server, you could instead execute a stored procedure that was created on Server 02. Try...catch error handling can then be done within that stored procedure (1205, 2627, etc). Alternative/additional features to consider are REMOTE_PROC_TRANSACTIONS and XACT_ABORT. If Server 01 and 02 are both SQL Servers, I expect uses of a stored procedure to mean Server 02's distribution statistics cannot be harnessed by/are not useful to Server 01. I am unsure how/whether MSDTC can handle passing a table as a parameter to a stored procedure (via a user-defined data type).
A little off topic, but even for a one-server transaction, a try...catch block will fail to catch sufficiently high severity errors. And a few years ago I saw a linked server created on a production server to a test server "cause" a production failure: The production server experienced massive blocking at a time when the test server was being tested for "its" response to a network failure ... ;). A little more on topic: No matter what you do, there will still be times when both systems must be checked for errors :).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply