March 10, 2011 at 5:57 am
BEGIN DISTRIBUTED TRAN tran03092011
UPDTE srvrn.dbn.dbo.tbl1
SET name='test'
WHERE number=1
IF @@ERROR<>0
BEGIN
SELECT 'fail'
ROLLBACK TRANSACTION tran03092011
END
ELSE
BEGIN
SELECT 'success'
COMMIT ROLLBACK TRANSACTION tran03092011
END
Is this the correct way of doing it?
March 10, 2011 at 6:52 am
This is how I would do it:
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
UPDATE srvrn.dbn.dbo.tbl1
SET name = 'test'
WHERE number=1
IF XACT_STATE() = 1
BEGIN
SELECT 'success'
COMMIT
END
ELSE
BEGIN
SELECT 'Uncommittable transaction'
END
END TRY
BEGIN CATCH
ROLLBACK
SELECT 'fail'
END CATCH
If you are just updating the linked server's table (and no local table), it is not an explicit distributed transaction. However, depending on the provider, it could end up being handled by MSDTC anyway. This happens when the query is not passed through and the update is made by calling ::getData and then ::setData on a Rowset object.
SQL Server will handle automatically the promotion of the transcation to distributed, if necessary.
Hope this helps
Gianluca
-- Gianluca Sartori
March 10, 2011 at 2:48 pm
can we insert the error or error number into the database with an insert statement?
March 10, 2011 at 7:55 pm
sql_2005_fan (3/10/2011)
can we insert the error or error number into the database with an insert statement?
If I understand your question correctly, the answer is "Absolutely". And more.
Lookup Try/Catch in Books Online where they demonstrate a way to capture that infomation. All that would be left to do on your part is to convert it into a really simple INSERT.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2011 at 8:53 pm
OptionA:
With XACT_ABORT ON ,I tried to record the error message and rollback action into the database table but when there is an error the transaction automatically roll back and nothing is being recorded.
Option B:
Then I tried do the same with removiing XACT_ABORT ON , I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.
Still I am getting the error that nested transactions can not be peroformed.
So does any body have any sample code to record the error message with option A?
Thanks.
March 14, 2011 at 10:25 am
Does anyone have a sample code for this?
Thanks.
March 14, 2011 at 10:29 am
I'm sorry, I don't understand what error you are getting and against what code.
Can you clarify please?
-- Gianluca Sartori
March 14, 2011 at 10:35 am
I have a stored procedure that has a distributed transaction in it.
The transaction has 2 updates,1 delete and 1 Insert.
So for the distributed transaction I added XACT_ABORT ON in my SP code.
So if the Sp executed with no errors then it is commiting the entire transaction, but if there is an error the entire transaction is getting rolled back. I am good until this part.
But whenever the transaction fails I need to log the error that is being generated during the SP execution.
AS XACT_ABORT is set to ON I am not able to do this.
My question is how can I record the error into the database table with XACT_ABORT is set to ON.
Thanks.
March 14, 2011 at 10:45 am
sql_novice_2007 (3/14/2011)
I have a stored procedure that has a distributed transaction in it.
Can you post the code? (or at least part of it)
sql_novice_2007 (3/14/2011)
My question is how can I record the error into the database table with XACT_ABORT is set to ON.
You have to do it outside the transaction. I can't say anything more without seeing the code.
-- Gianluca Sartori
March 14, 2011 at 11:08 am
CREATE PROCEDURE [dbo].[UpdInsDeltrans]
AS
SET
NOCOUNT ON
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
UPDATE srvr2.db2.dbo.tbl_a
SET COL1=col1+10
UPDATE srvr2.db2.dbo.tbl_a
SET COL2=col2+6
INSERT INTO srvr2.db2.dbo.tbl_a(col1,col2)
VALUES(9999,8885)
DELETE FROM srvr2.db2.dbo.tbl_a
WHERE col1<100
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
INSERT INTO ResultLog (error,time)
VALUES(@@ERROR,getdate()
END
ELSE
BEGIN
COMMIT TRAN
INSERT INTO ResultLog (error,time)
VALUES('success;,getdate()
END
In the above code I am not able to record for the error part.
Thanks.
March 14, 2011 at 11:14 am
I would do it similar to this:
CREATE PROCEDURE [dbo].[UpdInsDeltrans]
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
BEGIN DISTRIBUTED TRAN
UPDATE srvr2.db2.dbo.tbl_a
SET COL1 = col1 + 10
UPDATE srvr2.db2.dbo.tbl_a
SET COL2 = col2 + 6
INSERT INTO srvr2.db2.dbo.tbl_a (col1, col2)
VALUES (9999, 8885)
DELETE FROM srvr2.db2.dbo.tbl_a
WHERE col1 < 100
COMMIT TRAN
INSERT INTO ResultLog (error, time)
VALUES ('success', getdate())
END TRY
BEGIN CATCH
ROLLBACK TRAN
INSERT INTO ResultLog (error, time)
VALUES (ERROR_MESSAGE(), getdate())
END CATCH
END
-- Gianluca Sartori
March 14, 2011 at 11:30 am
I am getting the following error.
A Severe error occurred on the current command. The results, if any, should be discarded.
Thanks.
March 14, 2011 at 11:35 am
its not going to catch block at all.
March 14, 2011 at 11:42 am
Which database product are you linked to? Is it SQL Server?
EDIT: Figured out, you are using SQLNCLI. Sorry, please ignore.
-- Gianluca Sartori
March 14, 2011 at 11:44 am
Is there any additional error message or the "Severe error" thing is all you get back?
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply