amikor a következő szkriptet futtatom, az jól fut:
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;" de amikor Saját sp_foreachdb eljárást használok, a forráskód az alábbi linken található:
Megbízhatóbb és rugalmasabb sp_MSforeachdb
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;" Kivételt kapok (kérjük, vegye figyelembe, hogy kivételkezelést adtam hozzá az eljáráshoz)
--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) Megjegyzések
- Nem ‘ nem tudom, miért használod ‘ ezt a tekervényeset módszer. Miért ne csak a sys.master_files lekérdezést jelentené?
- @Nic jól rámutatott, azonban én csak az sp_helpfile példát használtam. a való életben ez az egyik saját tárolt eljárásunk, amelyet hívnék
Válasz
A forráskód mert Aaron “s sp_foreachdb a következő sort tartalmazza:
INSERT #x EXEC sp_executesql @sql;
Az Ön véleménye szerint hibaüzenet:
Az INSERT EXEC utasítás nem ágyazható be.
Tehát az alábbi kód nem lesz érvényes, mivel INSERT xxx EXEC xxx kódot fészkel.
INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" Megjegyzések
- @marcellomiorelli Úgy gondolom, hogy a dinamikus SQL eltávolításához nagy változtatásra lenne szükség Aaron ‘ SP-jén. Ehhez valószínűleg a egy további függvény.
Válasz
Ha saját szerveréhez csatlakozik elosztott lekérdezési módszerekkel, akkor az trükk, ha sp_foreachdb egyetlen eredményt adna vissza.
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 w lekérdezés felett ill csak az sp_foreachdb végrehajtás első eredményhalmazát adja vissza. de az alábbi lekérdezés egyetlen adatbázisban adja vissza az összes adatbázis eredményét.
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 Válasz
Megpróbálhatja Aaron SP alkalmazkodását megszabadulással dinamikus része. A dinamikus rész állítólag felépít egy lekérdezést, amely csak az sys.databases adatbázis neveit olvassa el a megadott argumentumok alapján. A dinamikus SQL-t a lekérdezés leghatékonyabbá tételére választják – Figyelembe véve sajátos igényeit, bizonyos áldozatok rendben lehetnek.
Ugyanakkor azt állítom, hogy az előadás nem sokat szenvedhet az alább felajánlott átírás miatt, mivel a sys.databases rendszer nézetben általában nincs túl sok sor, de mindenképpen felveheti a OPTION (RECOMPILE) -t. Bármilyen lassú is, bár valószínűleg meglehetősen csúnya lesz a vége, amit ígérhetek.
Az átírás módszere a következő: Aaron eljárása egy ismétlődő minta felhasználásával építi fel a lekérdezést, ahol egy paraméter értékét ellenőrzik, , az eredmény alapján további lekérdezés hozzáadódik a dinamikus lekérdezéshez, például a következőképpen:
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 + ... Így írhatja át:
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); Például a @system_only szabályozza, hogy a database_id IN (1,2,3,4) szűrőt be kell-e venni, például:
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 Az átírt lekérdezés a következő módon kerül paraméterezésre:
SELECT name FROM sys.databases WHERE 1=1 AND (database_id IN (1,2,3,4) OR @system_only <> 1) -- or: @system_only = 0 A kapott lekérdezést használja közvetlenül a kurzort a
SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db helyett, mint látható, az utolsó érintésre a SELECT részre kell menni, ahol az egyszerű helyébe a következő lép:
SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END Nyilvánvaló, hogy most már megszabadulhat a #x tábla, amire már nincs szükség az SP-ben.
Egy utolsó megjegyzés az adott adatbázishoz tervezett futtatásra vonatkozik.
@command1="use ?; Exec sp_helpfile;" helyett csak
@command1="Exec ?..sp_helpfile;" válasz
Cserét írtam, amelynek számos fejlesztése van az eredeti sp_foreachdb , beleértve a fészkelés képességét. Az újabb, előnyben részesített eljárást sp_ineachdb ( 1. rész / 2. rész ), és a Brent Ozar “s első válaszkészlet részeként is elérhető.
Néhány háttér itt :