hoe de “ Een INSERT EXEC-instructie kan in deze situatie niet worden genest ” uitzondering?

De broncode voor Aaron “s sp_foreachdb bevat de volgende regel:

INSERT #x EXEC sp_executesql @sql;

Volgens uw foutmelding:

Een INSERT EXEC-instructie kan niet worden genest.

Dus code zoals hieronder zal niet geldig zijn aangezien deze INSERT xxx EXEC xxx code nesten.

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

Reacties

• @marcellomiorelli Ik denk dat er een grote verandering nodig is in Aaron ‘ s SP om de dynamische SQL te verwijderen. Dit zou waarschijnlijk het gebruik van een extra functie.

Verbinding maken met je eigen server via gedistribueerde zoekmethoden zou voldoende zijn als sp_foreachdb zou een enkele resultatenset retourneren.

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 

bovenstaande query w ill retourneert alleen de eerste resultatenset van sp_foreachdb uitvoering. maar onderstaande query zal het resultaat voor alle databases in een enkele resultatenset retourneren.

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 

Je zou kunnen proberen de SP van Aaron aan te passen door weg te werken van zijn dynamische deel. Het dynamische deel wordt verondersteld een query te bouwen die alleen de databasenamen van sys.databases leest op basis van de opgegeven argumenten. De dynamische SQL is gekozen om de query zo efficiënt mogelijk te maken – Evenals onderhoudbaar. Rekening houdend met uw specifieke behoeften, kunnen enkele offers op zijn plaats zijn.

Ik zou echter willen stellen dat de uitvoering misschien niet veel lijdt onder de herschrijving die ik hieronder aanbied, aangezien de sys.databases systeemweergave heeft meestal niet veel rijen, maar je kunt in elk geval OPTION (RECOMPILE) aan het einde toevoegen. Hoe langzaam het ook is, hoewel, het zal waarschijnlijk nogal lelijk eindigen, dat kan ik beloven.

De methode van herschrijven is als volgt. De procedure van Aaron is het bouwen van de query met behulp van een herhalend patroon waarbij een parameterwaarde wordt gecontroleerd en , op basis van het resultaat, een extra query wordt toegevoegd aan de dynamische query, dwz als volgt:

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

Hier is hoe je dat zou herschrijven:

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

Het @system_only bepaalt bijvoorbeeld of het database_id IN (1,2,3,4) filter moet worden opgenomen, zoals dit:

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 

De herschreven zoekopdracht zou op deze manier geparametriseerd worden:

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

Gebruik de resulterende zoekopdracht in de cursor rechtstreeks, in plaats van

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

Zoals te zien is, zou de laatste hand naar het SELECT-gedeelte moeten gaan, waar het simpele SELECT name van de nieuwe zoekopdracht zou worden vervangen door

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

Het is duidelijk dat je nu ook de tabel omdat deze niet langer nodig is in de SP.

Een laatste opmerking betreft het specifieke script dat u voor elke database wilt uitvoeren. In plaats van

@command1="use ?; Exec sp_helpfile;" 

had je net

@command1="Exec ?..sp_helpfile;" 

Ik heb een vervanging geschreven die verschillende verbeteringen heeft ten opzichte van de originele sp_foreachdb , inclusief de mogelijkheid om te nestelen. De nieuwere, geprefereerde procedure heet sp_ineachdb ( deel 1 / deel 2 ), en is ook beschikbaar als onderdeel van Brent Ozar “s First Responder Kit .

Wat achtergrondinformatie hier :

• Slechte gewoonten om te trappen: vertrouwen op ongedocumenteerd gedrag