hur undviker vi “ Ett INSERT EXEC-uttalande kan inte kapslas ” undantag i denna situation?

Källkoden för Aaron ”s sp_foreachdb innehåller följande rad:

INSERT #x EXEC sp_executesql @sql;

Enligt din felmeddelande:

Ett INSERT EXEC-uttalande kan inte kapslas.

Så kod som den nedan kommer inte att vara giltig eftersom den häckar INSERT xxx EXEC xxx kod.

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

Kommentarer

• @marcellomiorelli Jag tror att det skulle kräva en stor förändring av Aaron ’ s SP för att ta bort den dynamiska SQL. Detta skulle förmodligen kräva användning av en ytterligare funktion.

Att ansluta till din egen server via distribuerade frågemetoder skulle göra tricket om sp_foreachdb skulle returnera enstaka resultatuppsättning.

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 

ovanför frågan w ill returnerar bara den första resultatsatsen för sp_foreachdb-körning. men under frågan returnerar resultatet för alla databaser i en enda resultatuppsättning.

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 

Du kan försöka anpassa Arons SP genom att bli av av sin dynamiska del. Den dynamiska delen ska bygga en fråga som bara läser databasnamnen från sys.databases baserat på de angivna argumenten. Den dynamiska SQL väljs för att göra frågan mest effektiv – Med hänsyn till dina specifika behov kan vissa uppoffringar vara i ordning.

Jag skulle dock hävda att föreställningen kanske inte lider mycket av den omskrivning jag erbjuder nedan, som sys.databases systemvyn har vanligtvis inte så många rader, men i vilket fall som helst kan du lägga till OPTION (RECOMPILE) i slutet. Hur långsam det än kan vara, men det kommer sannolikt att bli ganska ful, som jag kan lova.

Metoden för omskrivning är som följer. Arons procedur bygger upp frågan med ett upprepande mönster där ett parametervärde kontrolleras och , baserat på resultatet, en ytterligare fråga läggs till i den dynamiska frågan, dvs så här:

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

Så här skriver du om det:

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

Till exempel styr @system_only om database_id IN (1,2,3,4) -filtret ska inkluderas, så här:

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 

Den omskrivna frågan skulle parametriseras på detta sätt:

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

Använd den resulterande frågan i markören direkt, istället för

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

Som kan ses måste den sista handen gå till SELECT-delen, där den enkla SELECT name för den nya frågan skulle ersättas med

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

Uppenbarligen kan du nu också bli av med #x -tabellen som inte längre behövs i SP.

En sista anmärkning gäller det specifika skriptet du planerar att köra mot varje databas. Istället för

@command1="use ?; Exec sp_helpfile;" 

kan du bara ha

@command1="Exec ?..sp_helpfile;" 

Jag skrev en ersättare som har flera förbättringar jämfört med originalet sp_foreachdb , inklusive förmågan att bo. Det nyare, föredragna förfarandet kallas sp_ineachdb ( del 1 / del 2 ), och finns också som en del av Brent Ozar ”s First Responder Kit .

Lite bakgrund här :

• Dåliga vanor att sparka: Förlita sig på odokumenterat beteende