MSDTC issue on linked server

I am looking to transfer certain 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 Arhive 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,
UConnAsked:
Who is Participating?
 
x-menConnect With a Mentor IT super heroCommented:
is

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

acceptable?
0
 
x-menIT super heroCommented:
check if you have MSTDC configured, on both servers, like :MSDTC.tiff
0
 
UConnAuthor Commented:
Checked settings on both participating servers - and it is as mentioned above.
Still receiving the error:
OLE DB provider "SQLNCLI10" for linked server "xyz" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 6
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "xyz" was unable to begin a distributed transaction.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TempDBACommented:
Check Distributed Transaction Coordinator service is running , If not then start it.
To start Distributed Transaction Coordinator

1.Click WindowsButtion + R
2.Type services.msc in the run window then click enter or OK button
3.In the services window check Distributed Transaction Coordinator  service is running or not, if not then start it.

then run your Query

Ref:- http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ac4927d1-de9f-4837-b1a4-10ccb8f93ba3

Also check if it is configured to work through firewall
http://support.microsoft.com/kb/250367

Apart from above, do you apply any update for the sql server or windows recently?
0
 
UConnAuthor Commented:
Thank you.. but I have checked those too - The service is running and we have configured to work through firewall
It still doesn't work
0
 
x-menIT super heroCommented:
restart SQL Server Service
0
 
UConnAuthor Commented:
rebooted the server and restarted the service too.. no luck
0
 
x-menIT super heroCommented:
can you run distributed transaction on each of the servers (locally)?
0
 
UConnAuthor Commented:
yes, tested on both servers individually - and I can run distributed transactions on each of them
0
 
UConnAuthor Commented:
Reviewed the following articles and the issue seems to be related to the security context of the distributed transaction.

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp
http://www.sqlwebpedia.com/content/msdtc-troubleshooting
http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/f66a228d-0ae7-4e6e-a4ee-30a03279168b/

All MSDTC settings are correct according to these articles.
Still getting the error:
OLE DB provider "SQLNCLI10" for linked server "servername" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 14
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "servername" was unable to begin a distributed transaction.
0
 
UConnAuthor Commented:
I tried something a little different -
My previous code was
BEGIN DISTRIBUTED TRAN      
INSERT INTO Arhive 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 instead made it as
BEGIN DISTRIBUTED TRAN      
INSERT INTO Arhive table on Server B
            SELECT *
            FROM  Server A table WITH (NOLOCK)
            WHERE CREATED < @RUNDATE
     
            INSERT INTO Arhive table on Server B
            SELECT *
            FROM  Server A table WITH (NOLOCK)
            WHERE id = xyz
           
COMMIT TRAN
GO
The above statements with two inserts within BEGIN and COMMIT worked. It looks like the transaction fails upon touching DELETE portion of the script.
The delete does not seem to happen between servers and fails out on error as described above.
Please advise.
0
 
x-menIT super heroCommented:
according to MSDN example:
USE AdventureWorks2008R2;
GO
BEGIN DISTRIBUTED TRANSACTION;
-- Delete candidate from local instance.
DELETE AdventureWorks2008R2.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
-- Delete candidate from remote instance.
DELETE RemoteServer.AdventureWorks2008R2.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
COMMIT TRANSACTION;
GO

(http://msdn.microsoft.com/en-us/library/ms188386(v=sql.105).aspx)

should work.

are you using the [ServerInstance].[datbase].[schema].[tablename] nomenclature
0
 
UConnAuthor Commented:
yes.. we are using the same nomenclature as above, but no luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.