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?

Aaron Bertrand · Answer 1 · 2022-01-14T20:01:37.213

3

DECLARE @sql  nvarchar(max),
        @exec nvarchar(800) = QUOTENAME(@server_name)
                     + N'.' + QUOTENAME(@databaseName);

SET @sql = N'EXEC dbo.Foo;'
         + N'EXEC dbo.Bar;'
         + N'EXEC dbo.BigTime;';

EXEC @exec @sql;

edited Jan 14 '22 at 20:01

answered Nov 19 '09 at 23:49

Aaron Bertrand

272,866
37
466
490

It's master.sys.sp_executesql, btw. I really liked this idea, but to my astonishment, it's behaving very similar. Using SQL Profiler, I see that it fires the first one, then the second, then fires off sp_executesql with all three as the statement parameter!?! – Aaron Daniels Nov 20 '09 at 15:15
? This seems to work fine for me: EXEC master.dbo.sp_executeSQL N'print ''1'';'; ... as for the multiple calls, which server are you running profiler against, the one firing the commands or the one receiving them (@server_name)? – Aaron Bertrand Nov 20 '09 at 15:19
For testing, I was doing everything locally. I have a linked server that links to itself. I found it really weird that executing sp_executeSQL on the target linked server still resulted in each statement being split up with an sp_resetconnection call. – Aaron Daniels Dec 12 '09 at 22:26
@AaronBertrand How about getting output from one of them? I have a piece of dynamic sql that works great for output but I cant seem to get the output now from stringing them together exec? Any thoughts? – natur3 Apr 22 '15 at 21:43
@natur3 can you better define "output"? Do you mean consume a result set, or consume one or more proper output parameters? – Aaron Bertrand Apr 23 '15 at 00:10

Remus Rusanu · Answer 2 · 2009-11-19T23:55:54.037

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;

Remus, I posted something quite similar. Your declare line, however, will bomb out. — Aaron Bertrand, Nov 19 '09 at 23:50
Thanks Aaron, I fixed it. Serves me right for posting without even doing a syntax check... — Remus Rusanu, Nov 19 '09 at 23:56

score 0 · Answer 3 · answered Nov 19 '09 at 23:34

0

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?

answered Nov 19 '09 at 23:34

Cade Roux

88,164
40
182
265

I can't modify anything on the linked server. And yes, resetting the connection is causing a problem. – Aaron Daniels Nov 20 '09 at 14:47

score 0 · Answer 4 · answered Nov 19 '09 at 23:47

0

This may help:

BEGIN DISTRIBUTED TRANSACTION
 your stuff here
COMMIT TRANSACTION

For this you will need distributed transaction coordinator running.

answered Nov 19 '09 at 23:47

Damir Sudarevic

21,891
3
47
71

This didn't work. I get: "The server failed to resume the transaction. Desc:3500000006." returned. This was a good thought though. – Aaron Daniels Nov 20 '09 at 14:26

score 0 · Answer 5 · edited May 23 '17 at 11:55

0

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

edited May 23 '17 at 11:55

Community

1
1

answered Nov 20 '09 at 05:00

gbn

422,506
82
585
676

I didn't know about sp_setnetname actually. Is a neat trick, but can be dangerous in a concurrent environment, multiple callers can change the name on each other and wreak havoc. – Remus Rusanu Nov 20 '09 at 07:15
@remus: I used it as a DBA in limited circumstances, to be honest I didn't think it through. I'd use sp_getapplock to control it – gbn Nov 20 '09 at 07:31
I'm not trying to set the server name as a parameter. – Aaron Daniels Nov 20 '09 at 14:34
@Aaron: the same technique applies for dynamic server/database names – gbn Nov 20 '09 at 14:42

Executing multiple dynamic statements together over linked server

5 Answers5