May 29, 2013 at 7:54 am
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,
May 30, 2013 at 2:32 am
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
May 30, 2013 at 9:27 am
What happens if there is a failure during this process?
We are using BEGIN COMMIT ROLLBACK to protect against this.
June 2, 2013 at 11:40 pm
A DELETE statement with the OUTPUT clause is just one statement. It will be rolled back completely. That includes the INTO part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply