오류 : "INSERT EXEC 문은 중첩 될 수 없습니다."

오류 : "INSERT EXEC 문은 중첩 될 수 없습니다." 및 "INSERT-EXEC 문 내에서 ROLLBACK 문을 사용할 수 없습니다." 이것을 해결하는 방법?

---

나는 세 가지 저장 프로 시저를 가지고 Sp1, Sp2하고 Sp3.

첫 번째 항목 ( Sp1)은 두 번째 항목 ( )을 실행하고 Sp2반환 된 데이터를에 저장 @tempTB1하고 두 번째 항목은 세 번째 항목 ( Sp3)을 실행하고 데이터를에 저장합니다 @tempTB2.

실행 Sp2하면 작동하고에서 내 모든 데이터를 반환 Sp3하지만 문제는에 있습니다. Sp1실행하면 다음 오류가 표시됩니다.

INSERT EXEC 문은 중첩 될 수 없습니다.

장소를 변경하려고했는데 execute Sp2또 다른 오류가 표시됩니다.

INSERT-EXEC 문 내에서 ROLLBACK 문을 사용할 수 없습니다.

---

이것은 저장 프로 시저 체인에서 데이터를 '버블 링'하려고 할 때 흔히 발생하는 문제입니다. SQL Server의 제한 사항은 한 번에 하나의 INSERT-EXEC 만 활성화 할 수 있다는 것입니다. 이러한 유형의 문제를 해결하기 위해 패턴에 대한 매우 철저한 문서 인 저장 프로 시저간에 데이터를 공유하는 방법을 살펴 보는 것이 좋습니다 .

예를 들어 해결 방법은 Sp3를 테이블 반환 함수로 바꾸는 것입니다.

---

이것은 거대한 복잡한 생성 함수 나 실행 된 SQL 문자열 호출없이 SQL Server에서이 작업을 수행하는 유일한 "간단한"방법입니다. 둘 다 끔찍한 솔루션입니다.

1. 임시 테이블 생성
2. 저장 프로 시저 데이터를 저장하십시오.

예:

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'를 사용해야하며 저장 프로 시저 매개 변수가 포함 된 문자열 또는 테이블 이름에 대해 openrowset 호출 내에서 여기에 동적 SQL을 추가 할 수 없습니다. 그렇기 때문에 테이블 변수 대신 임시 테이블을 사용해야합니다. 대부분의 경우 임시 테이블을 수행하므로 더 좋을 것입니다.

---

좋습니다. 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

*/

---

My work around for this problem has always been to use the principle that single hash temp tables are in scope to any called procs. So, I have an option switch in the proc parameters (default set to off). If this is switched on, the called proc will insert the results into the temp table created in the calling proc. I think in the past I have taken it a step further and put some code in the called proc to check if the single hash table exists in scope, if it does then insert the code, otherwise return the result set. Seems to work well - best way of passing large data sets between procs.

---

I found a work around is to convert one of the prods into a table valued function. I realize that is not always possible, and introduces its own limitations. However, I have been able to always find at least one of the procedures a good candidate for this. I like this solution, because it doesn't introduce any "hacks" to the solution.

---

This trick works for me.

You don't have this problem on remote server, because on remote server, the last insert command waits for the result of previous command to execute. It's not the case on same server.

Profit that situation for a workaround.

If you have the right permission to create a Linked Server, do it. Create the same server as linked server.

• in SSMS, log into your server
• go to "Server Object
• Right Click on "Linked Servers", then "New Linked Server"
• on the dialog, give any name of your linked server : eg: THISSERVER
• server type is "Other data source"
• Provider : Microsoft OLE DB Provider for SQL server
• Data source: your IP, it can be also just a dot (.), because it's localhost
• Go to the tab "Security" and choose the 3rd one "Be made using the login's current security context"
• You can edit the server options (3rd tab) if you want
• Press OK, your linked server is created

now your Sql command in the SP1 is

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

Believe me, it works even you have dynamic insert in SP2

---

I had the same issue and concern over duplicate code in two or more sprocs. I ended up adding an additional attribute for "mode". This allowed common code to exist inside one sproc and the mode directed flow and result set of the sproc.

---

what about just store the output to the static table ? Like

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

its not ideal, but its so simple and you don't need to rewrite everything.

UPDATE: the previous solution does not work well with parallel queries (async and multiuser accessing) therefore now Iam using temp tables

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

nested spGetData stored procedure content

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

---

Declare an output cursor variable to the inner sp :

@c CURSOR VARYING OUTPUT

Then declare a cursor c to the select you want to return. Then open the cursor. Then set the reference:

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

DO NOT close or reallocate.

Now call the inner sp from the outer one supplying a cursor parameter like:

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

Once the inner sp executes, your @cOUT is ready to fetch. Loop and then close and deallocate.

---

On SQL Server 2008 R2, I had a mismatch in table columns that caused the Rollback error. It went away when I fixed my sqlcmd table variable populated by the insert-exec statement to match that returned by the stored proc. It was missing org_code. In a windows cmd file, it loads result of stored procedure and selects it.

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"

참고URL : https://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s