Yes, I also reopened the enterprise manger and also none of the jobs did not run as well. Checked Job run times.

Tring to start enterprise manger with a sa account.

Getting...

Either the user, does not have permission to create a new object in Lserv-daba1, or the object does not exist.

Yes, the insert fails when I run the script in SSMS, becuase of the datatype. col2 is a bit.

I am intentionally inserting these values to insert the error message.

Following is...

Insert statement.

No, its not working with even one transaction.

No. I want them to be in a nested transaction.

any ideas?

yes, If I add a print stmt before each tranasaction and then if the third sql stmt fails... only the first two PRINT stmts are being printed and they are...

Level 11

Yes, SQLNCLI10.

When ever there is an error the transaction is simply being rolled back and getting the error I mentioned in the previous post.

NO hold on the code.

its not going to catch block at all.

I am getting the following error.

A Severe error occurred on the current command. The results, if any, should be discarded.

Thanks.

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...

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...

Does anyone have a sample code for this?

Thanks.