Verwendung von OPENROWSET zum dynamischen Abrufen von SP-Ergebnissen, wenn SP # temporäre Tabellen enthält

Question

Mein Szenario

Ich arbeite an einer Datenbank, die viele Details aus verschiedenen Stored Procedures in verschiedenen Datenbanken auf dem gesamten Server enthalten wird. Die Informationen, die ich jetzt zu sammeln versuche, sind: "Was gibt die SP aus?"

Bei der Suche habe ich festgestellt, dass die Antwort in OPENROWSET liegt. Meine ersten Tests waren erfolgreich und alles sah gut aus. Beim Testen mit Live-SPs stieß ich jedoch auf ein großes Problem: Es funktioniert nicht gut mit temporären (#) Tabellen.

Zum Beispiel:

Wenn ich diese SP nehmen würde:

CREATE PROCEDURE dbo.zzTempSP(@A INT, @B INT) AS
SELECT @A AS A, @B AS B

Mit dem folgenden Code kann ich die Ausgabe leicht in eine temp (##)-Tabelle einfügen, dann die sysobjects von tempdb abfragen und eine Liste der Spalten und ihrer Datentypen erstellen:

IF OBJECT_ID('tempdb.dbo.##TempOutput','U') IS NOT NULL DROP TABLE ##TempOutput

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'SELECT * 
               INTO ##TempOutput
               FROM OPENROWSET(''SQLNCLI'', ''Server=' + 
        CONVERT(VARCHAR(100), SERVERPROPERTY('MachineName')) +
                             ';Trusted_Connection=yes;'', ''SET FMTONLY OFF exec ' + 
                               DB_NAME() + 
                              '.dbo.zzTempSP @A=1, @B=2'')'
EXEC(@sql)

SELECT *
FROM ##TempOutput

Großartig! Allerdings, wenn die SP war dies statt:

CREATE PROCEDURE dbo.zzTempSP (@A INT, @B INT) AS CREATE TABLE dbo.#T (A INT, B INT)

INSERT INTO dbo.#T
SELECT   @A AS A, @B AS B

SELECT *
FROM dbo.#T

Wenn ich die gleiche OPENROWSET Code wie zuvor erhalte ich den folgenden Fehler:

Das Objekt "SET FMTONLY OFF exec DatabaseName.dbo.zzTempSP @A=1,@B=2" kann nicht verarbeitet werden. Der OLE DB-Anbieter "SQLNCLI10" für den verknüpften Server "(null)" zeigt an, dass entweder das Objekt keine Spalten hat oder der aktuelle Benutzer keine Berechtigungen für dieses Objekt hat.

Wenn ich den OPENROWSET-Code (durch Entfernen des dynamischen Zeugs) auf Folgendes reduziere:

SELECT   *
FROM OPENROWSET('SQLNCLI','Server=ServerName;Trusted_Connection=yes;',
                          'exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
              )

Ich erhalte den folgenden (viel nützlicheren) Fehler:

Ungültiger Objektname '#T'.

Und da bin ich gegen die Wand gefahren. Bei meinen Recherchen stellte sich heraus, dass es keine Lösung gibt, aber ich konnte mich nicht dazu durchringen, einfach aufzugeben.

Und so werde ich dazu verleitet

Meine Frage an Sie

Ist jemandem eine Möglichkeit bekannt, diesen Fehler zu umgehen? Oder gibt es möglicherweise eine alternative Lösung?

Dieser Prozess wird nicht häufig ausgeführt, so dass ich mir keine allzu großen Sorgen um die Effizienz der Lösung machen muss.

Jeder Beitrag wäre sehr willkommen.

Danke! Zok

PS: Entschuldigung für die Formatierung. Ich habe die Sprachtags nicht ganz verstanden.

Answer 1

Ich hatte diese Frage auch auf SQL Server Central gestellt, und einige Antworten haben mich dazu gebracht, in OPENROWSET nach einer Antwort zu suchen (und sie zu finden). Einer der Teilnehmer verwies mich auf dieser Artikel den Abschnitt über OPENQUERY. Dort heißt es, dass Sie zur Umgehung des Problems mit temporären Tabellen einfach SET FMTONLY OFF in die Ausführungszeile Ihrer OPENQUERY/OPENROWSET-Anweisung einfügen, und zwar wie folgt:

SELECT  *
FROM    OPENROWSET( 'SQLNCLI',
                    'Server=SERVERNAME;Trusted_Connection=yes;',
                    'SET FMTONLY OFF; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
                  )

Wenn in der Prozedur jedoch nicht SET NOCOUNT ON angegeben ist, wird trotzdem ein Fehler ausgegeben. Ich hatte ein dummes Missverständnis über SET NOCOUNT ON in meinem Hinterkopf, das mich davon abhielt zu denken: "Hey, kann ich nicht einfach SET NOCOUNT ON zur Ausführungsanweisung von OPENROWSET hinzufügen?" Sobald jemand diese Frage stellte für mich auf den anderen Thread es machte alles zu viel Sinn =) So, hier ist die Lösung, die ich für alle entlang gesucht:

SELECT  *
FROM    OPENROWSET( 'SQLNCLI',
                    'Server=SERVERNAME;Trusted_Connection=yes;',
                    'SET FMTONLY OFF; SET NOCOUNT ON; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
                  )

Answer 2

Okay, ich habe aufgegeben und bin zu meinem alten Freund xpcmdshell zurückgekehrt. In dieser Antwort und ihrem Code wird der Unterstrich (_) für xpcmdshell impliziert, da ich Seiten mit dem vollständigen Namen oft nicht laden kann.

Zunächst einmal sind hier nur drei der Dinge, die ich ausprobiert habe und die NICHT funktioniert haben (an alle anderen kann ich mich nicht erinnern):

• SET NOCOUNT EIN
  • Funktioniert für jeden SP ohne temporäre Tabellen, aber da die meisten der über 2500, die ich durchsehen werde, diese verwenden, ist das nicht machbar.
• Keine Op
  • Ich habe eine Prozedur erstellt, um dynamisch ein No Op zu erstellen, aber bei der Implementierung konnte ich keinen Weg finden, wie SQL in einer Verschachtelungsschleife stecken bleibt.
• bcp-Abfrageout
  • Die Ausgabe enthält keine Kopfzeilen

Und so bin ich nach viel Kopfzerbrechen und Googeln wieder auf xpcmdshell zurückgekommen. Das folgende Skript (das ich in eine Prozedur umwandeln werde) nimmt eine SP exec-Anweisung und die Datenbank, unter der es ausgeführt werden soll, formatiert einen xpcmdshell sqlquery-Befehl in eine Datei, führt die Datei aus und fügt die Ausgabe in eine temporäre Tabelle ein und extrahiert dann die Spaltenüberschriften dieser Ergebnisse in eine weitere temporäre Tabelle.

SET NOCOUNT ON

DECLARE    @TempCmdPath VARCHAR(MAX),
        @ProcedureExec VARCHAR(MAX),
        @DatabaseName VARCHAR(255)

SELECT    @TempCmdPath = 'C:\Temp\' --Make sure path ends with a '\' (or add logic to append if missing)

SELECT    @ProcedureExec = 'exec dbo.crp_rpt_GetCustomerDetails @ShowContacts=0,@CustomerName=''cust123%''' --Make sure to double up the single quotes (')
SELECT    @ProcedureExec = REPLACE(@ProcedureExec, '''', '''''') --Double the single quotes again (') for use in xpcmdshell sqlquery command

SELECT    @DatabaseName = 'CorpDB'

IF OBJECT_ID('tempdb.dbo.#CmdOut','U') IS NOT NULL
        DROP TABLE dbo.#CmdOut

CREATE TABLE dbo.#CmdOut
    (
      id INT IDENTITY(1,1), --Used in ROW_NUMBER() function to update rid
      rid INT, --Actual number for use in WHILE loop
      LineOut VARCHAR(MAX)
    )

DECLARE    @cmdshell VARCHAR(MAX)

/* Create a file with the commands to run */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''sqlcmd '
                    + REPLACE( '-q "PRINT '':error ' + @TempCmdPath + 'TempSqlCmdOut.txt'' ' --Set errors to be directed to a text file
                                    + 'PRINT ''' + @ProcedureExec + '''" ' --Add additional PRINT statements to include more statements to run
                                + '-o "' + @TempCmdPath + 'TempSqlCmd.txt" ' --Specify where the file should output to
                               , '''', '''''' ) --Double up the single quotes (') /again/ for this statement
                    + '''' --Close the statement

PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )

/* Execute the commands stored in the file we just created */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''sqlcmd '
                    + '-d ' + @DatabaseName + ' '
                    + '-r 1 ' --Set any additional messsages to be treated as errors.  This, combined with the ":error <path>\TempSqlCmdOut.txt" line above, will ensure that print statements are not returned in the output
                    + '-i "' + @TempCmdPath + 'TempSqlCmd.txt" '
                    + '-s "," ' --Column Separator
                    + '''' --Close the statement

PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )

/* Clean up. Delete the two temp files */
SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''del "' + @TempCmdPath + 'TempSqlCmd.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )

SELECT    @cmdshell = 'exec master.dbo.xpcmdshell ''del "' + @TempCmdPath + 'TempSqlCmdOut.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
        EXEC ( @cmdshell )

/* Clean up NULL rows then update the rid column's value */
DELETE    dbo.#CmdOut
WHERE    LineOut IS NULL

UPDATE    co
SET        rid = n.rid
FROM    dbo.#CmdOut co
        INNER JOIN (    SELECT    id,
                                ROW_NUMBER() OVER ( ORDER BY id ) AS [rid]
                        FROM    dbo.#CmdOut
                   ) AS n ON co.id = n.id

--SELECT * FROM dbo.#CmdOut

---------------------------------------------------------------
---------------------------------------------------------------

IF OBJECT_ID('tempdb.dbo.#SPResultHeaders','U') IS NOT NULL
        DROP TABLE dbo.#SPResultHeaders

CREATE TABLE dbo.#SPResultHeaders
    (
      id INT IDENTITY(1,1),
      HeaderName VARCHAR(500)
    )

DECLARE    @LineCount INT,
        @LineIndex INT,
        @Delimiter VARCHAR(10),
        @PrevDelimitCharIndex INT,
        @NextDelimitCharIndex INT,
        @LineText VARCHAR(MAX),
        @EndOfLineText VARCHAR(MAX),
        @FoundDivider BIT

SELECT    @Delimiter = ',',
        @FoundDivider = 0

SELECT    @LineCount = COUNT(*),
        @LineIndex = 1
FROM    dbo.#CmdOut

/* Until we move through all of the output lines OR we run into the line between the headers and their data (divider).. */
WHILE ( @LineIndex <= @LineCount
        AND @FoundDivider = 0
      )
    BEGIN
        /* Reset DelimitCharIndex: */
        SELECT    @PrevDelimitCharIndex = 0,
                @NextDelimitCharIndex = 1

        /* Until the Delimiter is not found.. */
        WHILE ( @NextDelimitCharIndex <> 0
                AND @FoundDivider = 0
              )
            BEGIN
                /* Search for the Delimiter starting after the last one's position */
                SELECT    @NextDelimitCharIndex = CHARINDEX(@Delimiter, LineOut, @PrevDelimitCharIndex)
                FROM    dbo.#CmdOut
                WHERE    rid = @LineIndex

                /* If another Delimiter is found on this line.. */
                IF ( @NextDelimitCharIndex <> 0 OR @EndOfLineText IS NOT NULL )
                    BEGIN
                        /* Make sure we're don't have left overs from a previous line */
                        IF ( @EndOfLineText IS NOT NULL )
                            BEGIN
                                /* If we do, set the current string to the previous + the current */
                                SELECT    @LineText = @EndOfLineText + SUBSTRING(LineOut, @PrevDelimitCharIndex, (@NextDelimitCharIndex - @PrevDelimitCharIndex))
                                FROM    dbo.#CmdOut
                                WHERE    rid = @LineIndex

                                /* Then clear out the left overs */
                                SELECT    @EndOfLineText = NULL
                            END
                        ELSE
                            BEGIN
                                /* Get the text between the previous delimiter and the next */
                                SELECT    @LineText = SUBSTRING(LineOut, @PrevDelimitCharIndex, (@NextDelimitCharIndex - @PrevDelimitCharIndex))
                                FROM    dbo.#CmdOut
                                WHERE    rid = @LineIndex
                            END

                        /* After the column headers in the output it will have a divider consisting of hyphens (-) (split by whatever we specified for the -s argument of the sqlcmd)
                            Check to see if our text is purely hyphens. IF NOT, insert the text into our result table and increment Header Count by 1.  IF SO, set the FoundDivider flag to 1.
                        */
                        IF ( LTRIM(RTRIM(REPLACE(@LineText, '-', ''))) <> '' )
                            BEGIN
                                IF ( CHARINDEX('-', @LineText) <> 0 )
                                    BEGIN
                                        /* If there are more than three hyphens in a row, assume it's the divider and set @FoundDivider to 1 to exit while */
                                        IF ( SUBSTRING(@LineText, CHARINDEX('-', @LineText), 3) = '---' )
                                                SELECT    @FoundDivider = 1
                                        ELSE
                                            INSERT INTO dbo.#SPResultHeaders ( HeaderName )
                                                    SELECT    LTRIM(RTRIM(@LineText))
                                    END
                                ELSE
                                    BEGIN
                                        INSERT INTO dbo.#SPResultHeaders ( HeaderName )
                                                SELECT    LTRIM(RTRIM(@LineText))
                                    END
                            END
                        ELSE
                            BEGIN
                                /* If there are more than three hyphens in a row, assume it's the divider and set @FoundDivider to 1 to exit while */
                                IF ( SUBSTRING(@LineText, CHARINDEX('-', @LineText), 3) = '---' )
                                        SELECT    @FoundDivider = 1
                            END
                    END
                /* If another Delimiter is NOT found on this line.. */
                ELSE
                    BEGIN
                        /* Move remainder of this line's text to @EndOfLineText ("left overs") for use in next itteration */
                        SELECT    @LineText = NULL,
                                @EndOfLineText = SUBSTRING(LineOut, @PrevDelimitCharIndex, (LEN(LineOut) + 1))
                        FROM    dbo.#CmdOut
                        WHERE    rid = @LineIndex
                    END

                /* Update previous Delimiter's position */
                SELECT    @PrevDelimitCharIndex = @NextDelimitCharIndex + 1
            END --WHILE ( @NextDelimitCharIndex <> 0 )

        SELECT    @LineIndex = @LineIndex + 1
    END --WHILE ( @LineIndex <= @LineCount )

SELECT    *
FROM    dbo.#SPResultHeaders

Wenn Sie diesen Code verwenden möchten, vergessen Sie nicht, xpcmdshell durch xp(_)cmdshell zu ersetzen

Hoffentlich hilft das jemandem! Bitte zögern Sie nicht, uns Ihre Fragen, Kommentare oder Vorschläge mitzuteilen.

Answer 3

Sie verwenden eine Temp-Tabellenvariable #T. Sie müssen eine temporäre Tabelle @T verwenden. Nach meinem Verständnis können Temp-Tabellenvariablen nicht in verteilten Transaktionsumgebungen verwendet werden, und auch, dass Sie möglicherweise keinen Zugriff auf die TempDB im verknüpften Server haben.