À l'aide de la fonction OPENROWSET pour récupérer dynamiquement SP résultats lorsque SP contient # temp tables

D'accord.. j'ai abandonné et repris mon vieil ami xpcmdshell. Tout au long de cette réponse et son code le trait de soulignement (_) est implicite, pour xpcmdshell comme souvent, je ne peux pas charger les pages contenant le nom complet.

Tout d'abord, voici trois choses que j'ai essayé ça ne fonctionne PAS (je ne me souviens pas de tous les autres):

• SET NOCOUNT ON
  • Fonctionne pour n'importe quel service pack sans les tables temporaires, mais comme la plupart des 2500+- je vais être en regardant à travers les utiliser, ce n'est pas faisable.
• Pas Op
  • J'ai créé une procédure pour créer dynamiquement un Pas Op, cependant, au moment de la mise en œuvre, j'ai été incapable de trouver un moyen de contourner SQL coincé dans une imbrication de boucle.
• bcp queryout
  • De sortie n'a pas d'inclure les en-têtes de

Et donc, après beaucoup de tête dénigrement et la recherche sur Google, je suis tombé en arrière pour xpcmdshell. Le script suivant (que je vais avoir dans une procédure) prend un SP instruction exec et la base de données pour l'exécuter en vertu de l', formats un xpcmdshell sqlquery de commande dans un fichier, exécute le fichier et insère la sortie de celui-ci dans une table temporaire, puis extrait les en-têtes de colonne de ces résultats dans une autre table temp.

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

Si vous prévoyez d'utiliser ce code, n'oubliez pas de faire un rechercher remplacer pour xpcmdshell à xp(_)cmdshell

Espère que cela aide quelqu'un! S'il vous plaît n'hésitez pas à poster des questions, des commentaires ou des suggestions que vous pourriez avoir.

OriginalL'auteur Zok Wobblefotz