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
– critical_error, Commented Jul 9, 2020 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
– Aviral Goyal, Commented Jul 9, 2020 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
– Aviral Goyal, Commented Jul 9, 2020 at 17:43
support.microsoft.com/en-in/help/314520/… -- this is the closest solution I found, but it's still incomplete. — Aviral Goyal
– Aviral Goyal, Commented Jul 9, 2020 at 17:49

critical_error · Accepted Answer · 2020-07-09 18:03:54Z

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 9, 2020 at 18:03

critical_error

6,8263 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Aviral Goyal Over a year ago

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 Over a year ago

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.

critical_error Over a year ago

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 Over a year ago

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.

Aviral Goyal Over a year ago

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.

|

Weihui Guo · Accepted Answer · 2020-07-10 17:14:28Z

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, 2020 at 17:14

answered Jul 9, 2020 at 20:13

Weihui Guo

4,0656 gold badges40 silver badges62 bronze badges

5 Comments

critical_error Over a year ago

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.

Weihui Guo Over a year ago

@CriticalError, thanks for the comment. Can you explain a bit more? I never used FORMATMESSAGE for dynamic SQL.

Aviral Goyal Over a year ago

@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.

Weihui Guo Over a year ago

@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 helps.

critical_error Over a year ago

@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.

Collectives™ on Stack Overflow

Run dynamic SQL Server stored procedure with linked server

2 Answers 2

8 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related