My Scenario
I'm working on a database which will contain many details from various Stored Procedures in different databases across the entire server. The information I'm attempting to gather now is, "What does the SP output?"
In searching I've found that the answer lies in OPENROWSET. My initial testing was successful and everything looked great. However, upon testing it with live SPs I ran into one major problem: It doesn't play well with temp (#) tables.
For example:
If I were to take this SP:
CREATE PROCEDURE dbo.zzTempSP(@A INT, @B INT) AS
SELECT @A AS A, @B AS B
I can easily insert the output into a temp (##) table with the following code, then query tempdb's sysobjects and produce a list of the columns and their data types:
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
Great! However, if the SP was this instead:
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
When I execute the same OPENROWSET code as before I receive the following error:
Cannot process the object "SET FMTONLY OFF exec DatabaseName.dbo.zzTempSP @A=1,@B=2". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
When I trim down the OPENROWSET code (by removing the dynamic stuff) to this:
SELECT *
FROM OPENROWSET('SQLNCLI','Server=ServerName;Trusted_Connection=yes;',
'exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
)
I receive the following (much more useful) error:
Invalid object name '#T'.
Which is where I hit the wall. In my searching it seems that there is no solution, but I couldn't bring myself to give up on it just yet.
And so I'm led to..
My question to you
Is anyone aware of any possible way to circumvent this error? Or is there possibly an alternative solution?
This process won't be run frequently so I needn't worry too much about the solution's efficiency.
Any input would be greatly appreciated.
Thanks,
Zok
PS: Sorry about the formatting. I didn't quite figure out the language tags.
I had this question posted on SQL Server Central as well and some responses turned me back to looking for an answer within OPENROWSET (and finding it). One of the people turned me to this article's section on OPENQUERY. It states that in order to work around the issue with temp tables you simply add SET FMTONLY OFF to the execute line of your OPENQUERY/OPENROWSET statement like so:
SELECT *
FROM OPENROWSET( 'SQLNCLI',
'Server=SERVERNAME;Trusted_Connection=yes;',
'SET FMTONLY OFF; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
)
However, if the procedure does not have SET NOCOUNT ON specified it still raises an error.
I had a silly misunderstanding about SET NOCOUNT ON in the back of my head that stopped me from thinking, "Hey, can't I just add SET NOCOUNT ON to the execute statement of OPENROWSET??" Once someone asked that question for me on the other thread it made all too much sense =) So, here is the solution I've been looking for all along:
SELECT *
FROM OPENROWSET( 'SQLNCLI',
'Server=SERVERNAME;Trusted_Connection=yes;',
'SET FMTONLY OFF; SET NOCOUNT ON; exec DatabaseName.dbo.zzTempSP @A=1,@B=2'
)
Okay.. I've given up and gone back to my old friend xpcmdshell. Throughout this response and its code the underscore (_) will be implied for xpcmdshell as I often can't load pages containing the full name.
First, here are just three of the things I've tried that did NOT work (I can't recall all of the others):
- SET NOCOUNT ON
- Works for any SP without temp tables, but as most of the 2500+- I'll be looking through make use of them this isn't feasible.
- No Op
- I created a procedure to dynamically create a No Op, however upon implementation I was unable to find a way around SQL getting stuck in a nesting loop.
- bcp queryout
- Output doesn't include headers
And so, after much head bashing and Googling, I've fallen back to xpcmdshell. The following script (which I'll be turning into a procedure) takes an SP exec statement and the database to run it under, formats an xpcmdshell sqlquery command into a file, executes the file and inserts the output of it into a temp table, then extracts the column headers of those results into another temp table.
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
If you plan to use this code, don't forget to do a find replace for xpcmdshell to xp(_)cmdshell
Hope this helps someone! Please don't hesitate to post any questions, comments, or suggestions you may have.
You are using a Temp table variable #T. You have to use a Temporary table @T.
As per my understanding, Temp table variable can not be used in distributed transaction environment and also, that you might not have access to the TempDB in the linked server.
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 男人必须洒脱 的文章《Using OPENROWSET to dynamically retrieve SP result》','https://www.manongdao.com/article-561580.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}