0

I am migrating several hundred stored procedures from one server to another, so I wanted to write a stored procedure to execute an SP on each server and compare the output for differences.

In order to do this, I would normally use this syntax to get the results into tables:

select * into #tmp1 from OpenQuery(LocalServer,'exec usp_MyStoredProcedure')
select * into #tmp2 from OpenQuery(RemoteServer,'exec usp_MyStoredProcedure')

I then would union them and do a count, to get how many rows differ in the results:

select * into #tmp3
from ((select * from #tmp1) union (select * from #tmp2))

select count(*) from #tmp1
select count(*) from #tmp3

However, in this case, my stored procedure contains an OpenQuery, so when I try to put the exec into an OpenQuery, the query fails with the error:

The operation could not be performed because OLE DB provider "SQLNCLI"
for linked server "RemoteServer" was unable to begin a distributed transaction.

Are there any good workarounds to this? Or does anybody have any clever ideas for things I could do to make this process go more quickly? Because right now, it seems that I would have to run the SP on each server, script the results into tmp tables, then do the compare. That seems like a poor solution!

Thank you for taking the time to read this, and any help would be appreciated greatly!

nosirrahcd
  • 1,467
  • 3
  • 18
  • 36

2 Answers2

0

I think your method would work - you just need to start the MSDTC. This behavior occurs if the Distributed Transaction Coordinator (DTS) service is disabled or if network DTC access is disabled. By default, network DTC access is disabled in Windows. When running and configured properly, the OLE DB provider would be able start the distributed transaction.

Check out this for instructions- it applies to any Windows Server 2003 or 2008.

Brian Knight
  • 4,970
  • 28
  • 34
0

Similar to your question. Insert results of a stored procedure into a temporary table

Community
  • 1
  • 1
JeffO
  • 7,957
  • 3
  • 44
  • 53