cuando ejecuto la siguiente secuencia de comandos, funciona bien:
declare @temp table ( 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 @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" pero cuando Utilizo mi propio procedimiento sp_foreachdb, el código fuente está en este enlace a continuación:
Un sp_MSforeachdb más confiable y flexible
declare @temp table ( 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 @temp exec sp_foreachdb @command="use ?; Exec sp_helpfile;" Recibo una excepción (tenga en cuenta que agregué manejo de excepciones en ese procedimiento)
--EXCEPTION WAS CAUGHT-- THE ERROR NUMBER:8164 SEVERITY: 16 STATE: 1 PROCEDURE: sp_foreachdb LINE NUMBER: 165 ERROR MESSAGE: An INSERT EXEC statement cannot be nested. ------------------------------------ the sql ------------------------------------ SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N"ONLINE" AND is_read_only = 0 Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239 A cursor with the name "c" does not exist. Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240 A cursor with the name "c" does not exist. (0 row(s) affected) Comentarios
- Yo ‘ no estoy seguro de por qué ‘ estás usando esta complicación método. ¿Por qué no solo consultar sys.master_files?
- @Nic, bien señalado, sin embargo, acabo de usar sp_helpfile como ejemplo. en la vida real, es uno de nuestros propios procedimientos almacenados al que llamaría
Responder
El código fuente for Aaron «s sp_foreachdb contiene la siguiente línea:
INSERT #x EXEC sp_executesql @sql;
Según su mensaje de error:
No se puede anidar una instrucción INSERT EXEC.
Entonces un código como el siguiente no será válido ya que está anidando INSERT xxx EXEC xxx código.
INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" Comentarios
- @marcellomiorelli Creo que sería necesario realizar un gran cambio en el SP de Aaron ‘ para eliminar el SQL dinámico. Esto probablemente requeriría el uso de una función adicional.
Responder
Conectarse a su propio servidor a través de métodos de consulta distribuidos funcionaría si sp_foreachdb devolvería un único conjunto de resultados.
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 sobre la consulta w Devolveré solo el primer conjunto de resultados de la ejecución de sp_foreachdb. pero la siguiente consulta devolverá el resultado de todas las bases de datos en un solo conjunto de resultados.
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 Responder
Podrías intentar adaptar el SP de Aaron deshaciéndote de su parte dinámica. Se supone que la parte dinámica crea una consulta leyendo solo los nombres de la base de datos de sys.databases en función de los argumentos proporcionados. El SQL dinámico se elige para hacer la consulta más eficiente – así como mantenible. Teniendo en cuenta sus necesidades específicas, algunos sacrificios podrían estar en orden.
Sin embargo, diría que el rendimiento podría no sufrir mucho por la reescritura que estoy ofreciendo a continuación, como el sys.databases la vista del sistema generalmente no tiene muchas filas, pero en cualquier caso, podría agregar OPTION (RECOMPILE) al final. Por lento que sea, sin embargo, es probable que termine siendo bastante feo, lo prometo.
El método de reescritura es el siguiente. El procedimiento de Aaron es construir la consulta usando un patrón repetido donde se verifica el valor de un parámetro y , basado en el resultado, un adicional La consulta se agrega a la consulta dinámica, es decir, así:
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 + ... Así es como reescribiría eso:
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); Por ejemplo, @system_only controla si el filtro database_id IN (1,2,3,4) debe incluirse, así:
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 consulta reescrita se parametrizaría de esta forma:
SELECT name FROM sys.databases WHERE 1=1 AND (database_id IN (1,2,3,4) OR @system_only <> 1) -- or: @system_only = 0 Utilice la consulta resultante en el cursor directamente, en lugar de
SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db Como puede verse, el toque final tendría que ir a la parte SELECT, donde el simple SELECT name de la nueva consulta se reemplazaría con
SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END Obviamente, ahora también podría deshacerse del #x ya que ya no se necesita en el SP.
Una última nota se refiere al script específico que planea ejecutar en cada base de datos. En lugar de
@command1="use ?; Exec sp_helpfile;" , podría simplemente
@command1="Exec ?..sp_helpfile;"