sql server - Errores: "INSERTAR instrucción EXEC no se puede anidar" y "No se puede usar la instrucción ROLLBACK dentro de una instrucción INSERT-EXEC". ¿Cómo resolver esto?

sql-server sql-server-2008 (10)

Tengo tres procedimientos almacenados Sp1 , Sp2 y Sp3 .

El primero ( Sp1 ) ejecutará el segundo ( Sp2 ) y guardará los datos devueltos en @tempTB1 y el segundo ejecutará el tercero ( Sp3 ) y guardará los datos en @tempTB2 .

Si ejecuto el Sp2 , funcionará y me devolverá todos mis datos del Sp3 , pero el problema está en el Sp1 , cuando lo ejecute mostrará este error:

INSERTAR instrucción EXEC no se puede anidar

Traté de cambiar el lugar de execute Sp2 y me muestra otro error:

No se puede usar la instrucción ROLLBACK dentro de una instrucción INSERT-EXEC.

¿Qué hay de simplemente almacenar la salida a la tabla estática? Me gusta

-- SubProcedure: subProcedureName --------------------------------- -- Save the value DELETE lastValue_subProcedureName INSERT INTO lastValue_subProcedureName (Value) SELECT @Value -- Return the value SELECT @Value -- Procedure -------------------------------------------- -- get last value of subProcedureName SELECT Value FROM lastValue_subProcedureName

no es ideal, pero es tan simple y no es necesario volver a escribir todo.

