come evitare “ Unistruzione INSERT EXEC non può essere nidificata ” eccezione in questa situazione?

Il codice sorgente per Aaron “s sp_foreachdb contiene la seguente riga:

INSERT #x EXEC sp_executesql @sql;

Secondo il tuo messaggio di errore:

Impossibile nidificare unistruzione INSERT EXEC.

Quindi il codice come quello riportato di seguito non sarà valido perché nidifica INSERT xxx EXEC xxx codice.

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

Commenti

• @marcellomiorelli Penso che sarebbe necessario un grande cambiamento allSP di Aaron ‘ per rimuovere lSQL dinamico. Ciò richiederebbe probabilmente luso di una funzione aggiuntiva.

Connettersi al proprio server tramite metodi di query distribuiti farebbe il trucco se sp_foreachdb restituirebbe un singolo set di risultati.

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 

sopra la query w restituisce solo il primo gruppo di risultati dellesecuzione di sp_foreachdb. ma la query sottostante restituirà il risultato per tutti i database in un unico gruppo di risultati.

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 

Potresti provare ad adattare lSP di Aaron sbarazzandoti della sua parte dinamica. La parte dinamica dovrebbe costruire una query che legge solo i nomi del database da sys.databases in base agli argomenti forniti. LSQL dinamico viene scelto per rendere la query più efficiente – oltre che mantenibile. Tenendo conto delle tue esigenze specifiche, potrebbero essere necessari alcuni sacrifici.

Direi, tuttavia, che la performance potrebbe non risentire molto della riscrittura che offro di seguito, poiché sys.databases la vista di sistema di solito non ha molte righe, ma in ogni caso potresti aggiungere OPTION (RECOMPILE) alla fine. Per quanto lento possa essere, tuttavia, è probabile che finisca per diventare piuttosto brutto, lo posso promettere.

Il metodo di riscrittura è il seguente. La procedura di Aaron consiste nel costruire la query utilizzando uno schema ripetuto in cui viene controllato il valore di un parametro e , in base al risultato, un ulteriore la query viene aggiunta alla query dinamica, ad esempio in questo modo:

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

Ecco come lo riscriveresti:

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

Ad esempio, il @system_only controlla se il filtro database_id IN (1,2,3,4) deve essere incluso, in questo modo:

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 

La query riscritta verrebbe parametrizzata in questo modo:

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

Utilizza la query risultante in direttamente il cursore, invece di

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

Come si può vedere, il tocco finale dovrebbe andare alla parte SELECT, dove il semplice SELECT name della nuova query verrebbe sostituita con

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

Ovviamente, ora potresti anche sbarazzarti del #x tabella in quanto non più necessaria nellSP.

Unultima nota riguarda lo script specifico che si prevede di eseguire su ciascun database. Invece di

@command1="use ?; Exec sp_helpfile;" 

potresti avere solo

@command1="Exec ?..sp_helpfile;" 

Ho scritto una sostituzione che presenta diversi miglioramenti rispetto alloriginale sp_foreachdb , inclusa la possibilità di annidare. La procedura più recente e preferita si chiama sp_ineachdb ( parte 1 / parte 2 ) ed è anche disponibile come parte del First Responder Kit di Brent Ozar .

Qualche informazione qui :

• Cattive abitudini da prendere a calci: fare affidamento su comportamenti non documentati