July 22, 2009 at 8:36 pm
Hi, I was hoping somebody can help as i can't find the exact issue / work around.
The scenario is:
SQL 2008 Standard (guessing the issue could be the same in SQL 2005)
SQL 2000 Sandard
I have a linked server set up between them.
I am running code from the SQL 2008 side.
I am getting the "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction" error.
The basic of the code is:
SET XACT_ABORT ON
Begin Try
BEGIN TRAN
insert into sql2000server.db.dbo.tablename(..) -- SQL 2000 is the remote server
select statement
commit
End TRY
begin CAtch
some code
end Catch
I know there will be a primary key violation that will occur from my insert and if I remove the "Begin Tran" I get the error back and handled correctly.
But in a DT it cancels the transaction provided no feedback to be caught by the catch block.
I notice there is a note in BOL that explains this scenario but I do notr know a way around it or too be fair completely understand what the issue is or if it is a bug or "by design".
Is the issue the SQL 2000 end with the difference in error handling???
I can put a stored procedure on the SQL 2000 side, call this from the SQL 2008 side and pull the data into SQL 2000, begin a transaction and insert it with the correct error occuring.
The problem then is I can get the error number using the SQL 2000 limited Error Handling but do not know how to get capture the full message and get it back to the initial calling 2008 instance so it can be handled in the code flow as appropriate.
Any ideas on how best to handle this??
Thanks
July 28, 2009 at 9:59 pm
Be proactive, do a if exists check on the key value before the insert and then you will know before the insert whether it would would fail or not. It seems that it is easy enough to check and then handle the case where we know it won't work..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply