Ошибки: "Инструкция INSERT EXEC не может быть вложенной". и "Невозможно использовать инструкцию ROLLBACK в инструкции INSERT-EXEC". Как это решить?

Ответ 1

Это обычная проблема при попытке "вывести" данные из цепочки хранимых процедур. Ограничением на SQL Server может быть только один INSERT-EXEC за один раз. Я рекомендую посмотреть Как делиться данными между хранимыми процедурами, что является очень тщательной статьей о шаблонах для решения этой проблемы.

Например, работа может заключаться в том, чтобы превратить Sp3 в табличную функцию.

Ответ 2

Это единственный "простой" способ сделать это в SQL Server без какой-либо гигантской запутанной созданной функции или выполнения строкового вызова sql, оба из которых являются ужасными решениями:

• создать временную таблицу
• открывает данные хранимой процедуры в нем

Пример:

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

Примечание. Вы ДОЛЖНЫ использовать "set fmtonly off", и вы НЕ МОЖЕТЕ добавить динамический sql к этому либо внутри вызова openrowset, либо для строки, содержащей параметры хранимой процедуры, или для имени таблицы. Вот почему вы должны использовать таблицу temp, а не переменные таблицы, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.

Ответ 3

Я нашел, что работа заключается в том, чтобы преобразовать один из prods в функцию с табличной оценкой. Я понимаю, что это не всегда возможно, и вводит свои собственные ограничения. Тем не менее, мне удалось найти хотя бы одну из процедур, подходящих для этого. Мне нравится это решение, потому что оно не вводит никаких "хаков" в решение.

Ответ 4

Моя работа для этой проблемы всегда заключалась в том, чтобы использовать принцип, согласно которому одиночные хеш-таблицы temp находятся в области видимости для любых вызванных процессов. Таким образом, у меня есть опция в параметрах proc (по умолчанию установлено значение off). Если это включено, вызываемый proc будет вставлять результаты в таблицу temp, созданную в вызывающем proc. Я думаю, что в прошлом я сделал еще один шаг и поместил некоторый код в вызываемый proc, чтобы проверить, существует ли единственная хеш-таблица в области видимости, если она вставляет код, иначе верните результирующий набор. Кажется, хорошо работает - лучший способ передачи больших наборов данных между procs.

Ответ 5

ОК, воодушевленный jimhark, приведен пример старого синтаксиса хеш-таблицы: -

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

Ответ 6

Этот трюк работает для меня.

У вас нет этой проблемы на удаленном сервере, потому что на удаленном сервере последняя команда вставки ожидает выполнения предыдущей команды. Это не так на том же сервере.

Принесите эту ситуацию для обходного пути.

Если у вас есть право на создание Linked Server, сделайте это. Создайте тот же сервер, что и связанный сервер.

• в SSMS, войдите в свой сервер
• перейти к "Объект сервера
• Щелкните правой кнопкой мыши на "Связанные серверы", затем "Новый связанный сервер"
• в диалоговом окне укажите любое имя вашего связанного сервера: например: THISSERVER
• Тип сервера - "Другой источник данных"
• Поставщик: поставщик Microsoft OLE DB для SQL-сервера
• Источник данных: ваш IP-адрес, это может быть просто точка (.), потому что это localhost
• Перейдите на вкладку "Безопасность" и выберите третий вариант "Будь сделана с использованием текущего контекста безопасности входа"
• Вы можете изменить параметры сервера (3-я вкладка), если хотите
• Нажмите OK, ваш связанный сервер создан.

теперь ваша команда Sql в пакете обновления 1

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Поверьте, он работает даже с динамической вставкой в ​​SP2

Ответ 7

У меня была такая же проблема и проблема с дублирующим кодом в двух или более sprocs. В итоге я добавил дополнительный атрибут для "mode". Это позволило использовать общий код внутри одного sproc и направленного потока и результата набора sproc.

Ответ 8

как просто сохранить вывод в статическую таблицу? Как

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

его не идеально, но его так просто и вам не нужно переписывать все.

UPDATE: предыдущее решение не работает с параллельными запросами (асинхронный и многопользовательский доступ), поэтому теперь я использую временные таблицы

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

вложенные spGetData содержимое хранимой процедуры

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1

Ответ 9

Объявить переменную курсора вывода для внутреннего sp:

@c CURSOR VARYING OUTPUT

Затем объявите курсор c для выбора, который вы хотите вернуть. Затем откройте курсор. Затем установите ссылку:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c 

НЕ закрывать или перераспределять.

Теперь вызовите внутренний sp из внешнего, указав параметр курсора, например:

exec sp_abc a,b,c,, @cOUT OUTPUT

После выполнения внутреннего sp, ваш @cOUT готов к извлечению. Loop, а затем закрыть и освободить.

Ответ 10

В SQL Server 2008 R2 у меня было несоответствие в столбцах таблицы, которые вызвали ошибку Rollback. Он ушел, когда я исправил переменную таблицы sqlcmd, заполненную оператором insert-exec, чтобы соответствовать возврату хранимой процедуры. Отсутствовал org_code. В файле cmd Windows загружает результат хранимой процедуры и выбирает ее.

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"