Frage Fehler: "INSERT EXEC-Anweisung kann nicht geschachtelt werden." Und "Die ROLLBACK-Anweisung kann nicht innerhalb einer INSERT-EXEC-Anweisung verwendet werden." Wie kann das Problem gelöst werden?


Ich habe drei gespeicherte Prozeduren Sp1, Sp2 und Sp3.

Der erste (Sp1) wird den zweiten ausführen (Sp2) und speichern Sie die zurückgegebenen Daten in @tempTB1 und der zweite wird den dritten ausführen (Sp3) und speichern Sie Daten in @tempTB2.

Wenn ich das ausführe Sp2 es wird funktionieren und es wird mir alle meine Daten aus der Sp3aber das Problem ist in der Sp1Wenn ich es ausführe, wird dieser Fehler angezeigt:

INSERT EXEC-Anweisung kann nicht geschachtelt werden

Ich habe versucht, den Ort zu ändern execute Sp2 und es zeigt mir einen anderen Fehler:

Die ROLLBACK-Anweisung kann nicht verwendet werden   innerhalb einer INSERT-EXEC-Anweisung.


76
2017-09-25 19:36


Ursprung


Antworten:


Dies ist ein häufiges Problem beim Versuch, Daten aus einer Kette gespeicherter Prozeduren zu "sprudeln". Eine Einschränkung in SQL Server ist, dass Sie nur jeweils einen INSERT-EXEC aktiv haben können. Ich empfehle es anzuschauen So geben Sie Daten zwischen gespeicherten Prozeduren frei Das ist ein sehr gründlicher Artikel über Muster, um diese Art von Problem zu umgehen.

Zum Beispiel könnte eine Umgehung sein, Sp3 in eine Tabellenwert-Funktion zu verwandeln.


77
2017-09-25 20:18



Dies ist der einzige "einfache" Weg, dies in SQL Server zu tun, ohne eine riesige, gewundene erstellte Funktion oder einen ausgeführten SQL-String-Aufruf, beides schreckliche Lösungen:

  1. Erstellen Sie eine temporäre Tabelle
  2. Openrowset Ihre gespeicherten Prozedurdaten hinein

BEISPIEL:

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

Hinweis: Sie müssen 'set fmtonly off' verwenden, UND Sie können weder innerhalb des Openrowset-Aufrufs, noch für die Zeichenfolge, die Ihre gespeicherten Prozedurparameter enthält, oder für den Tabellennamen dynamisch sql hinzufügen. Deshalb müssen Sie eine temporäre Tabelle anstelle von Tabellenvariablen verwenden, was besser wäre, da in den meisten Fällen die temporäre Tabelle ausgeführt wird.


15
2018-06-01 22:53



Meine Arbeit für dieses Problem war immer die Verwendung des Prinzips, dass einzelne Hashtemp-Tabellen für alle aufgerufenen Procs gelten. Also, ich habe einen Optionsschalter in den Prozessparametern (standardmäßig ausgeschaltet). Wenn diese Option aktiviert ist, fügt der aufgerufene proc die Ergebnisse in die temporäre Tabelle ein, die im aufrufenden Proc erstellt wurde. Ich denke, dass ich in der Vergangenheit einen Schritt weiter gegangen bin und etwas Code in den aufgerufenen proc geschrieben habe, um zu überprüfen, ob die einzelne Hash-Tabelle im Gültigkeitsbereich existiert, wenn es dann den Code einfügt, andernfalls gebe die Ergebnismenge zurück. Scheint gut zu funktionieren - beste Methode, große Datenmengen zwischen Procs zu übertragen.


4
2018-05-04 07:27



Ich fand eine Arbeit um einen der Prods in eine Tabellenwertfunktion umzuwandeln. Ich erkenne, dass das nicht immer möglich ist, und führt seine eigenen Beschränkungen ein. Ich konnte jedoch immer mindestens eines der Verfahren als einen geeigneten Kandidaten dafür finden. Ich mag diese Lösung, weil sie keine "Hacks" zur Lösung bringt.


