오류: "INSERT EXEC 문을 중첩할 수 없습니다." 및 "INSERT-EXEC 문 내에서 ROLLBLL 문을 사용할 수 없습니다."어떻게 해결할까요?

오류: "INSERT EXEC 문을 중첩할 수 없습니다." 및 "INSERT-EXEC 문 내에서 ROLLBLL 문을 사용할 수 없습니다."어떻게 해결할까요?

저장 .Sp1,Sp2 ★★★★★★★★★★★★★★★★★」Sp3.

번째 것 「 」 「 」 )Sp1는 두 번째( )를합니다.Sp2 ) 및 를 에 저장합니다.)에 저장합니다.@tempTB1 두 가 세 한다.Sp3및 에 합니다.@tempTB2.

★★★★★★★★★★★★★를 실행했을 경우,Sp2됩니다.Sp3, 는 ' ' '에 있습니다Sp1실행 시 다음 오류가 표시됩니다.

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

는 소장 of of of of of of의 위치를 했다.의 위치를 .execute Sp2또 다른 . 즉, '오류입니다.

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

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

예를 들어 Sp3를 테이블 값 함수로 바꾸는 방법이 있습니다.

SQL Server에서 이 작업을 수행할 수 있는 유일한 "간단한" 방법이며, 이 방법에는 몇 가지 거대한 복잡한 생성 함수나 실행된 SQL 문자열 호출이 포함되어 있지 않습니다.이 두 가지 모두 끔찍한 솔루션입니다.

1. 임시 테이블을 작성하다
2. openrow저장 프로시저 데이터 설정

예:

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을 추가할 수 없습니다.따라서 테이블 변수보다 temp 테이블을 사용해야 합니다.대부분의 경우 out이 temp 테이블을 실행하므로 이것이 더 나았을 것입니다.

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

*/

이 문제에 대한 저의 대처법은 항상 단일 해시 임시 테이블이 모든 procs에 적용된다는 원칙을 사용하는 것이었습니다.따라서 proc 파라미터에 옵션스위치가 있습니다(디폴트는 off로 설정되어 있습니다).이것이 켜져 있는 경우, 착신측 proc는 콜 프로시저에서 작성된 temp 테이블에 결과를 삽입합니다.과거에는 한 걸음 더 나아가 하나의 해시 테이블이 스코프에 존재하는지 여부를 확인하기 위해 호출된 proc에 코드를 삽입한 후 코드를 삽입하고 그렇지 않으면 결과 세트를 반환한 적이 있다고 생각합니다.잘 작동하는 것 같습니다. 큰 데이터 세트를 프로세서 간에 전달하는 가장 좋은 방법입니다.

이 속임수는 나에게 통한다.

리모트 서버에서는 이 문제가 발생하지 않습니다.리모트 서버에서는 마지막 insert 명령어가 이전 명령어의 결과가 실행될 때까지 대기하기 때문입니다.같은 서버에서는 그렇지 않습니다.

이 상황을 회피책으로 활용하십시오.

Linked Server를 생성할 수 있는 적절한 권한이 있는 경우 이 작업을 수행합니다.링크된 서버와 동일한 서버를 만듭니다.

• SSMS에서 서버에 로그인합니다.
• "Server Object"로 이동합니다.
• [ Linked Servers ]우클릭 후 [New Linked Server]을 클릭합니다.
• 대화상자에서 링크된 서버의 이름을 지정합니다(예: THISSERVER).
• 서버 유형은 "기타 데이터 원본"입니다.
• 공급자: SQL 서버용 Microsoft OLE DB 공급자
• 데이터 소스: IP는 localhost이기 때문에 닷(.)일 수도 있습니다.
• "보안" 탭으로 이동하여 세 번째 "로그인의 현재 보안 컨텍스트를 사용하여 작성"을 선택합니다.
• 필요에 따라 서버 옵션(3번째 탭)을 편집할 수 있습니다.
• [확인]을 누릅니다.링크된 서버가 생성됩니다.

SP1의 SQL 명령어는

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

SP2에 다이내믹 인서트 기능이 탑재되어 있어도 동작합니다.

프로세서 중 하나를 테이블 밸류 함수로 변환하는 작업이 있습니다.그것이 항상 가능한 것은 아니라는 것을 깨닫고, 그 자체의 한계를 소개합니다.하지만, 저는 항상 이 작업에 적합한 절차 중 적어도 하나를 찾을 수 있었습니다.이 솔루션은 솔루션에 "핵"을 도입하지 않기 때문에 마음에 듭니다.

Stored Proc 결과를 temp 테이블로 Import하려고 할 때 Stored Pro가 자체 작업의 일부로 temp 테이블에 삽입했을 때 이 문제가 발생했습니다.이 문제는 SQL Server에서 동시에 두 개의 다른 임시 테이블에 동일한 프로세스를 쓸 수 없다는 것입니다.

승인된 OPENROWSET 답변은 정상적으로 동작하지만 프로세스에서 Dynamic SQL 또는 외부 OLE 공급자를 사용하지 않도록 해야 했기 때문에 다른 경로를 선택하게 되었습니다.

