Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete failing in distributed transaction on linked server Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 1:56 PM
Points: 2, 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,
Post #1457733
Posted Thursday, May 30, 2013 2:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 2,448, Visits: 2,988
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 . 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’! **
Post #1458083
Posted Thursday, May 30, 2013 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 1:56 PM
Points: 2, Visits: 35
What happens if there is a failure during this process?
We are using BEGIN COMMIT ROLLBACK to protect against this.
Post #1458284
Posted Sunday, June 2, 2013 11:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1459079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse