como evitar a “ Uma instrução INSERT EXEC não pode ser aninhada ” exceção nesta situação?

O código-fonte para Aaron “s sp_foreachdb contém a seguinte linha:

INSERT #x EXEC sp_executesql @sql;

De acordo com seu mensagem de erro:

Uma instrução INSERT EXEC não pode ser aninhada.

Então código como o abaixo não será válido, pois está aninhando o código INSERT xxx EXEC xxx.

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

Comentários

• @marcellomiorelli Acho que seria necessária uma grande mudança no SP de Aaron ‘ s para remover o SQL dinâmico. Isso provavelmente exigiria o uso de uma função adicional.

Conectar-se ao seu próprio servidor por meio de métodos de consulta distribuída resolveria o problema se sp_foreachdb retornaria conjunto de resultados único.

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 

acima da consulta w Vou retornar apenas o primeiro conjunto de resultados da execução de sp_foreachdb. mas a consulta abaixo retornará o resultado de todos os bancos de dados em um único conjunto de resultados.

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 

Você poderia tentar adaptar o SP de Aaron livrando-se de sua parte dinâmica. A parte dinâmica deve construir uma consulta lendo apenas os nomes do banco de dados de sys.databases com base nos argumentos fornecidos. O SQL dinâmico é escolhido para tornar a consulta mais eficiente – bem como de manutenção. Levando em consideração suas necessidades específicas, alguns sacrifícios podem ser necessários.

Eu argumentaria, porém, que o desempenho pode não sofrer muito com a reescrita que estou oferecendo abaixo, como o sys.databases a visualização do sistema geralmente não tem muitas linhas, mas em qualquer caso, você pode adicionar OPTION (RECOMPILE) no final. Por mais lento que seja, entretanto, é provável que termine bastante feio, isso eu posso prometer.

O método de reescrever é o seguinte. O procedimento de Aaron é construir a consulta usando um padrão de repetição onde um valor de parâmetro é verificado e , com base no resultado, um adicional a consulta é adicionada à consulta dinâmica, isto é:

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

Aqui está como você reescreveria isso:

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

Por exemplo, o @system_only controla se o database_id IN (1,2,3,4) filtro deve ser incluído, como este:

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 

A consulta reescrita seria parametrizada desta forma:

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

Use a consulta resultante em o cursor diretamente, em vez de

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

Como pode ser visto, o toque final precisaria ir para a parte SELECT, onde o da nova consulta seria substituído por

Obviamente, agora você também pode se livrar do #x tabela como não é mais necessária no SP.

Uma última observação diz respeito ao script específico que você está planejando executar em cada banco de dados. Em vez de

@command1="use ?; Exec sp_helpfile;" 

você poderia ter apenas

@command1="Exec ?..sp_helpfile;" 

Escrevi uma substituição que tem várias melhorias em relação ao original sp_foreachdb , incluindo a capacidade de aninhar. O procedimento preferido mais recente é chamado de sp_ineachdb ( parte 1 / parte 2 ) e também está disponível como parte do Brent Ozar “s Kit de primeiros socorros .

Algumas informações aqui :

• Maus hábitos para se livrar: confiar em comportamento não documentado