この状況で“ INSERTEXECステートメントをネストできない”例外を回避する方法は？

ソースコードアーロンのsp_foreachdbには、次の行が含まれています。

INSERT #x EXEC sp_executesql @sql;

エラーメッセージ：

INSERTEXECステートメントをネストできません。

以下のようなコードは、INSERT xxx EXEC xxxコードをネストしているため、無効になります。

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

コメント

• @marcellomiorelli動的SQLを削除するには、Aaron 'のSPを大幅に変更する必要があると思います。これには、おそらく追加の関数。

分散クエリメソッドを介して独自のサーバーに接続すると、sp_foreachdbの場合にうまくいきます。単一の結果セットを返します。

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 

上記のクエリw illは、sp_foreachdb実行の最初の結果セットのみを返します。ただし、以下のクエリは、単一の結果セット内のすべてのデータベースの結果を返します。

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 

削除することで、アーロンのSPを適応させることができます動的部分は、指定された引数に基づいてsys.databasesからデータベース名のみを読み取るクエリを作成することになっています。動的SQLは、クエリを最も効率的にするために選択されます–特定のニーズを考慮に入れると、いくつかの犠牲が必要になる場合があります。

ただし、以下のように、以下で提供する書き直しによってパフォーマンスがそれほど低下しない可能性があると主張します。sys.databasesシステムビューには通常、行数がそれほど多くありませんが、いずれにしても最後にOPTION (RECOMPILE)を追加できます。ただし、遅くなる可能性があります。ただし、約束できるのはかなり醜いものになる可能性があります。

書き直しの方法は次のとおりです。アーロンの手順では、パラメータ値をチェックする繰り返しパターンを使用してクエリを作成し、 、結果に基づいて、追加クエリが動的クエリに追加されます。つまり、次のようになります。

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

これを書き直す方法は次のとおりです。

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

たとえば、@system_onlyは、次のようにdatabase_id IN (1,2,3,4)フィルターを含めるかどうかを制御します。

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 

書き直されたクエリは次のようにパラメータ化されます：

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

結果のクエリをカーソルの代わりに直接カーソル

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

ご覧のとおり、最後の仕上げはSELECT部分に移動する必要があります。ここで単純なは次のように置き換えられます

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

明らかに、テーブルはSPで不要になりました。

最後の注意点は、各データベースに対して実行する予定の特定のスクリプトに関するものです。

@command1="use ?; Exec sp_helpfile;" 

の代わりに

@command1="Exec ?..sp_helpfile;" 

元の sp_foreachdb <よりもいくつか改善された置換を作成しました/ a>、ネストする機能を含みます。新しい推奨手順は、sp_ineachdb（パート1 / パート2 ）、ブレントオザールのファーストレスポンダーキットの一部としても利用できます。

ここにいくつかの背景があります：

• キックする悪い習慣：文書化されていない行動に依存する