jak se vyhnout “ příkazu INSERT EXEC nelze vnořit ” výjimku v této situaci?

zdrojový kód pro Aarona „s sp_foreachdb obsahuje následující řádek:

INSERT #x EXEC sp_executesql @sql;

Podle vašeho chybová zpráva:

Příkaz INSERT EXEC nelze vnořit.

Takže níže uvedený kód nebude platný, protože vnořuje INSERT xxx EXEC xxx kód.

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

Komentáře

• @marcellomiorelli Myslím, že k odstranění dynamického SQL by bylo zapotřebí velké změny v Aaron ‚ s SP. Pravděpodobně by to vyžadovalo použití další funkce.

Připojení k vlastnímu serveru pomocí metod distribuovaného dotazu by stačilo, kdyby sp_foreachdb vrátí jednu sadu výsledků.

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 

nad dotazem w vrátím pouze první sadu výsledků sp_foreachdb provedení. ale níže uvedený dotaz vrátí výsledek pro všechny databáze v jedné sadě výsledků.

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 

Můžete zkusit přizpůsobit Aaronovu SP odstraněním jeho dynamické části. Dynamická část má na základě zadaných argumentů vytvořit dotaz, který přečte pouze názvy databází z sys.databases. Aby byl dotaz co nejefektivnější, je vybrán dynamický SQL – a také udržovatelné. S přihlédnutím k vašim specifickým potřebám mohou být některé oběti v pořádku.

Tvrdím však, že výkon nemusí příliš trpět přepsáním, které níže nabízím, protože sys.databases zobrazení systému obvykle nemá příliš mnoho řádků, ale v každém případě byste mohli na konec přidat OPTION (RECOMPILE). Může to však být pomalé, i když je pravděpodobné, že to skončí docela ošklivé, to mohu slíbit.

Metoda přepisování je následující. Aaronův postup vytváří dotaz pomocí opakujícího se vzoru, kde je kontrolována hodnota parametru a , na základě výsledku další dotaz se přidá k dynamickému dotazu, tj. takto:

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

Takto byste jej přepsali:

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

Například @system_only řídí, zda má být zahrnut filtr database_id IN (1,2,3,4), například:

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 

Přepsaný dotaz by byl parametrizován takto:

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

Výsledný dotaz použijte v přímo kurzor, místo

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

Jak je vidět, poslední dotek bude muset přejít do části SELECT, kde jednoduchý SELECT name nového dotazu bude nahrazen

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

Je zřejmé, že byste se nyní mohli také zbavit #x tabulka již není v SP potřeba.

Jedna poslední poznámka se týká konkrétního skriptu, který plánujete spustit proti každé databázi. Místo

@command1="use ?; Exec sp_helpfile;" 

můžete mít pouze

@command1="Exec ?..sp_helpfile;" 

Napsal jsem náhradu, která má několik vylepšení oproti původní sp_foreachdb , včetně možnosti vnoření. Novější upřednostňovaný postup se nazývá sp_ineachdb ( část 1 / part 2 ), a je také k dispozici jako součást First Responder Kit Brenta Ozara .

Některé pozadí zde :

• Špatné návyky ke kopnutí: spoléhání se na nezdokumentované chování