Run dynamic SQL Server stored procedure with linked server

Question

This is a dynamic stored procedure that will pass the database, linked server and state. When executing this stored procedure, it runs the stored procedure on the database on the linked server and gives the results back.

Working code - here the linked server is absolute and not passed as a variable

EXECUTE MYPROC 'CA','MYDB'

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state varchar(2),
    @DATABASE char(20)
)
AS
    DECLARE @SQL @VARCHAR(MAX)
 
    SELECT @SQL = 'use ' + @DATABASE + ';
    SELECT * FROM pubs.dbo.authors WHERE state = @state'

    EXEC MYLINKSERVER.master.dbo.sp_executesql
              @SQL, N'@state char(2)', @state

Not working code: here the linked server is passed through a variable.

I get a "Syntax error" at @LINKEDSERVER**.**master

EXECUTE MYPROC 'CA','MYDB','MYLINKSERVER'

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state varchar(2),
    @DATABASE char(20),
    @LINKEDSERVER VARCHAR(20)
)
AS
    DECLARE @SQL @VARCHAR(MAX)

    SELECT @SQL = 'use ' + @DATABASE + ';
    SELECT * FROM pubs.dbo.authors WHERE state = @state'

    EXEC @LINKEDSERVER.master.dbo.sp_executesql
            @SQL, N'@state char(2)', @state

Thanks in advance

You cannot call your linked server as a variable name like you have it: @LINKEDSERVER.master.dbo.sp_executesql... Your entire statement will need to executed as dynamic SQL (e.g., EXEC ( 'your sql statement' ) ). — critical_error, Jul 09 '20 at 17:34
@CriticalError What is the alternative, as I need to run this stored procedure on multiple servers from a central location. — Aviral Goyal, Jul 09 '20 at 17:37
What's a trigger code? The dynamic code I'm simply running from a Nodejs application knex raw query where I'm simply executing (EXECUTE MYPROC 'CA','MYDB','MYLINKSERVER). This works without the MYLINKSERVER variable that is the former code but syntax error in the latter code. — Aviral Goyal, Jul 09 '20 at 17:43
https://support.microsoft.com/en-in/help/314520/how-to-pass-a-variable-to-a-linked-server-query -- this is the closest solution I found, but it's still incomplete. — Aviral Goyal, Jul 09 '20 at 17:49

score 1 · Answer 1 · answered Jul 09 '20 at 18:03

1

Try this in your SP:

DECLARE @SQL VARCHAR(MAX);
SET @SQL = FORMATMESSAGE ( 'SELECT * FROM [%s].[%s].[dbo].[authors] WHERE [state] = ''%s'';', @LINKEDSERVER, @DATABASE, @state );
EXEC ( @SQL );

This would create the following SQL statement to be executed based on your sample parameters above:

SELECT * FROM [MYLINKSERVER].[MYDB].[dbo].[authors] WHERE [state] = 'CA';

I'm not sure what version of SQL Server you're running, so you may not be able to use FORMATMESSAGE, however, I'm sure you're familiar with concatenating strings.

answered Jul 09 '20 at 18:03

critical_error

6,306
3
14
16

So the stored procedure I'm actually running is 500+ lines with 20 different parameters, I'm not quite sure how I'd convert that to this format. I'm running SQL server 2017. The basic idea is to maintain the code at a centralised location so I don't have to update the Stored Procedure on each and every instance. – Aviral Goyal Jul 09 '20 at 18:10
Is there an alternate means to create such an architecture? Where I don't need to update the stored procedure on each and every sql instance and database. I have the flexibility to not make it a dynamic procedure, it can be on the instance as well, but the problem remains how to update them all in one go. – Aviral Goyal Jul 09 '20 at 18:15
FORMATMESSAGE is available in SQL 2017. Regardless of the number of parameters passed to your procedure, to make these "linked" calls, you'll need LINKEDSEVER, DATABASE to call the proper linked server/location. If you build your dynamic SELECTs based on my example, this will allow you to centralize your code and simplify the statement you need to execute. – critical_error Jul 09 '20 at 18:15
As for simplification, I don't know your schema/setup, however, if it were me, I would have one SP that each requesting server called, passing in the expected parameters. Managing a single SP is much simpler than multiple. I suspect this will only be one of many updates you'll end up addressing over time. – critical_error Jul 09 '20 at 18:19
So there's one main server where the stored procedures are kept for now and there are multiple data servers linked to the main server. Now if i have to maintain the stored procedures on each data server, then the whole purpose of single copy of SP would get defeated. – Aviral Goyal Jul 09 '20 at 18:27
Alternatively, if I were to write the code the way you suggested, I could also write the same code inside a raw query in knex and simply call it from there. But that seems like a really cumbersome task owing to the number code lines and parameters involved. The way this code is structured right now, I dont have to worry about the squence of Parameters. – Aviral Goyal Jul 09 '20 at 18:27
Sounds like either way is cumbersome. You'll need to figure out which way is best for your setup. Regardless, you'll need dynamic SQL to accomplish your task--which my example demonstrates how to do. – critical_error Jul 09 '20 at 18:30
I've used the method you described in other places, I'll just wait a little longer to see if someone comes up with an alternate solution. But thanks for your help regardless, much appreciated! – Aviral Goyal Jul 09 '20 at 18:34

