hvordan man undgår “ En INSERT EXEC-sætning kan ikke indlejres ” undtagelse i denne situation?

Kildekoden til Aaron “s sp_foreachdb indeholder følgende linje:

INSERT #x EXEC sp_executesql @sql;

Ifølge din fejlmeddelelse:

En INSERT EXEC-sætning kan ikke indlejres.

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

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

Kommentarer

• @marcellomiorelli Jeg tror, det ville kræve en stor ændring af Aaron ‘ s SP for at fjerne den dynamiske SQL. Dette ville sandsynligvis kræve brug af en ekstra funktion.

Oprettelse af forbindelse til din egen server via distribuerede forespørgselsmetoder ville gøre tricket, hvis sp_foreachdb ville returnere enkelt resultatsæt.

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 forespørgsel w syg returnerer kun det første resultatsæt af sp_foreachdb-udførelse. men under forespørgsel returnerer resultatet for alle databaser i et enkelt resultatsæt.

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 at tilpasse Arons SP ved at slippe af med af sin dynamiske del. Den dynamiske del skal bygge en forespørgsel, der kun læser databasenavnene fra sys.databases baseret på de leverede argumenter. Den dynamiske SQL er valgt for at gøre forespørgslen mest effektiv – Under hensyntagen til dine specifikke behov kan nogle ofre være i orden.

Jeg vil dog hævde, at forestillingen måske ikke lider meget af den omskrivning, jeg tilbyder nedenfor, som sys.databases systemvisning har normalt ikke så mange rækker, men under alle omstændigheder kan du tilføje OPTION (RECOMPILE) i slutningen. Hvor langsom det end er, selvom det sandsynligvis ender ret grimt, som jeg kan love.

Metoden til omskrivning er som følger. Arons procedure bygger op på forespørgslen ved hjælp af et gentaget mønster, hvor en parameterværdi kontrolleres og , baseret på resultatet, en ekstra forespørgsel tilføjes til den dynamiske forespørgsel, dvs. sådan:

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ådan omskriver du 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); 

F.eks. kontrollerer @system_only, om database_id IN (1,2,3,4) -filteret skal inkluderes, sådan:

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 omskrevne forespørgsel vil blive parametriseret på denne måde:

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

Brug den resulterende forespørgsel 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 kan ses, skal den sidste berøring gå til SELECT-delen, hvor den enkle SELECT name af den nye forespørgsel ville blive erstattet med

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

Du kan naturligvis nu også slippe af med #x -tabellen som ikke længere nødvendig i SP.

En sidste note vedrører det specifikke script, du planlægger at køre mod hver database. I stedet for

@command1="use ?; Exec sp_helpfile;" 

kan du bare have

@command1="Exec ?..sp_helpfile;" 

Jeg skrev en erstatning, der har flere forbedringer i forhold til den originale sp_foreachdb , herunder evnen til at rede. Den nyere foretrukne procedure kaldes sp_ineachdb ( del 1 / del 2 ), og er også tilgængelig som en del af Brent Ozar “s First Responder Kit .

Noget baggrund her :

• Dårlige vaner at sparke: Stole på udokumenteret adfærd