El uso de OPENQUERY (procedimiento almacenado ejecutivo) para crear una nueva tabla temporal falla con el error 11526
Tengo instalada la versión completa de SQL Server 2012 en mi PC de desarrollo.
Estoy tratando de seguir los ejemplos aquí , que muestran cómo crear una nueva tabla temporal utilizando un procedimiento almacenado como fuente de datos. Estoy intentando combinar los resultados de varios procedimientos almacenados en una única tabla temporal (la estructura/definición de columnas de los distintos conjuntos de resultados es idéntica).
Para probar si la plomería está funcionando, hago esta consulta:
SELECT * FROM OPENQUERY("FOO\SQL2012", 'exec mySchema.myStoredProc')
Pero recibo este error de esa simple consulta de selección de prueba de plomería:
Mensaje 11526, Nivel 16, Estado 1, Procedimiento sp_describe_first_result_set, Línea 1
No se pudieron determinar los metadatos porque la instrucción 'inserte #tmp(foo1, foo2, foo3) seleccione 'O' como foo1, foo2, foo3' en el procedimiento 'myStoredProc' usa una tabla temporal.
Si entiendo el error correctamente, OPENQUERY depende de que el servidor pueda extraer los tipos de datos de las columnas de la definición persistente en la base de datos, y la tabla temporal instanciada en mi proceso almacenado, al ser efímera, carece de una definición persistente. Si ese es el caso, ¿hay alguna configuración que le indique a OPENQUERY que haga lo mejor que pueda e intente hacer una suposición inteligente sobre los tipos de datos de las columnas?
Aquí está el SP ficticio con el que estoy probando:
create proc testproc
as
begin
create table #test
(id int, name varchar(5) );
insert into #test(id,name)values(1,'xxx');
select * from #test;
--drop table #test; -- tried dropping and not dropping, same error either way
end
Prueba esto:
SELECT *
FROM OPENQUERY("FOO\SQL2012", 'SET FMTONLY OFF; EXEC mySchema.myStoredProc;') X;
La razón de esto es que cuando ejecuta un procedimiento almacenado en un servidor vinculado, el proveedor primero intenta determinar la forma del conjunto de filas resultante. Para ello, emite SET FMTONLY ON;y luego ejecuta su declaración. En un procedimiento almacenado que no utiliza tablas temporales, esto funciona a la perfección. Básicamente, el analizador de consultas realiza un ensayo sin obtener todos los datos, solo los metadatos (algo así como mostrar un plan de ejecución estimado).
El problema es que cuando el procedimiento almacenado usa tablas temporales, falla porque los metadatos de la tabla temporal no existen: no se pueden recopilar a través del metanálisis que funciona para procedimientos almacenados que no usan tablas temporales. La solución, entonces, es hacerlo manualmente SET FMTONLY OFF;dentro del lote que ejecuta el procedimiento almacenado.
Tenga en cuenta que el uso de este método hará que el procedimiento almacenado se ejecute dos veces . La primera vez para recopilar los metadatos (los datos se descartan) y la segunda vez para devolver los datos. Si el llamado procedimiento almacenado es particularmente costoso o tiene efectos secundarios, es posible que deba hacer concesiones.
Finalmente, tenga en cuenta que este truco no funciona en todos los procedimientos almacenados. Hay cosas que los procedimientos almacenados pueden hacer y que simplemente complican el proceso. No conozco todas las posibilidades, pero una de ellas es devolver varios conjuntos de registros.
En respuesta a su actualización que SET FMTONLY OFFno funciona: ¿es posible reestructurar su SP para no usar una tabla temporal o usar una tabla permanente con clave de sesión? Cualquiera de estas opciones podría funcionar. En SQL Server 2012, también tiene la opción de pasar datos con parámetros con valores de tabla .
Quizás le interese leer Cómo compartir datos entre procedimientos almacenados de Erland Sommarskog , ya que podría inspirarle sobre una manera de lograr su propósito.
Agregar " WITH RESULT SETS [NONE | UNDEFINED]" al final de la llamada EXEC debería corregir este problema. http://technet.microsoft.com/en-us/library/ms188332.aspx