Weihui Guo · Accepted Answer · 2020-07-10T17:14:28.580

0

I prefer to use OPENQUERY as it is usually much faster than using four-part query. So instead of SELECT * FROM [MYLINKSERVER].[MYDB].[dbo].[authors] WHERE [state] = 'CA';, try this:

SELECT * FROM OPENQUERY([MYLINKSERVER], '
    SELECT * FROM [MYDB].[dbo].[authors] WHERE [state] = ''CA''
')

And your procedure will be something like this

CREATE PROCEDURE [dbo].[MYPROC]
(
    @state CHAR(2),
    @DATABASE VARCHAR(20),
    @LINKEDSERVER VARCHAR(20)
)
AS

DECLARE @SQL NVARCHAR(500)

SET @SQL = N'SELECT * FROM OPENQUERY(' + QUOTENAME(@LINKEDSERVER) + ', ''
    SELECT * FROM ' + QUOTENAME(@DATABASE) + '.dbo.authors WHERE state = ''''' + @state + '''''
'')'
EXEC SP_EXECUTESQL @SQL
--PRINT @SQL -- To see the final query to execute

Or you can use FORMATMESSAGE as the answer given by Critical Error.

SET @SQL = FORMATMESSAGE ('SELECT * FROM OPENQUERY([%s], ''
    SELECT * FROM [%s].[dbo].[authors] WHERE [state] = ''''%s'''''');', QUOTENAME(@LINKEDSERVER), QUOTENAME(@DATABASE), @state
);
EXEC (@SQL);

Use QUOTENAME to avoid SQL injection. As the other parameter is limited to char(2), I guess it should be safe.

edited Jul 10 '20 at 17:14

answered Jul 09 '20 at 20:13

Weihui Guo

3,669
5
34
56

You may be able to get away with "SELECT * FROM OPENQUERY ( @LINKEDSERVER, 'SELECT ...' );" without having to resort to the entire statement being dynamic and calling SP_EXECUTESQL. – critical_error Jul 09 '20 at 23:47
@CriticalError, thanks for the comment. Can you explain a bit more? I never used `FORMATMESSAGE` for dynamic SQL. – Weihui Guo Jul 10 '20 at 02:57
@WeihuiGuo Thank you for your solution, It seems more viable, from how the code is structured at the moment. But I'm unable to find clear documentation on OPENQUERY where I can learn about the number of Single Quotations, as there are quite a few you've used to define the parameters. Could you explain me a little or point me to a documentation that explains how to use them. – Aviral Goyal Jul 10 '20 at 06:10
@AviralGoyal I updated the answer by inserting some links. You can `PRINT @SQL` to see the final query. As for multiple single quotations, see if this [SO post](https://stackoverflow.com/questions/30129864/escape-single-quote-in-openquery-using-dynamic-query) helps. – Weihui Guo Jul 10 '20 at 12:37
@WeihuiGuo, ignore my comment. Your example is how it needs to be done as you cannot pass a variable for the linked server part of OPENQUERY. Dynamic SQL to the rescue. – critical_error Jul 10 '20 at 16:53

Run dynamic SQL Server stored procedure with linked server

2 Answers2