jak uniknąć “ instrukcji INSERT EXEC nie można zagnieżdżać ” w tej sytuacji?

Kod źródłowy dla Aarona „s sp_foreachdb zawiera następujący wiersz:

INSERT #x EXEC sp_executesql @sql;

Zgodnie z Twoim komunikat o błędzie:

Instrukcja INSERT EXEC nie może być zagnieżdżona.

Więc poniższy kod nie będzie prawidłowy, ponieważ jest zagnieżdżony kod INSERT xxx EXEC xxx.

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

Komentarze

• @marcellomiorelli Myślę, że wymagałoby to dużej zmiany na SP Aarona ' s, aby usunąć dynamiczny SQL. Prawdopodobnie wymagałoby to użycia dodatkowa funkcja.

Połączenie z własnym serwerem za pomocą rozproszonych metod zapytań załatwi sprawę, jeśli sp_foreachdb zwróci pojedynczy zestaw wyników.

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 

powyżej zapytania w źle zwróci tylko pierwszy zestaw wyników wykonania sp_foreachdb. ale poniżej zapytanie zwróci wynik dla wszystkich baz danych w jednym zestawie wyników.

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 

Możesz spróbować dostosować SP Aarona, pozbywając się części dynamicznej. Część dynamiczna ma na celu zbudowanie zapytania odczytującego tylko nazwy baz danych z sys.databases na podstawie dostarczonych argumentów. Dynamiczny SQL jest wybierany, aby uczynić zapytanie najbardziej wydajnym – jak również do utrzymania. Biorąc pod uwagę twoje specyficzne potrzeby, niektóre wyrzeczenia mogą być w porządku.

Twierdzę jednak, że wydajność może nie ucierpieć zbytnio z powodu przepisania, które oferuję poniżej, ponieważ sys.databases widok systemu zwykle nie ma zbyt wielu wierszy, ale w każdym razie możesz dodać na końcu OPTION (RECOMPILE). Jakkolwiek wolno to może być, chociaż prawdopodobnie skończy się raczej brzydko, co mogę obiecać.

Metoda przepisywania jest następująca. Procedura Aarona polega na budowaniu zapytania przy użyciu powtarzającego się wzorca, w którym sprawdzana jest wartość parametru i , na podstawie wyniku, dodatkowy zapytanie jest dodawane do dynamicznego zapytania, np. w ten sposób:

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

Oto jak można to przepisać:

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

Na przykład @system_only określa, czy filtr database_id IN (1,2,3,4) powinien zostać uwzględniony, na przykład:

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 

Przepisane zapytanie byłoby sparametryzowane w następujący sposób:

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

Użyj wynikowego zapytania w kursor bezpośrednio, zamiast

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

Jak widać, ostatnie dotknięcie musiałoby przejść do części SELECT, gdzie proste SELECT name nowego zapytania zostanie zastąpione przez

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

Oczywiście możesz teraz również pozbyć się #x tabela, która nie jest już potrzebna w SP.

Ostatnia uwaga dotyczy konkretnego skryptu, który planujesz uruchomić na każdej bazie danych. Zamiast

@command1="use ?; Exec sp_helpfile;" 

mógłbyś mieć po prostu

@command1="Exec ?..sp_helpfile;" 

Napisałem zamiennik, który ma kilka ulepszeń w stosunku do oryginalnego sp_foreachdb , w tym możliwość zagnieżdżania. Nowsza, preferowana procedura nosi nazwę sp_ineachdb ( część 1 / część 2 ) i jest również dostępny jako część zestawu ratunkowego Brenta Ozara .

Trochę informacji ogólnych tutaj :

• Złe nawyki: poleganie na nieudokumentowanym zachowaniu