SQLNCLI error from synchronizing script

meklembl

I have two databases on my reporting server (SSRS). They contain stored procedures that our reports use. One is ReportingStoredProcedures. The other is ReportingStoredProcedures_DEV. I just created the ReportingStoredProcedures_DEV database. I used SQL Compare to generate a script to put the stored procedures and functions into ReportingStoredProcedures_DEV.

The Script fails on about half of the stored procedures and a third of the functions giving me the error “OLE DB provider "SQLNCLI" for linked server "HERCULES" returned message "The transaction manager has disabled its support for remote/network transactions.".”

I tested a stored procedure and the procedure runs properly in ReportingStoredProcedures. If I take the CREATE script for the stored procedure and run in it ReportingStoredProcedures_DEV, the scripts executes without error and the procedure runs properly.

Can you offer any assistance as to why the script fails within the script created from SQL Compare 8.2.0.16?

Chris Auckland

Thanks for your post.

I believe the reason you're getting this error is because by default, SQL Compare will script the transaction isolation level to be read committed, this causes problems with linked servers. If you manually change the script to:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Then it should probably be fine.

We have a feature request already in place and hope to resolve this issue with linked servers in a future version. (SC-1318).

I hope this helps.

meklembl

Thanks. That worked.