cum se poate evita “ O instrucțiune INSERT EXEC nu poate fi imbricată ” excepție în această situație?

Codul sursă pentru Aaron „s sp_foreachdb conține următoarea linie:

INSERT #x EXEC sp_executesql @sql;

Conform mesaj de eroare:

O instrucțiune INSERT EXEC nu poate fi imbricată.

Deci codul de genul de mai jos nu va fi valid, deoarece este cuibărit INSERT xxx EXEC xxx cod.

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

Comentarii

• @marcellomiorelli Cred că ar necesita o mare schimbare a SP-ului lui Aaron ‘ s pentru a elimina SQL-ul dinamic. Acest lucru ar necesita probabil utilizarea o funcție suplimentară.

Conectarea la propriul server prin metode de interogare distribuită ar face truc dacă sp_foreachdb ar returna un singur set de rezultate.

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 

deasupra interogării w Nu returnez decât primul set de rezultate al execuției sp_foreachdb. dar mai jos interogarea va returna rezultatul pentru toate bazele de date dintr-un singur set de rezultate.

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 

Ai putea încerca să adaptezi SP-ul lui Aaron scăpând din partea sa dinamică. Partea dinamică ar trebui să construiască o interogare citind doar numele bazei de date din sys.databases pe baza argumentelor furnizate. SQL dinamic este ales pentru a face interogarea cea mai eficientă – Ținând cont de nevoile dvs. specifice, unele sacrificii ar putea fi în ordine.

Totuși, aș susține că performanța ar putea să nu sufere prea mult din rescrierea pe care o ofer mai jos, ca sys.databases vizualizarea sistemului de obicei nu are foarte multe rânduri, dar în orice caz puteți adăuga OPTION (RECOMPILE) la sfârșit. Oricât de lent ar fi, totuși, este posibil să sfârșească destul de urât, ceea ce pot promite.

Metoda de rescriere este după cum urmează. Procedura lui Aaron construiește interogarea utilizând un model de repetare în care se verifică o valoare a parametrului și , pe baza rezultatului, un supliment interogarea este adăugată la interogarea dinamică, adică astfel:

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

Iată cum ați rescrie că:

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

De exemplu, @system_only controlează dacă filtrul database_id IN (1,2,3,4) trebuie inclus, astfel:

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 

Interogarea rescrisă va fi parametrizată în acest fel:

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

Utilizați interogarea rezultată în cursorul direct, în loc de

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

După cum se poate vedea, atingerea finală ar trebui să meargă la partea SELECT, unde simpla SELECT name al noii interogări ar fi înlocuit cu

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

Evident, acum ai putea scăpa și de #x tabelul nu mai este necesar în SP.

O ultimă notă se referă la scriptul specific pe care intenționați să îl rulați pentru fiecare bază de date. În loc de

@command1="use ?; Exec sp_helpfile;" 

ai putea avea doar

@command1="Exec ?..sp_helpfile;" 

Am scris un înlocuitor care are mai multe îmbunătățiri față de sp_foreachdb , inclusiv capacitatea de a cuibări. Procedura mai nouă și preferată se numește sp_ineachdb ( partea 1 / partea 2 ) și este, de asemenea, disponibil ca parte a lui Brent Ozar „s First Responder Kit .

Câteva informații aici :

• Obiceiuri rele de lovit: bazându-se pe un comportament nedocumentat