Wie vermeide ich die “ Eine INSERT EXEC-Anweisung kann in dieser Situation nicht verschachtelt werden? ” Ausnahme?

Der Quellcode aufrufen würde für Aaron „s sp_foreachdb enthält die folgende Zeile:

INSERT #x EXEC sp_executesql @sql;

Entsprechend Ihrer Fehlermeldung:

Eine INSERT EXEC-Anweisung kann nicht verschachtelt werden.

Also Code wie der folgende ist nicht gültig, da er INSERT xxx EXEC xxx Code verschachtelt.

INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" 

Kommentare

• @marcellomiorelli Ich denke, es würde eine große Änderung an Aaron ‚ s SP erfordern, um das dynamische SQL zu entfernen. Dies würde wahrscheinlich die Verwendung von erfordern eine zusätzliche Funktion.

Eine Verbindung mit Ihrem eigenen Server über verteilte Abfragemethoden würde den Trick tun, wenn sp_foreachdb würde eine einzelne Ergebnismenge zurückgeben.

create table #temp ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); insert into #temp select * FROM OPENROWSET("SQLNCLI", "SERVER=****;UID=****;PWD=****", " exec sp_foreachdb @command="" Exec ?..sp_helpfile;"" WITH RESULT SETS ((name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255))); ") select * from #temp 

über Abfrage w Ich gebe nur die erste Ergebnismenge der Ausführung von sp_foreachdb zurück. Die folgende Abfrage gibt jedoch das Ergebnis für alle Datenbanken in einer einzigen Ergebnismenge zurück.

create table #temp ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); exec sp_foreachdb @command="INSERT INTO #temp Exec ?..sp_helpfile;" select * from #temp 

Sie können versuchen, Aarons SP anzupassen, indem Sie ihn entfernen Der dynamische Teil soll eine Abfrage erstellen, die nur die Datenbanknamen von sys.databases basierend auf den angegebenen Argumenten liest. Das dynamische SQL wird ausgewählt, um die Abfrage am effizientesten zu gestalten. Unter Berücksichtigung Ihrer spezifischen Bedürfnisse könnten einige Opfer angebracht sein.

Ich würde jedoch argumentieren, dass die Leistung möglicherweise nicht viel unter dem Umschreiben leidet, das ich unten als sys.databases Die Systemansicht enthält normalerweise nicht sehr viele Zeilen. In jedem Fall können Sie jedoch am Ende OPTION (RECOMPILE) hinzufügen. So langsam es auch sein mag, Es ist jedoch wahrscheinlich ziemlich hässlich, was ich versprechen kann.

Die Methode zum Umschreiben ist wie folgt. Aarons Prozedur erstellt die Abfrage unter Verwendung eines sich wiederholenden Musters, in dem ein Parameterwert überprüft wird und , basierend auf dem Ergebnis, eine zusätzliche Die Abfrage wird der dynamischen Abfrage hinzugefügt, dh wie folgt:

SET @sql = N"SELECT name FROM sys.databases WHERE 1=1" + CASE WHEN some_condition1 THEN "AND some_filter1" ELSE "" END + CASE WHEN some_condition2 THEN "AND some_filter2" ELSE "" END + ... 

So würden Sie Folgendes umschreiben:

SELECT name FROM sys.databases WHERE 1=1 AND (some_filter1 OR opposite_of_some_condition1) AND (some_filter2 OR opposite_of_some_condition2) AND ... OPTION (RECOMPILE); 

Zum Beispiel steuert die @system_only, ob der Filter database_id IN (1,2,3,4) wie folgt enthalten sein soll:

SET @sql = N"SELECT name FROM sys.databases WHERE 1=1" + CASE WHEN @system_only = 1 THEN " AND database_id IN (1,2,3,4)" ELSE "" END 

Die umgeschriebene Abfrage wird folgendermaßen parametrisiert:

SELECT name FROM sys.databases WHERE 1=1 AND (database_id IN (1,2,3,4) OR @system_only <> 1) -- or: @system_only = 0 

Verwenden Sie die resultierende Abfrage in den Cursor direkt anstelle von

 SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db 

Wie zu sehen ist, müsste die letzte Berührung zum SELECT-Teil gehen, wo die einfache SELECT name der neuen Abfrage wird durch

 SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END 

ersetzt. Natürlich können Sie jetzt auch die -Tabelle, die im SP nicht mehr benötigt wird.

Ein letzter Hinweis betrifft das spezifische Skript, das Sie für jede Datenbank ausführen möchten. Anstelle von

@command1="use ?; Exec sp_helpfile;" 

könnten Sie auch

@command1="Exec ?..sp_helpfile;"