I have a script that calls a stored procedure on a different SQL Server,using a Linked Server reference and an OPENQUERY()
statement. For demonstration, I have made the following procedure, on the remote server:
-- ON SQL_SVR Remote Server
CREATE PROC [dbo].[MyProc]
( @Pram_A NVARCHAR(4) OUTPUT
,@Pram_B NVARCHAR(4) OUTPUT
,@Pram_C NVARCHAR(4) OUTPUT
,@Pram_D NVARCHAR(4) OUTPUT
)
AS
SELECT
@Pram_A = 'foo'
,@Pram_B = 'bar'
,@Pram_C = 'this'
,@Pram_D = 'that'
GO
Now, I would like to call this procedure and retrieve all four values that it returns, so I have written the following script on the local server:
-- These variables are final resting places for my data
DECLARE
@Pram_1 NVARCHAR(4)
,@Pram_2 NVARCHAR(4)
,@Pram_3 NVARCHAR(4)
,@Pram_4 NVARCHAR(4);
DECLARE
@SQL NVARCHAR(MAX)
-- I will pass values from the remote proc to the dynamic sql through some output parameters, which are middle men between the remote Alpha and the local Numeric params
,@Parms NVARCHAR(MAX) = '@Pram_A1 NVARCHAR(4) OUTPUT, @Pram_B2 NVARCHAR(4) OUTPUT, @Pram_C3 NVARCHAR(4) OUTPUT, @Pram_D4 NVARCHAR(4) OUTPUT'
,@LinkedServer VARCHAR(50) = '[SQL_SVR]';
-- The dynamic sql expects to get the middle-men parameters, which it will fill with the values it gets for it's alpha-style params
SET @SQL = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''EXEC [dbo].[MyProc] @Pram_A = @Pram_A1 OUTPUT, @Pram_B = @Pram_B2,@Pram_C = @Pram_C3,@Pram_D = @Pram_D4'')';
-- Take whatever the remote proc puts into the middle-men params and put it into our local numeric params
EXEC sp_executesql @SQL, @Parms
,@Pram_A1 = @Pram_1 OUTPUT
,@Pram_B2 = @Pram_2 OUTPUT
,@Pram_C3 = @Pram_3 OUTPUT
,@Pram_D4 = @Pram_4 OUTPUT;
-- show the contents of the local, numeric params.
SELECT
@Pram_1 AS P1
,@Pram_2 AS P2
,@Pram_3 AS P3
,@Pram_4 AS P4;
So far as I am aware, this is all correct usage of the (admitedly slightly complex) parameter passing, declaring the local parameters, passing them to the dynamic sql and inside the dynamic sql using those declared on the remote script. However, when I run the above, I am given the following warning and error:
--OLE DB provider "SQLNCLI11" for linked server "PRODSQL-V2" returned message "Deferred prepare could not be completed.".
'Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Pram_A1".'
I have searched extensively for a solution, but everything I look at seems to imply that my script is correct. I know I must have misunderstood something and made a mistake but I cannot for the life of me see what! Can you see what I have done wrong or explain a possible reason why the script might be failing?
Note1: There are reasons for using dynamic sql and a variable server name that are not shown in the very simplified example script.
Note2: The local script is on SQL Server 14.0.3048.4, the remote on 13.0.4001.0
You can use the same parameter names in each "scope" which simplifies things, and you have to use sp_executesql
not OPENQUERY to bind output parameters. With OPENQUERY you'd have to return the values in a resultset.
So something like this:
-- ON SQL_SVR Remote Server
CREATE OR ALTER PROC [dbo].[MyProc]
( @Pram_A NVARCHAR(4) OUTPUT
,@Pram_B NVARCHAR(4) OUTPUT
,@Pram_C NVARCHAR(4) OUTPUT
,@Pram_D NVARCHAR(4) OUTPUT
)
AS
SELECT
@Pram_A = 'foo'
,@Pram_B = 'bar'
,@Pram_C = 'this'
,@Pram_D = 'that'
GO
-- These variables are final resting places for my data
DECLARE @Pram_A NVARCHAR(4)
,@Pram_B NVARCHAR(4)
,@Pram_C NVARCHAR(4)
,@Pram_D NVARCHAR(4);
declare @LinkedServer VARCHAR(50) = 'SQL_SVR';
declare @SQL NVARCHAR(MAX) = concat('
EXEC ',quotename(@LinkedServer),'.[TempDb].[dbo].[MyProc] @Pram_A = @Pram_A OUTPUT,
@Pram_B = @Pram_B OUTPUT,
@Pram_C = @Pram_C OUTPUT,
@Pram_D = @Pram_D OUTPUT
')
declare @Parms NVARCHAR(MAX) = '@Pram_A NVARCHAR(4) OUTPUT, @Pram_B NVARCHAR(4) OUTPUT, @Pram_C NVARCHAR(4) OUTPUT, @Pram_D NVARCHAR(4) OUTPUT'
exec sp_executesql @sql, @Parms, @Pram_A = @Pram_A OUTPUT,
@Pram_B = @Pram_B OUTPUT,
@Pram_C = @Pram_C OUTPUT,
@Pram_D = @Pram_D OUTPUT
SELECT
@Pram_A AS P1
,@Pram_B AS P2
,@Pram_C AS P3
,@Pram_D AS P4;