hvordan unngår vi “ Et INSERT EXEC-utsagn kan ikke nestes ” unntak for denne situasjonen?

Kildekoden for Aaron «s sp_foreachdb inneholder følgende linje:

INSERT #x EXEC sp_executesql @sql;

I følge din feilmelding:

En INSERT EXEC-setning kan ikke nestes.

Så koden som den nedenfor er ikke gyldig, da den hekker INSERT xxx EXEC xxx kode.

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

Kommentarer

• @marcellomiorelli Jeg tror det vil kreve en stor endring i Aaron ‘ s SP for å fjerne dynamisk SQL. Dette vil trolig kreve bruk av en tilleggsfunksjon.

Å koble til din egen server gjennom distribuerte spørremetoder ville gjort susen hvis sp_foreachdb ville returnere enkelt resultatsett.

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 

over spørring w syk returnerer bare det første resultatsettet for sp_foreachdb-kjøring. men under spørringen vil resultatet for alle databaser returneres i et enkelt resultatsett.

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 prøve å tilpasse Aarons SP ved å bli kvitt av den dynamiske delen. Den dynamiske delen skal bygge et spørsmål som bare leser databasenavnene fra sys.databases basert på de angitte argumentene. Den dynamiske SQL er valgt for å gjøre spørringen mest effektiv – Hvis du tar hensyn til dine spesifikke behov, kan det være noen ofre i orden.

Jeg vil imidlertid hevde at forestillingen kanskje ikke lider mye av omskrivingen jeg tilbyr nedenfor, som sys.databases systemvisning har vanligvis ikke veldig mange rader, men i alle fall kan du legge til OPTION (RECOMPILE) på slutten. Hvor treg det kan være, skjønt, det vil sannsynligvis ende opp ganske stygt, det kan jeg love.

Metoden for omskriving er som følger. Arons prosedyre bygger opp spørringen ved å bruke et gjentatt mønster der en parameterverdi blir sjekket og , basert på resultatet, et tillegg spørring legges til i den dynamiske spørringen, dvs. slik:

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

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

For eksempel kontrollerer @system_only om database_id IN (1,2,3,4) -filteret skal være inkludert, slik:

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 

Det omskrevne spørsmålet vil bli parametrisert på denne måten:

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

Bruk den resulterende spørringen i markøren direkte, i stedet for

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

Som det fremgår, må den siste berøringen gå til SELECT-delen, der den enkle SELECT name av den nye forespørselen ville bli erstattet med

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

Selvfølgelig kan du nå også kvitte deg med #x -tabellen som ikke lenger er nødvendig i SP.

En siste merknad gjelder det spesifikke skriptet du planlegger å kjøre mot hver database. I stedet for

@command1="use ?; Exec sp_helpfile;" 

kan du bare ha

@command1="Exec ?..sp_helpfile;" 

Jeg skrev en erstatning som har flere forbedringer i forhold til den opprinnelige sp_foreachdb , inkludert muligheten til å hekke. Den nyere, foretrukne prosedyren kalles sp_ineachdb ( del 1 / del 2 ), og er også tilgjengelig som en del av Brent Ozar «s First Responder Kit .

Noe bakgrunn her :

• Dårlige vaner å sparke: Stole på udokumentert oppførsel