A Problem with dynamically created distributed queries in SQL Server 2008 on Windows 7

Question

I'm just doing some stat collection on multiple servers, and as a test I'm working with my machine (Machine A) and another machine (Machine B) on the local network.

My Machine (A) is collecting all the information in the staging table from the other Machine (B). I have a sp that runs and dynamically creates something like this:

 exec ('exec SprocName ?', 1000) at [Machine B]

What above does is pull the information needed with 1000 row batches from Machine B. This will be looped until all the data is retrieved.

The next time it runs, with a different SprocName, it doesn't actually make the call to Machine B, sees the @@rowcount as 0 and moves on. It only runs the first sproc that makes it to the statement above.

so the psuedo code:

while (each sproc)
{
 set @qry =  exec ('exec SprocName ?', 1000) at [Machine B]
 while (rowcount <> 0)
 {  exec (@qry) }
}

I have tried this method before as 'select * from openquery([Machine B], 'exec SprocName @batchsize), but i was trying a different method this time around. Does anybody have a clue why exec () at Servername only wants to work with one sprocname? It will loop through and pull all the rows, but moving to the second sprocname apparently does not even call to Machine B.

I'm not going to use Servername.Table.Schema.Sproc for performance reasons.

Some stats:
Machine A - Windows 7 Sql Server 2008 SP1 no CU installed
Machine B - Windows 2003 Sql SErver 2005 SP3 no CU installed
Both have mostly all the MSDTC options on that pertain to this except XA transactions.

Thanks in advance if anyone actually understood my problem and can help.

Answer 1

I need to step away from the code for a bit every once and awhile... Came back and noticed a flaw in the looping logic. The psuedo code was mostly right... it didn't reset the @rowcount variable I was using..