I have a question with respect to distributed transactions when carried out between two database server machines on the same network.
Consider there are two database server machines \SOURCE_DATABASE_SERVER\ and \DESTINATION_DATABASE_SERVER\. Now we have to archive the data from one database server to
other database server.
Let us suppose the database server \SOURCE_DATABASE_SERVER\ is the live production database server and \DESTINATION_DATABASE_SERVER\ is Long term storage database server.
Now data from \SOURCE_DATABASE_SERVER\ needs to be archived into the database server \DESTINATION_DATABASE_SERVER\.
So I have written a dynamic stored procedure which includes distributed transaction (begin Tran, commit Tran, rollback
Tran) coding in it.
Now initially when I executed the procedure, it gave some of the following errors
1) OLE DB provider "SQLNCLI" for linked server "\DESTINATION_DATABASE_SERVER\" returned message "The partner transaction manager has disabled its support for remote/network
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "\DESTINATION_DATABASE_SERVER\" was unable to begin a distributed transaction.
View Complete Post