comment éviter lexception “ Une instruction INSERT EXEC ne peut pas être imbriquée ” dans cette situation?

Le code source pour Aaron « s sp_foreachdb contient la ligne suivante:

INSERT #x EXEC sp_executesql @sql;

Selon votre message derreur:

Une instruction INSERT EXEC ne peut pas être imbriquée.

Donc un code comme celui ci-dessous ne sera pas valide car il imbrique INSERT xxx EXEC xxx code.

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

Commentaires

• @marcellomiorelli Je pense quil faudrait un gros changement au SP dAaron ‘ pour supprimer le SQL dynamique. Cela nécessiterait probablement lutilisation de une fonction supplémentaire.

La connexion à votre propre serveur via des méthodes de requête distribuées ferait laffaire si sp_foreachdb renvoie un seul ensemble de résultats.

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 

au-dessus de la requête w ill renvoie uniquement le premier ensemble de résultats de lexécution de sp_foreachdb. mais la requête ci-dessous renverra le résultat pour toutes les bases de données dans un seul jeu de résultats.

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 

Vous pouvez essayer dadapter le SP dAaron en vous débarrassant de sa partie dynamique. La partie dynamique est censée créer une requête lisant uniquement les noms de base de données de sys.databases en fonction des arguments fournis. Le SQL dynamique est choisi pour rendre la requête la plus efficace – ainsi que maintenable. Compte tenu de vos besoins spécifiques, certains sacrifices pourraient être nécessaires.

Je dirais, cependant, que la performance pourrait ne pas souffrir beaucoup de la réécriture que je propose ci-dessous, car le sys.databases ne comporte généralement pas beaucoup de lignes, mais dans tous les cas, vous pouvez ajouter OPTION (RECOMPILE) à la fin. Quelle que soit la lenteur, elle peut être cependant, il est susceptible de finir plutôt moche, ce que je peux promettre.

La méthode de réécriture est la suivante. La procédure dAaron consiste à construire la requête en utilisant un modèle répétitif où une valeur de paramètre est vérifiée et , en fonction du résultat, un la requête est ajoutée à la requête dynamique, cest-à-dire comme ceci:

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

Voici comment vous réécririez cela:

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

Par exemple, le @system_only contrôle si le filtre database_id IN (1,2,3,4) doit être inclus, comme ceci:

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 requête réécrite serait paramétrée de cette manière:

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

Utilisez la requête résultante dans le curseur directement, au lieu de

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

Comme on peut le voir, la touche finale devrait aller à la partie SELECT, où le simple SELECT name de la nouvelle requête serait remplacé par

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

Évidemment, vous pouvez maintenant également vous débarrasser du #x comme nétant plus nécessaire dans le SP.

Une dernière remarque concerne le script spécifique que vous prévoyez dexécuter sur chaque base de données. Au lieu de

@command1="use ?; Exec sp_helpfile;" 

vous pourriez avoir juste

@command1="Exec ?..sp_helpfile;" 

Jai écrit un remplacement qui a plusieurs améliorations par rapport à loriginal sp_foreachdb , y compris la possibilité dimbriquer. La nouvelle procédure préférée est appelée sp_ineachdb ( part 1 / partie 2 ), et est également disponible dans le cadre du Kit de premier répondant de Brent Ozar « .

Quelques informations générales ici :

• Mauvaises habitudes de frappe: se fier à un comportement non documenté