miten vältetään “ INSERT EXEC -käskyä ei voida sijoittaa tähän tilanteeseen ”?

Lähdekoodi Aaronille ”s sp_foreachdb sisältää seuraavan rivin:

INSERT #x EXEC sp_executesql @sql;

virhesanoma:

INSERT EXEC -käskyä ei voida sisäkkäin.

Joten alla oleva tällainen koodi ei ole kelvollinen, koska se pesii INSERT xxx EXEC xxx -koodia.

INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" 

Kommentit

• @marcellomiorelli Mielestäni se vaatii suuren muutoksen Aaronin ’ SP: hen dynaamisen SQL: n poistamiseksi. Tämä edellyttäisi todennäköisesti lisätoiminto.

Yhteyden muodostaminen omaan palvelimeen hajautettujen kyselymenetelmien avulla tekisi tempun, jos sp_foreachdb palauttaisi yhden tulosjoukon.

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 

kyselyn yläpuolella w huonosti palauttaa vain sp_foreachdb-suorituksen ensimmäisen tulosjoukon. mutta alla oleva kysely palauttaa kaikkien tietokantojen tulokset yhdessä tulosjoukossa.

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 

Voit yrittää mukauttaa Aaronin SP: tä päästä eroon dynaamisen osan. Dynaamisen osan on tarkoitus rakentaa kysely, joka lukee vain tietokannan nimet kohdasta sys.databases toimitettujen argumenttien perusteella. Dynaaminen SQL valitaan tekemään kyselystä tehokkain – Kun otetaan huomioon erityistarpeesi, jotkut uhraukset saattavat olla kunnossa.

Väitän kuitenkin, että suorituskyky ei ehkä kärsi paljon alla tarjoamastani uudelleenkirjoittamisesta, koska sys.databases järjestelmänäkymässä ei yleensä ole kovin monta riviä, mutta joka tapauksessa voit lisätä lopuksi OPTION (RECOMPILE). Vaikka se voikin olla hidasta, tosin todennäköisesti loppujen lopuksi melko ruma, mitä voin luvata.

Uudelleen kirjoittamismenetelmä on seuraava. Aaronin menettely rakentaa kyselyn toistuvan mallin avulla, jossa parametriarvo tarkistetaan ja , tuloksen perusteella ylimääräinen kysely lisätään dynaamiseen kyselyyn, näin:

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 + ... 

Näin kirjoitat sen uudestaan:

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); 

Esimerkiksi @system_only ohjaa, sisällytetäänkö database_id IN (1,2,3,4) -suodatin, kuten tämä:

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 

Uudelleen kirjoitettu kysely parametrisoidaan tällä tavalla:

SELECT name FROM sys.databases WHERE 1=1 AND (database_id IN (1,2,3,4) OR @system_only <> 1) -- or: @system_only = 0 

Käytä tuloksena olevaa kyselyä kohdistin suoraan sen sijaan, että

 SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db 

Kuten voidaan nähdä, viimeisen kosketuksen tulisi siirtyä SELECT-osaan, jossa yksinkertainen korvataan sanalla

 SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END 

Voit tietysti päästä eroon myös #x -taulukkoa ei enää tarvita SP: ssä.

Viimeinen huomautus koskee tiettyä komentosarjaa, jonka aiot suorittaa kussakin tietokannassa. Voit

@command1="use ?; Exec sp_helpfile;" 

sijasta vain

@command1="Exec ?..sp_helpfile;" 

Kirjoitin korvaavan osan, jolla on useita parannuksia alkuperäiseen sp_foreachdb , mukaan lukien kyky pesiä. Uudempaa, ensisijaista menettelyä kutsutaan nimellä sp_ineachdb ( osa 1 / osa 2 ), ja se on saatavana myös osana Brent Ozarin ”s First Responder Kit .

Tausta täällä :

• Potkuttavat tottumukset: luottaminen dokumentoimattomaan käyttäytymiseen