Solved

MSDTC issue on linked server

Posted on 2013-05-21
13
1,226 Views
Last Modified: 2013-06-03
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,
0
Comment
Question by:UConn
  • 7
  • 5
13 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39186998
check if you have MSTDC configured, on both servers, like :MSDTC.tiff
0
 

Author Comment

by:UConn
ID: 39188007
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 39188135
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
 

Author Comment

by:UConn
ID: 39188158
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
 
LVL 18

Expert Comment

by:x-men
ID: 39188725
restart SQL Server Service
0
 

Author Comment

by:UConn
ID: 39188864
rebooted the server and restarted the service too.. no luck
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 18

Expert Comment

by:x-men
ID: 39189088
can you run distributed transaction on each of the servers (locally)?
0
 

Author Comment

by:UConn
ID: 39189186
yes, tested on both servers individually - and I can run distributed transactions on each of them
0
 

Author Comment

by:UConn
ID: 39189313
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
 

Author Comment

by:UConn
ID: 39202266
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
 
LVL 18

Expert Comment

by:x-men
ID: 39204748
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
 

Author Comment

by:UConn
ID: 39204858
yes.. we are using the same nomenclature as above, but no luck
0
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
ID: 39204887
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now