간단한 회피책 중 하나는 저장 프로시저의 임시 테이블을 테이블 변수로 변경하는 것입니다.TEMP 테이블과 동일하게 동작하지만 다른 TEMP 테이블 삽입과 경합하지 않습니다.

코멘트를 회피하기 위해 몇 분께서 곧 글을 쓰시겠지만 퍼포먼스 킬러로서 테이블 변수에서 벗어나라고 경고해 주셨으면 합니다.내가 말할 수 있는 것은 2020년에는 테이블 변수를 두려워하지 않기 위해 배당금을 지급한다는 것이다.2008년의 경우, 내 데이터베이스가 16GB RAM을 탑재하여 5400RPM HDD에서 실행되고 있는 서버 상에서 호스트된 경우, 나는 당신의 의견에 동의할 수 있습니다.하지만 지금은 2020년이고 SSD 어레이를 프라이머리 스토리지로 사용하고 수백 기가바이트의 RAM을 사용하고 있습니다.회사 전체의 데이터베이스를 테이블 변수에 로드할 수 있지만 여유 RAM은 여전히 충분합니다.

Table Variables(Table Variables)

나는 이 기사를 전부 읽을 것을 추천한다.다음은 해당 기사의 질문에 대한 가장 적절한 섹션입니다.

롤백 및 오류 처리가 어려움

SQL Server의 오류 및 트랜잭션 처리에 대한 내 기사에서 항상 다음과 같은 오류 핸들러를 사용할 것을 권장합니다.

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

이 절차에서 트랜잭션을 시작하지 않더라도 계약을 이행할 수 없는 경우에는 트랜잭션이 유효하지 않기 때문에 항상 Rollback을 포함해야 합니다.

아쉽게도 INSERT-EXEC에서는 잘 동작하지 않습니다.호출된 프로시저가 ROLLBLL 문을 실행하는 경우 다음과 같이 됩니다.

Msg 3915, Level 16, State 0, Procedure SalesByStore, Line 9 Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

저장 프로시저의 실행이 중단됩니다.CATCH 핸들러가 없는 경우 배치 전체가 중단되고 트랜잭션이 롤백됩니다.INSERT-EXEC이 TRY-CATCH 내부에 있는 경우 해당 CATCH 핸들러는 실행되지만 트랜잭션은 중단되므로 롤백해야 합니다.결과적으로 롤백은 요구대로 이루어지지만 롤백을 트리거한 원래 오류 메시지가 손실됩니다.사소한 문제처럼 보일 수 있지만, 트러블 슈팅이 훨씬 더 어려워집니다.이 에러를 보면, 뭔가 잘못되었다는 것을 알 수 있을 뿐, 무엇이 잘못되었는지 알 수 없기 때문입니다.

두 개 이상의 sproc에서 동일한 문제와 코드가 중복되는 것에 대한 우려가 있었습니다.mode의 Atribut을 추가했습니다.이를 통해 공통 코드가 하나의 spro 내에 존재하며 spro의 모드 방향 흐름 및 결과 세트 내에 존재할 수 있습니다.

출력을 정적 테이블에 저장하는 건 어때?

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

이상적이지는 않지만 매우 간단해서 모든 것을 다시 쓸 필요는 없습니다.

업데이트: 이전 솔루션은 병렬 쿼리(async 및 다중 사용자 액세스)에서 제대로 작동하지 않으므로 현재 임시 테이블을 사용합니다.

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

출력 커서 변수를 내부 sp에 선언합니다.

@c CURSOR VARYING OUTPUT

그런 다음 반환할 선택 항목에 커서 c를 선언합니다.그런 다음 커서를 엽니다.그런 다음 기준을 설정합니다.

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

닫거나 재할당하지 마십시오.

이제 커서 파라미터를 제공하는 외부 SP에서 내부 SP를 호출합니다.

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

내부 SP가 실행되면@cOUT가져올 준비가 되었습니다.루프 후 닫은 후 할당을 해제합니다.

C#과 같은 다른 관련 기술을 사용할 수 있다면 Transaction 파라미터와 함께 built-in SQL 명령어를 사용하는 것이 좋습니다.

var sqlCommand = new SqlCommand(commandText, null, transaction);

이 기능을 보여주는 간단한 콘솔 앱을 만들었습니다.https://github.com/hecked12/SQL-Transaction-Using-C-Sharp

즉, C#을 사용하면 각 저장 프로시저의 출력을 검사하고 원하는 대로 출력을 사용할 수 있는 이 제한을 극복할 수 있습니다. 예를 들어 다른 저장 프로시저에 출력을 공급할 수 있습니다.출력이 정상이면 트랜잭션을 커밋할 수 있습니다.그렇지 않으면 롤백을 사용하여 변경을 되돌릴 수 있습니다.

SQL Server 2008 R2에서 테이블 열이 일치하지 않아 롤백 오류가 발생했습니다.insert-exec 문에 의해 채워진 sqlcmd 테이블 변수를 저장된 proc에 의해 반환된 변수와 일치하도록 수정하면 사라집니다.org_code가 없었습니다.Windows cmd 파일에서는 저장 프로시저의 결과를 로드하여 선택합니다.

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