Errori: “L’istruzione INSERT EXEC non può essere nidificata.” E “Imansible utilizzare l’istruzione ROLLBACK all’interno di un’istruzione INSERT-EXEC”. Come risolvere questo?

Ho tre stored procedure Sp1 , Sp3 e Sp3 .

Il primo ( Sp1 ) eseguirà il secondo ( Sp2 ) e salverà i dati restituiti in @tempTB1 e il secondo eseguirà il terzo ( Sp3 ) e salverà i dati in @tempTB2 .

Se Sp3 lo Sp3 , funzionerà e mi restituirà tutti i miei dati dallo Sp3 , ma il problema è nello Sp1 , quando lo Sp3 verrà visualizzato questo errore:

L’istruzione INSERT EXEC non può essere annidata

Ho provato a cambiare il luogo execute Sp2 e mi mostra un altro errore:

Imansible utilizzare l’istruzione ROLLBACK all’interno di un’istruzione INSERT-EXEC.

Questo è un problema comune quando si tenta di “bollare” i dati da una catena di stored procedure. Una restrizione in SQL Server è che è ansible avere solo un INSERT-EXEC attivo alla volta. Raccomando di consultare Come condividere i dati tra le stored procedure, che è un articolo molto approfondito sui modelli per aggirare questo tipo di problema.

Ad esempio, una soluzione potrebbe essere quella di trasformare Sp3 in una funzione con valori di tabella.

Questo è l’unico modo “semplice” per farlo in SQL Server senza una gigantesca funzione creata complicata o chiamata stringa sql eseguita, entrambe soluzioni terribili:

  1. creare una tabella temporanea
  2. aprire i dati memorizzati della procedura in esso

ESEMPIO:

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

Nota : DEVI usare ‘set fmtonly off’, E NON PUO ‘aggiungere sql dinamico a questo sia all’interno della chiamata openrowset, sia per la stringa contenente i parametri della stored procedure o per il nome della tabella. Ecco perché è necessario utilizzare una tabella temporanea piuttosto che variabili di tabella, che sarebbe stato meglio, dato che nella maggior parte dei casi esegue la tabella temporanea.

Il mio modo di aggirare questo problema è sempre stato quello di utilizzare il principio secondo cui le singole tabelle temporanee hash sono in scope per qualsiasi proc chiamato. Quindi, ho un’opzione di commutazione nei parametri proc (default impostato su off). Se è triggersto, il proc chiamato inserirà i risultati nella tabella temporanea creata nel proc chiamante. Penso che in passato ho fatto un ulteriore passo avanti e ho inserito del codice nel proc chiamato per verificare se la singola tabella hash esiste nell’oscilloscopio, se invece inserisce il codice, altrimenti restituisce il set di risultati. Sembra funzionare bene: il modo migliore per passare grandi quantità di dati tra i proc.

Ho trovato un lavoro intorno a convertire uno dei prods in una funzione di valore di tabella. Mi rendo conto che non è sempre ansible e introduce i suoi limiti. Tuttavia, sono stato in grado di trovare sempre almeno una delle procedure un buon candidato per questo. Mi piace questa soluzione, perché non introduce alcun “hack” per la soluzione.

OK, incoraggiato da jimhark ecco un esempio del vecchio approccio alla tabella hash: –

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

Questo trucco funziona per me.

Non si dispone di questo problema sul server remoto, perché sul server remoto, l’ultimo comando di inserimento attende il risultato del comando precedente da eseguire. Non è il caso sullo stesso server.

Approfitta di questa situazione per una soluzione alternativa.

Se hai il diritto di creare un server collegato, fallo. Crea lo stesso server del server collegato.

  • in SSMS, accedi al tuo server
  • andare su “Oggetto server
  • Fare clic destro su “Server collegati”, quindi “Nuovo server collegato”
  • nella finestra di dialogo, fornire qualsiasi nome del server collegato: ad esempio: THISSERVER
  • il tipo di server è “Altre origini dati”
  • Provider: Provider Microsoft OLE DB per server SQL
  • Fonte dei dati: il tuo IP, può essere anche solo un punto (.), Perché è localhost
  • Vai alla scheda “Sicurezza” e scegli il terzo “Fatti utilizzando il contesto di sicurezza corrente del login”
  • Puoi modificare le opzioni del server (3a scheda) se lo desideri
  • Premere OK, il server collegato è stato creato

ora il tuo comando Sql in SP1 è

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

Credimi, funziona anche con l’inserimento dinamico in SP2

Ho avuto lo stesso problema e preoccupazione per il codice duplicato in due o più sproc. Ho finito per aggiungere un attributo aggiuntivo per “modalità”. Questo ha permesso l’esistenza di un codice comune all’interno di uno sproc e il stream diretto alla modalità e il set di risultati dello sproc.

che dire solo archiviare l’output nella tabella statica? Piace

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

non è l’ideale, ma è così semplice e non è necessario riscrivere tutto.

AGGIORNAMENTO : la soluzione precedente non funziona bene con le query parallele (accesso asincrono e multiutente) quindi ora Iam utilizzando le tabelle temporanee

 -- 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 stored procedure spGetData nidificato

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

Dichiara una variabile del cursore di output sulla sp interna:

 @c CURSOR VARYING OUTPUT 

Quindi dichiarare un cursore c alla selezione che si desidera restituire. Quindi apri il cursore. Quindi imposta il riferimento:

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

NON chiudere o riallocare.

Ora chiama lo sp interno da quello esterno fornendo un parametro del cursore come:

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

Una volta eseguito lo sp interno, @cOUT è pronto per essere @cOUT . Fai un loop e poi chiudi e rilascia.

In SQL Server 2008 R2, ho riscontrato una mancata corrispondenza nelle colonne della tabella che ha causato l’errore di rollback. È andato via quando ho corretto la mia variabile di tabella sqlcmd popolata dall’istruzione insert-exec in modo che corrisponda a quella restituita dal proc memorizzato. Mancava org_code. In un file cmd di Windows, carica il risultato della stored procedure e la seleziona.

 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"