Error Executing Dynamic SQL with OpenRowSet

Question

I'm getting the following error when trying to execute dynamic SQL which includes OpenRowSet

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.". Msg 8180, Level 16, State 1, Line 83 Statement(s) could not be prepared. Msg 137, Level 15, State 2, Line 84 Must declare the scalar variable "@P1".

Here's a sample proc

    IF OBJECT_ID('dbo.usp_SomeProc') IS NULL
      EXEC ('CREATE PROCEDURE dbo.usp_SomeProc AS RETURN 0;');
    GO
    --DROP PROC dbo.usp_SomeProc
    ALTER PROCEDURE dbo.usp_SomeProc
        --Input Parameters
         @Param1    NVARCHAR(50)
        ,@Param2    INT
        ,@Param3    NVARCHAR(11)
    AS
    BEGIN
        SET NOCOUNT ON;

        SELECT Param1 = @Param1, Param2 = @Param2, Param3 = @Param3

        SET NOCOUNT OFF;

        RETURN 0;

    END;

    GO

Here's executing the proc with dynamic SQL - this works

DECLARE @P1 NVARCHAR(50)='Some-Nonsense-Text'
, @P2 INT=98765, @P3 NVARCHAR(11)='Meaningless'
, @SQL NVARCHAR(MAX)
SET @SQL = 'EXEC dbo.usp_SomeProc    @Param1=@P1
                                    ,@Param2=@P2
                                    ,@Param3=@P3'
--PRINT @SQL
EXEC sys.sp_executesql @SQL, N'@P1 NVARCHAR(50), @P2 INT, @P3 NVARCHAR(11)', @P1, @P2, @P3 ; 
GO

Executing an OpenRowSet statement not using Dynamic SQL works

DECLARE @P1 NVARCHAR(50)='Some-Nonsense-Text', @P2 INT=98765, @P3 NVARCHAR(11)='Meaningless'
SELECT * 
INTO dbo.SomeProcTest
FROM OPENROWSET (   'SQLNCLI','Server=<server>\<instance?;Trusted_Connection=yes;'
,'EXEC xStuff.dbo.usp_SomeProc
 @Param1=''Some-Nonsense-Text''
,@Param2=98765
,@Param3=''Meaningless'''
                        )
SELECT * FROM dbo.SomeProcTest
DROP TABLE dbo.SomeProcTest
GO

Executing the Proc with dynamic SQL and OPENROWSET Fails with above error

DECLARE @ServerInstance NVARCHAR(200) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(90))
    +'\' +CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(90)) ;
DECLARE @OpenRowSet NVARCHAR(MAX) ;
DECLARE @P1 NVARCHAR(50)='Some-Nonsense-Text', @P2 INT=98765, @P3 NVARCHAR(11)='Meaningless', @SQL NVARCHAR(MAX)


SET @SQL = 'EXEC xStuff.dbo.usp_SomeProc
                         @Param1=@P1
                        ,@Param2=@P2
                        ,@Param3=@P3'
--PRINT @SQL
SET @OpenRowSet = 
'
SELECT * 
INTO xStuff.dbo.SomeProcTest 
FROM OPENROWSET (   ''SQLNCLI''
                    ,''Server='+@ServerInstance +';Trusted_Connection=yes;''
                    ,'''+ @SQL +'''
                )' ;
--PRINT @OpenRowSet
EXEC sys.sp_executesql @OpenRowSet, N'@P1 NVARCHAR(50), @P2 INT, @P3 NVARCHAR(11)', @P1, @P2, @P3 ; 
SELECT * FROM xStuff.dbo.SomeProcTest
DROP TABLE xStuff.dbo.SomeProcTest
GO

Where am I going wrong?

`OPENROWSET` does not support parameters. Constructing the query dynamically isn't going to resolve that. In your first example, things work because the parameter assignments are part of the query text itself (so the query effectively has no parameters). There are [various workarounds](https://stackoverflow.com/a/3378592/4137916), but they all boil down to not using parameters in `OPENROWSET` itself. — Jeroen Mostert, Feb 21 '18 at 10:18
Clarification: the answer linked above deals specifically with `OPENQUERY`, not `OPENROWSET`. The basic idea is still the same (neither support parameters) but the solutions might need some tweaking, of course. — Jeroen Mostert, Feb 21 '18 at 10:26
Using the example in this answer (https://stackoverflow.com/a/13831792/3266499) I was able to modify my query to get it to work — Mazhar, Feb 21 '18 at 10:28

score 0 · Answer 1 · answered Feb 21 '18 at 10:30

Using the example in this answer I was able to modify my query to get it to work

It seems you can't pass variables to OPENROWSET, you need to modify the initial Query like so to get it to work

--Execute Proc with dynamic SQL and OPENROWSET
DECLARE @ServerInstance NVARCHAR(200) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(90))
    +'\' +CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(90)) ;
DECLARE @OpenRowSet NVARCHAR(MAX) ;
DECLARE @P1 NVARCHAR(50)='Some-Nonsense-Text', @P2 INT=98765, @P3 NVARCHAR(11)='Meaningless', @SQL NVARCHAR(MAX)

SET @SQL = 'EXEC xStuff.dbo.usp_SomeProc
                         @Param1=''''' +@P1+ '''''
                        ,@Param2=' +CAST(@P2 AS NVARCHAR(20))+ '
                        ,@Param3=''''' +@P3+ ''''''
--PRINT @SQL

SET @OpenRowSet = 
'
SELECT * 
INTO xStuff.dbo.SomeProcTest 
FROM OPENROWSET (   ''SQLNCLI''
                    ,''Server='+@ServerInstance +';Trusted_Connection=yes;''
                    ,'''+ @SQL +'''
                )' ;
--PRINT @OpenRowSet
EXEC sys.sp_executesql @OpenRowSet ;--
SELECT * FROM xStuff.dbo.SomeProcTest
DROP TABLE xStuff.dbo.SomeProcTest
GO

Error Executing Dynamic SQL with OpenRowSet

1 Answers1