Erreurs : "L'instruction INSERT-EXEC ne peut pas être imbriquée" et "Impossible d'utiliser l'instruction ROLLBACK dans une instruction INSERT-EXEC". Comment résoudre ce problème ?

Question

J'ai trois procédures stockées Sp1 , Sp2 y Sp3 .

Le premier ( Sp1 ) exécutera le second ( Sp2 ) et sauvegarder les données retournées dans @tempTB1 et le deuxième exécutera le troisième ( Sp3 ) et sauvegarder les données dans @tempTB2 .

Si j'exécute le Sp2 cela fonctionnera et me renverra toutes mes données depuis le Sp3 mais le problème se situe au niveau de la Sp1 Lorsque je l'exécute, il affiche cette erreur :

L'instruction INSERT EXEC ne peut pas être imbriquée

J'ai essayé de changer la place de execute Sp2 et il m'affiche une autre erreur :

Impossible d'utiliser l'instruction ROLLBACK dans une instruction INSERT-EXEC.

Answer 1

Il s'agit d'un problème courant lorsque l'on tente de faire remonter des données à partir d'une chaîne de procédures stockées. Une restriction de SQL Server est que vous ne pouvez avoir qu'un seul INSERT-EXEC actif à la fois. Je vous recommande de consulter Comment partager des données entre procédures stockées qui est un article très complet sur les modèles permettant de contourner ce type de problème.

Par exemple, une solution pourrait consister à transformer Sp3 en une fonction à valeur de tableau.

Answer 2

C'est la seule façon "simple" de le faire dans SQL Server sans une fonction créée géante et alambiquée ou un appel de chaîne sql exécuté, qui sont tous deux des solutions terribles :

1. créer une table temporaire
2. d'y insérer les données de votre procédure stockée

EXEMPLE :

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

Note : Vous DEVEZ utiliser 'set fmtonly off', ET vous NE POUVEZ PAS ajouter de sql dynamique dans l'appel openrowset, que ce soit pour la chaîne contenant les paramètres de votre procédure stockée ou pour le nom de la table. C'est pourquoi vous devez utiliser une table temporaire plutôt que des variables de table, ce qui aurait été mieux, car cela surpasse la table temporaire dans la plupart des cas.

Answer 3

J'ai trouvé un moyen de contourner le problème en convertissant l'une des prods en une fonction à valeur de tableau. Je me rends compte que ce n'est pas toujours possible et que cela introduit ses propres limites. Cependant, j'ai toujours pu trouver au moins une des procédures comme étant un bon candidat pour cela. J'aime cette solution, parce qu'elle n'introduit pas de "hack" dans la solution.

Answer 4

Je doute que vous puissiez réaliser l'exécution de l'insertion imbriquée.
Références : http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e66c9e71-4424-4cf3-920c-6725ffc40162/ Mais je pense que cela pourrait vous aider http://www.sqlservercentral.com/Forums/Topic13595-8-1.aspx#bm68301

Answer 5

J'ai eu le même problème et la même inquiétude concernant le code dupliqué dans deux ou plusieurs sprocs. J'ai fini par ajouter un attribut supplémentaire pour "mode". Cela permettait au code commun d'exister à l'intérieur d'un sproc et le mode dirigeait le flux et l'ensemble des résultats du sproc.