4
2017-08-19 01:59



OK, ermutigt von Jimhark ist hier ein Beispiel für den alten Single-Hash-Table-Ansatz:

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

*/

3
2018-05-16 12:00



Dieser Trick funktioniert für mich.

Dieses Problem tritt auf dem Remoteserver nicht auf, da der letzte INSERT-Befehl auf dem Remoteserver auf die Ausführung des vorherigen Befehls wartet. Auf demselben Server ist das nicht der Fall.

Profitieren Sie von dieser Situation für eine Problemumgehung.

Wenn Sie die Berechtigung zum Erstellen eines Verbindungsservers haben, tun Sie es. Erstellen Sie denselben Server wie den Verbindungsserver.

  • Melden Sie sich in SSMS bei Ihrem Server an
  • Gehe zu "Server-Objekt"
  • Klicken Sie mit der rechten Maustaste auf "Verbindungsserver" und dann auf "Neuer Verbindungsserver".
  • Geben Sie im Dialogfeld einen beliebigen Namen für den Verbindungsserver an: zB: THISSERVER
  • Servertyp ist "Andere Datenquelle"
  • Anbieter: Microsoft OLE DB-Provider für SQL Server
  • Datenquelle: Deine IP, es kann auch nur ein Punkt (.) Sein, weil es localhost ist
  • Gehen Sie zum Reiter "Sicherheit" und wählen Sie den 3. "Wird mit dem aktuellen Sicherheitskontext des Logins erstellt"
  • Sie können die Serveroptionen (3. Registerkarte) bearbeiten, wenn Sie möchten
  • Drücken Sie auf OK, Ihr Verbindungsserver wird erstellt

Jetzt ist dein Sql-Befehl in der SP1

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

Glauben Sie mir, es funktioniert sogar wenn Sie dynamische Einfügung in SP2 haben


2
2017-11-22 18:09



Ich hatte das gleiche Problem und die Sorge um doppelten Code in zwei oder mehr Sprocs. Ich fügte ein zusätzliches Attribut für "Modus" hinzu. Dies ermöglichte die Existenz von gemeinsamem Code innerhalb eines Sprocs und den modusgesteuerten Fluss und die Ergebnismenge des Sprocs.


1
2018-04-13 20:15



Was ist mit der Ausgabe der statischen Tabelle? Mögen

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

Es ist nicht ideal, aber es ist so einfach und Sie müssen nicht alles neu schreiben.

AKTUALISIEREN: Die vorherige Lösung funktioniert nicht gut mit parallelen Abfragen (Async und Multiuser-Zugriff), daher verwende ich jetzt temporäre Tabellen

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

verschachtelt spGetData Inhalt der gespeicherten Prozedur

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

0
2018-02-03 12:09



Deklarieren Sie eine Ausgabecursorvariable zum inneren SP:

@c CURSOR VARYING OUTPUT

Dann deklarieren Sie einen Cursor c zu der Auswahl, die Sie zurückgeben möchten. Dann öffne den Cursor. Dann setze die Referenz:

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

NICHT schließen oder neu zuweisen.

Rufen Sie nun das innere SP von dem äußeren SP auf, das einen Cursorparameter wie folgt liefert:

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

Sobald die innere SP ausführt, Ihr @cOUT ist bereit zu holen. Schleife und dann schließen und freigeben.


0
2018-05-19 12:31



In SQL Server 2008 R2 hatte ich eine Diskrepanz in den Tabellenspalten, die den Rollback-Fehler verursacht haben. Es ging weg, als ich meine sqlcmd Tabellenvariable reparierte, die durch die Anweisung insert-exec aufgefüllt wurde, um mit dem übereinzustimmen, das vom gespeicherten proc zurückgegeben wurde. Es fehlte org_code. In einer Windows-cmd-Datei lädt es das Ergebnis der gespeicherten Prozedur und wählt es aus.

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"

-1
2017-11-29 06:16