Wenn ich das folgende Skript ausführe, läuft es einwandfrei:

declare @temp table ( 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 @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" 

aber wann Ich verwende meine eigene sp_foreachdb-Prozedur. Der Quellcode befindet sich unter folgendem Link:

Eine zuverlässigere und flexiblere sp_MSforeachdb

declare @temp table ( 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 @temp exec sp_foreachdb @command="use ?; Exec sp_helpfile;" 

Ich erhalte eine Ausnahme (bitte beachten Sie, dass ich bei dieser Prozedur eine Ausnahmebehandlung hinzugefügt habe)

--EXCEPTION WAS CAUGHT-- THE ERROR NUMBER:8164 SEVERITY: 16 STATE: 1 PROCEDURE: sp_foreachdb LINE NUMBER: 165 ERROR MESSAGE: An INSERT EXEC statement cannot be nested. ------------------------------------ the sql ------------------------------------ SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N"ONLINE" AND is_read_only = 0 Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239 A cursor with the name "c" does not exist. Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240 A cursor with the name "c" does not exist. (0 row(s) affected) 

Kommentare

  • Ich ‚ bin nicht sicher, warum Sie ‚ diese verschlungene verwenden Methode. Warum nicht einfach sys.master_files abfragen?
  • @Nic gut hervorgehoben, ich habe jedoch nur die sp_helpfile als Beispiel verwendet. Im wirklichen Leben ist es eine unserer eigenen gespeicherten Prozeduren, die ich

Antwort

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.

Antwort

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 

Antwort

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

Antwort

Ich habe einen Ersatz geschrieben, der gegenüber dem ursprünglichen sp_foreachdb , einschließlich der Fähigkeit zu nisten. Die neuere, bevorzugte Prozedur heißt sp_ineachdb ( Teil 1 / Teil 2 ) und ist auch als Teil von Brent Ozars First Responder Kit verfügbar.

Einige Hintergrundinformationen hier :


Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.