ACTUALIZACIÓN : la solución anterior no funciona bien con consultas paralelas (acceso asincrónico y multiusuario), por lo tanto, ahora utilizo tablas temporales

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. -- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. -- The table cannot be referenced by the process that called the stored procedure that created the table. IF OBJECT_ID(''tempdb..#lastValue_spGetData'') IS NULL CREATE TABLE #lastValue_spGetData (Value INT) -- trigger stored procedure with special silent parameter EXEC dbo.spGetData 1 --silent mode parameter

contenido de procedimiento almacenado spGetData anidado

-- Save the output if temporary table exists. IF OBJECT_ID(''tempdb..#lastValue_spGetData'') IS NOT NULL BEGIN DELETE #lastValue_spGetData INSERT INTO #lastValue_spGetData(Value) SELECT Col1 FROM dbo.Table1 END -- stored procedure return IF @silentMode = 0 SELECT Col1 FROM dbo.Table1

Declara una variable de cursor de salida a la sp interior:

@c CURSOR VARYING OUTPUT

Luego declare un cursor c al seleccionar que desea devolver. Luego abre el cursor. Luego establece la referencia:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ... OPEN c SET @c = c

NO cierre ni reasigne.

Ahora llame al sp interior desde el exterior que proporciona un parámetro de cursor como:

exec sp_abc a,b,c,, @cOUT OUTPUT

Una vez que se ejecuta el sp interno, su @cOUT está listo para buscar. Bucle y luego cerrar y desasignar.

En SQL Server 2008 R2, tuve una discrepancia en las columnas de la tabla que causó el error de revertir. Se eliminó cuando arreglé mi variable de tabla sqlcmd poblada por la instrucción insert-exec para que coincida con la devuelta por el proceso almacenado. Faltaba org_code. En un archivo cmd de Windows, carga el resultado del procedimiento almacenado y lo selecciona.

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^ tin(char9), old_strt_dt char(10), strt_dt char(10)); ^ insert @resets exec rsp_reset; ^ select * from @resets; sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"

Encontré una solución alternativa para convertir una de las herramientas en una función de valor de tabla. Me doy cuenta de que no siempre es posible, y presenta sus propias limitaciones. Sin embargo, siempre he podido encontrar que al menos uno de los procedimientos es un buen candidato para esto. Me gusta esta solución, porque no introduce ningún "ataque" a la solución.

Esta es la única forma "simple" de hacer esto en SQL Server sin alguna función gigantesca creada o ejecución ejecutada de cadena sql, ambas soluciones terribles:

crea una tabla temporal
abrir sus datos de procedimiento almacenados en ella

EJEMPLO:

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

Nota : DEBE usar ''set fmtonly off'', Y NO PUEDE agregar sql dinámico a esto dentro de la llamada openrowset, ya sea para la cadena que contiene los parámetros del procedimiento almacenado o para el nombre de la tabla. Es por eso que debe usar una tabla temporal en lugar de variables de tabla, lo que hubiera sido mejor, ya que supera la tabla temporal en la mayoría de los casos.

Este es un problema común cuando se intenta ''burbujear'' datos de una cadena de procedimientos almacenados. Una restricción en SQL Server es que solo puede tener un INSERT-EXEC activo a la vez. Recomiendo consultar Cómo compartir datos entre procedimientos almacenados, que es un artículo muy completo sobre patrones para solucionar este tipo de problemas.

Por ejemplo, una solución podría ser convertir Sp3 en una función con valores de tabla.

Este truco funciona para mí.

No tiene este problema en el servidor remoto, porque en el servidor remoto, el último comando de inserción espera que se ejecute el resultado del comando anterior. No es el caso en el mismo servidor.

Aproveche esa situación para una solución alternativa.

Si tiene el permiso correcto para crear un servidor vinculado, hágalo. Crea el mismo servidor como servidor vinculado.

en SSMS, inicie sesión en su servidor
ve al "Objeto del servidor"
Haga clic derecho en "Servidores enlazados", luego "Nuevo servidor vinculado"
en el cuadro de diálogo, ingrese el nombre de su servidor vinculado, por ejemplo: THISSERVER
tipo de servidor es "Otra fuente de datos"
Proveedor: proveedor Microsoft OLE DB para servidor SQL
Fuente de datos: su IP, también puede ser solo un punto (.), Porque es localhost
Vaya a la pestaña "Seguridad" y elija la tercera "Realice usando el contexto de seguridad actual de inicio de sesión"
Puede editar las opciones del servidor (3ª pestaña) si lo desea
Presiona OK, tu servidor vinculado es creado

ahora su comando Sql en el SP1 es

insert into @myTempTable exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Créame, funciona incluso si tiene inserción dinámica en SP2

Mi trabajo alrededor de este problema siempre ha sido utilizar el principio de que las tablas temporales hash únicas están en el alcance de cualquier proceso llamado. Entonces, tengo un interruptor de opción en los parámetros de proceso (por defecto está desactivado). Si esto está activado, el proceso llamado insertará los resultados en la tabla temporal creada en el proceso de llamada. Creo que en el pasado di un paso más y puse un código en el proceso llamado para verificar si la tabla hash única existe en el alcance; si lo hace, inserte el código; de lo contrario, devuelva el conjunto de resultados. Parece funcionar bien: la mejor forma de pasar grandes conjuntos de datos entre los procesos.

OK, alentado por jimhark aquí hay un ejemplo del viejo enfoque de tabla hash simple:

CREATE PROCEDURE SP3 as BEGIN SELECT 1, ''Data1'' UNION ALL SELECT 2, ''Data2'' END go CREATE PROCEDURE SP2 as BEGIN if exists (select * from tempdb.dbo.sysobjects o where o.xtype in (''U'') and o.id = object_id(N''tempdb..#tmp1'')) INSERT INTO #tmp1 EXEC SP3 else EXEC SP3 END go CREATE PROCEDURE SP1 as BEGIN EXEC SP2 END GO /* --I want some data back from SP3 -- Just run the SP1 EXEC SP1 */ /* --I want some data back from SP3 into a table to do something useful --Try run this - get an error - can''t nest Execs if exists (select * from tempdb.dbo.sysobjects o where o.xtype in (''U'') and o.id = object_id(N''tempdb..#tmp1'')) DROP TABLE #tmp1 CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20)) INSERT INTO #tmp1 EXEC SP1 */ /* --I want some data back from SP3 into a table to do something useful --However, if we run this single hash temp table it is in scope anyway so --no need for the exec insert if exists (select * from tempdb.dbo.sysobjects o where o.xtype in (''U'') and o.id = object_id(N''tempdb..#tmp1'')) DROP TABLE #tmp1 CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20)) EXEC SP1 SELECT * FROM #tmp1 */

Tuve el mismo problema y preocupación por el código duplicado en dos o más sprocs. Terminé agregando un atributo adicional para "modo". Esto permitió que existiera un código común dentro de un sproc y el flujo de modo dirigido y el conjunto de resultados del sproc.