October 19, 2011 at 5:31 pm
[h1]My Scenario[/h1]
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..
[h1]My question to you[/h1]
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
October 21, 2011 at 3:54 pm
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):
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.xp_cmdshell ''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.xp_cmdshell ''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.xp_cmdshell ''del "' + @TempCmdPath + 'TempSqlCmd.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
EXEC ( @cmdshell )
SELECT@cmdshell = 'exec master.dbo.xp_cmdshell ''del "' + @TempCmdPath + 'TempSqlCmdOut.txt"'''
PRINT @cmdshell
INSERT INTO dbo.#CmdOut ( LineOut )
EXEC ( @cmdshell )
/* Clean up NULL rows then update the rid column's value */
DELETEdbo.#CmdOut
WHERELineOut IS NULL
UPDATEco
SETrid = n.rid
FROMdbo.#CmdOut co
INNER JOIN (SELECTid,
ROW_NUMBER() OVER ( ORDER BY id ) AS [rid]
FROMdbo.#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
FROMdbo.#CmdOut
/* Until we move through all of the output lines OR the Divider Count meets the Header count.. */
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)
FROMdbo.#CmdOut
WHERErid = @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 current appended to the previous */
SELECT@LineText = @EndOfLineText + SUBSTRING(LineOut, @PrevDelimitCharIndex, (@NextDelimitCharIndex - @PrevDelimitCharIndex))
FROMdbo.#CmdOut
WHERErid = @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))
FROMdbo.#CmdOut
WHERErid = @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, increment Divider Count by 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 )
SELECTLTRIM(RTRIM(@LineText))
END
ELSE
BEGIN
INSERT INTO dbo.#SPResultHeaders ( HeaderName )
SELECTLTRIM(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))
FROMdbo.#CmdOut
WHERErid = @LineIndex
END
/* Update previous Delimiter's position */
SELECT@PrevDelimitCharIndex = @NextDelimitCharIndex + 1
END --WHILE ( @NextDelimitCharIndex <> 0 )
SELECT@LineIndex = @LineIndex + 1
END --WHILE ( @LineIndex <= @LineCount )
SELECT*
FROMdbo.#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.
October 24, 2011 at 4:14 am
ZokWobblefotz (10/19/2011)
[h1]My Scenario[/h1]I'm working on a database which will contain many details from various Stored Procedures in different databases across the entire server. ...
Can you confirm that you are working with objects located on a single server?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2011 at 8:06 am
Stewart, the method you propose requires me to know the output of a procedure in advance, does it not?
ChrisM, I am indeed working on one single server.
October 24, 2011 at 8:19 am
ZokWobblefotz (10/24/2011)
Stewart, the method you propose requires me to know the output of a procedure in advance, does it not?ChrisM, I am indeed working on one single server.
It's not clear from the sample code, but there are two types of temp table, ##global temp tables and #local temp tables. The difference is scope -global temp tables are visible to other connections, local ones are not. This restriction to local temp tables extends to sqlexec - IIRC if the temp table is created within the batch of the executed string then it's not visible on return to the calling batch, but if it's created before then the contents can be written to within the batch and are visible on return to the caller. Darn, have to script and test this now.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2011 at 8:27 am
DECLARE @sql VARCHAR(MAX)
IF(OBJECT_ID('TempDB..##TempOutput') IS NOT NULL) DROP TABLE ##TempOutput
SELECT @sql = 'SELECT * INTO ##TempOutput FROM (SELECT Today = GETDATE()) d'
EXEC(@sql)
SELECT * FROM ##TempOutput
-- returns "now"
IF(OBJECT_ID('TempDB..#TempOutput') IS NOT NULL) DROP TABLE #TempOutput
SELECT @sql = 'SELECT * INTO #TempOutput FROM (SELECT Today = GETDATE()) d'
EXEC(@sql)
SELECT * FROM #TempOutput
-- fails: Invalid object name '#TempOutput'.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2011 at 9:19 am
Stewart, I think I agree, but the purpose of this utility is to catalog the output columns of procedures without having prior knowledge of said output. I had hoped to be able to catch their data types as well, but having to omit that detail isn't game ending for this situation
Chris, you're referring to the initial samples, right? I am using a global temp in that so I'm a little confused as to where you're going?
October 24, 2011 at 9:30 am
Also, if a local temp table is created outside of the EXEC statement it can be referenced within one like so:
DECLARE @sql VARCHAR(MAX)
IF(OBJECT_ID('TempDB..#TempOutput') IS NOT NULL) DROP TABLE #TempOutput
CREATE TABLE #TempOutput (Today DATETIME)
SELECT @sql = 'INSERT INTO #TempOutput SELECT GETDATE()'
EXEC(@sql)
SELECT * FROM #TempOutput
However IIRC, temp tables, whether global or local, exist as the session exists. e.g. Whether # or ## if I create a table within a SSMS tab, those tables are disposed of when I close it. So EXEC must work a little differently?? Since it can reference a # table but also keep a ## table alive after its execution completes.
October 24, 2011 at 9:40 am
ZokWobblefotz (10/24/2011)
Also, if a local temp table is created outside of the EXEC statement it can be referenced within one like so:
DECLARE @sql VARCHAR(MAX)
IF(OBJECT_ID('TempDB..#TempOutput') IS NOT NULL) DROP TABLE #TempOutput
CREATE TABLE #TempOutput (Today DATETIME)
SELECT @sql = 'INSERT INTO #TempOutput SELECT GETDATE()'
EXEC(@sql)
SELECT * FROM #TempOutput
However IIRC, temp tables, whether global or local, exist as the session exists. e.g. Whether # or ## if I create a table within a SSMS tab, those tables are disposed of when I close it. So EXEC must work a little differently?? Since it can reference a # table but also keep a ## table alive after its execution completes.
The method you're considering looks close to being viable with only a few changes. This article should get you back on track - OPENQUERY, near the end of the article.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 24, 2011 at 9:57 am
My mind has just been blown..
The solution in there seems to be using 'SET FMTONLY OFF' before the execute statement in OPENROWSET or OPENQUERY, which I had tested extensively (or so I had thought) but it seems to be working just fine now??? I'm going to do more testing with it to see if I can't break it again.
October 24, 2011 at 11:16 am
Okay, if SET NOCOUNT ON is not specified for a procedure the SET FMTONLY OFF still does not resolve the issue.
Again, since there will be 2500+- predefined SPs to go through, I don't want to have to go open each and add this line if it's missing.
October 25, 2011 at 9:25 am
Once again my mind has been blown ^_^
For some reason I was under the impression that using SET NOCOUNT ON didn't have an affect on procedures, as in, if I were to turn it on before I execute an SP (that doesn't have that line) it wouldn't change that behavior. So.. I never thought to try that!!
Thank you very much Stewart and Chris.
Though my previous solution still works, this one is SO much more elegant and exactly what I had initially hope for.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply