Executing multiple dynamic statements together over linked server

Question

I need to execute three dynamic SQL statements synchronously on a linked server (SQL Server 2005) like this:

declare @statement nvarchar(max);

set @statement = 'exec ' + @server_name + '.' + @database_name + '.dbo.Foo;exec ' + @server_name + '.' + @database_name + '.dbo.Bar;exec ' + @server_name + '.' + @database_name + '.dbo.BigTime';

exec sp_executesql @statement;

To test this, I have a linked server setup that links to itself.

When executing this local (by removing the @server_name), using SQL Profiler I see that it executes just fine as one statement. However, when I execute this via the linked server (using the @server_name variable), I see in SQL Profiler that each statement gets executed separately, with sp_reset_connection getting executed after each one.

The trace looks something like this:

Audit Login ....
exec database.dbo.Foo
exec sp_reset_connection
Audit Logout
Audit Login ....
exec database.dbo.Bar
exec sp_reset_connection
Audit Logout
Audit Login ....
exec database.dbo.BigTime
exec sp_reset_connection
Audit Logout

This is causing me problems. Is there any why I can specify to SQL Server to not call sp_reset_connection between statements?

Answer 1

Can you not make a single SP on the linked server which calls the other 3 and then just call that one?

Why does the reset connection cause a problem? Does each SP use some special kind of connection persistent storage like a temp table or something?

Answer 2

You are executing three calls to three linked servers, the result is exactly what you should be expecting (even if the 3 linked servers are not distinct). To achieve what describe, execute the calls as you describe: execute three procedures on one linked server:

linkedserver.master.dbo.sp_ExecuteSQL N'
  exec dbname.dbo.Foo; 
  exec dbname.dbo.Bar; 
  exec dbname.dbo.BigTime;';

All you have to do is wrap this around in a dynamic built SQL:

declare @remoteStatement nvarchar(max), @localStatement nvarchar(max);
set @remoteStatement = N'exec ' + @database_name + N'.dbo.Foo; 
    exec ' + @database_name + N'.dbo.Bar; 
    exec '+ @database_name + N'.dbo.BigTime';
set @localStatement = @server_name + N'.master.dbo.sp_executesql @remoteStatement;';

exec sp_executesql @localStatement, N'@remoteStatement nvarchar(max)', @remoteStatement;

Answer 3

This may help:

BEGIN DISTRIBUTED TRANSACTION
 your stuff here
COMMIT TRANSACTION

For this you will need distributed transaction coordinator running.

Answer 4

DECLARE @sql1 NVARCHAR(MAX), @sql2 NVARCHAR(MAX);

SET @sql1 = 'EXEC ' + @database_name + '.dbo.Foo;'
          + 'EXEC ' + @database_name + '.dbo.Bar;'
          + 'EXEC ' + @database_name + '.dbo.BigTime;';

SET @sql2 = 'EXEC ' + @server_name + '.master.dbo.sp_executeSQL ''' + @sql1 + ''';';

EXEC sp_executeSQL @sql2;

Answer 5

As per Remus' answer, but avoids some dynamic SQL using sp_setnetname. YMMV.

This was asked a day or 2 ago: Fully qualified table names with SP_ExecuteSql to access remote server