Ошибка при выполнении динамического SQL с помощью OpenRowSet

Я получаю следующую ошибку при попытке выполнить динамический SQL, который включает OpenRowSet

Поставщик OLE DB «SQLNCLI10» для связанного сервера «(null)» вернул сообщение «Не удалось завершить отложенную подготовку». Сообщение 8180, уровень 16, состояние 1, строка 83. Выписка(я) не может быть подготовлена. Сообщение 137, уровень 15, состояние 2, строка 84 Необходимо объявить скалярную переменную "@P1".

Вот пример процесса

    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

Вот выполнение процедуры с динамическим SQL - это работает

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

Выполнение оператора OpenRowSet без использования динамического SQL работает

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

Выполнение Proc с динамическим SQL и 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=@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

Где я ошибаюсь?


sql-server tsql openrowset dynamic-sql
person Mazhar    schedule 21.02.2018    source источник
comment
OPENROWSET не поддерживает параметры. Динамическое построение запроса не решит эту проблему. В вашем первом примере все работает, потому что назначения параметров являются частью самого текста запроса (поэтому запрос фактически не имеет параметров). Существуют различные обходные пути, но все они сводятся к тому, чтобы не использовать параметры в самом OPENROWSET.   -  person Jeroen Mostert    schedule 21.02.2018
comment
Уточнение: ответ, указанный выше, касается конкретно OPENQUERY, а не OPENROWSET. Основная идея остается прежней (нет параметров поддержки), но, конечно, решения могут нуждаться в некоторой настройке.   -  person Jeroen Mostert    schedule 21.02.2018
comment
Используя пример в этом ответе (stackoverflow.com/a/13831792/3266499), я смог изменить свой запрос, чтобы получить это работать   -  person Mazhar    schedule 21.02.2018

Ответы (1)


arrow_upward
0
arrow_downward

Используя пример в этом ответе, я смог изменить свой запрос, чтобы заставить его работать

Кажется, вы не можете передавать переменные в OPENROWSET, вам нужно изменить исходный запрос, чтобы он работал

--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
person Mazhar    schedule 21.02.2018