SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete failing in distributed transaction on linked server


Delete failing in distributed transaction on linked server

Author
Message
mdepascale-794609
mdepascale-794609
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
I am looking to "transfer" records from a table on Server A to a table on Server B. And then delete those records from table on Server A. So this way, table on Server B acts as a space to store archive records.
My process is a SQL Server Agent job running a stored procedure which accomplishes the above stated scenario.
My code is Insert into Server B table and subsequent delete from Server A table:

BEGIN DISTRIBUTED TRAN
INSERT INTO Archive table on Server B
SELECT *
FROM Server A table WITH (NOLOCK)
WHERE CREATED < @RUNDATE;

DELETE
FROM Server A table
WHERE CREATED < @RUNDATE;

COMMIT TRAN
GO

I get this error:
OLE DB provider "SQLNCLI10" for linked server "xyz" returned message "No transaction is active.".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Upon removing:
BEGIN DISTRIBUTED TRAN
COMMIT TRAN
GO
from the code - the job works
But I want to place these pieces of code to treat this process as one transaction.

I have tried several resolutions like:
Ping both servers: Yes
Linked Server Connection properties = TRUE: YES
Enable DTC over network: YES
Check registry to make sure MSDTC security turned off: YES
Is firewall blocking port 135 or higher level - checked - all open

Please advise
thank you,
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8950 Visits: 3718
I've had the same issues some time ago. In some cases it was a misconfigured MSDTC setting on one of the servers. But in some other cases I could not figure out the problem Crazy. In the end I rebuild the query to one single statement.
In your case it would be somthing like below:
DELETE FROM server A table
OUTPUT
deleted.[column names]
INTO archive table on server B



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
mdepascale-794609
mdepascale-794609
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
What happens if there is a failure during this process?
We are using BEGIN COMMIT ROLLBACK to protect against this.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8950 Visits: 3718
A DELETE statement with the OUTPUT clause is just one statement. It will be rolled back completely. That includes the INTO part.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search