[Solved] No transaction is active message when accessing Linked Server

How to Solve

Error message

OLE DB provider “SQLNCLI10” for linked server “XXXXXXX” 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.

Dear All,

For one of our projects we had created Linked Server in MSSQL for two Database servers because the requirement to execute transactions to transfer data from one DB server to other

So done below settings  in Component Services  (dcomcnfg)

msdtc1

msdtc2

But still getting error

Error message

OLE DB provider “SQLNCLI10” for linked server “XXXXXXX” 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.

 

So checked with Virtual Server creation team and come to know they are using a template for creating VMS that means the Unique Identity for the MS DTC (SID) was same on both the local and the destination servers and the configuration of MS DTC was also propagated to all the servers where the same image was used.

SOLUTION Applied

  • Opened the Command Prompt as an Administrator and executed “msdtc -uninstall”
  • Deleted the following registry keys (after exporting them as a precautionary measure)

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC  ( not present in my case )
HKEY_CLASSES_ROOT\CID   ( not present in my case )
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

  • Executed “msdtc -install” command in the Command Prompt
  • Rebooted the server

And Error Done away J and ALL started well

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s