March 16, 2012 at 6:20 am
Title says it all. Is there a way to suppress the output generated by sp_configure? I'm creating some text output reports for audit purposes and have to enable / disable xp_cmdshell to get some of the information, but I don't want the output from sp_configure included in the text file. Thanks in advance for any suggestions / solutions.
March 27, 2012 at 7:49 am
Oddly enough, I needed to do this in the past as well. The best solution seemed to be low tech: run the query in three distinct and [separate] steps/queries:
1.) enable xp_cmdshell (one query)
2.) run your script or query, creating your 'clean' output file
3. disable xp_cmdshell (last query)
As a side note, I had to run sp_configure as part of a DR script generation, so my script was generating a .sql script as output. In this case, I simply put a '/*' before I ran sp_configure, then put a '*/' after, effectively [commenting out] sp_configure's output so it didn't mess up the output script.
Cheers,
Ken
March 27, 2012 at 8:10 am
In case you wanted something quick for the "script generating a script" thing...
DECLARE @xpcmdshellflag TINYINT,
@exec_str NVARCHAR(MAX)
-- Temporarily enable xp_cmdshell if it's disabled
SELECT @xpcmdshellflag = 0
IF (SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'xp_cmdshell') = 0
BEGIN
SELECT @exec_str = 'EXEC sp_configure '
+ QUOTENAME('allow updates', CHAR(39))
+ ', 1; RECONFIGURE WITH OVERRIDE;'
+ ' EXEC sp_configure '
+ QUOTENAME('xp_cmdshell', CHAR(39))
+ ', 1; RECONFIGURE WITH OVERRIDE;'
, @xpcmdshellflag = 1
BEGIN TRY
PRINT '/*'
PRINT '-- Temporarily enabling command shell'
EXEC sp_executesql @exec_str
PRINT '*/'
END TRY
BEGIN CATCH
PRINT 'Bad query: ' + @exec_str
END CATCH
END
-- Disable xp_cmdshell if previously disabled (old backup file deletion)
IF @xpcmdshellflag = 1
BEGIN
SELECT @exec_str = 'EXEC sp_configure '
+ QUOTENAME('allow updates', CHAR(39))
+ ', 1; RECONFIGURE WITH OVERRIDE;'
+ ' EXEC sp_configure '
+ QUOTENAME('xp_cmdshell', CHAR(39))
+ ', 0; RECONFIGURE WITH OVERRIDE;'
, @xpcmdshellflag = 0
BEGIN TRY
PRINT '/*'
PRINT '-- Disabling command shell'
EXEC sp_executesql @exec_str
PRINT '*/'
END TRY
BEGIN CATCH
PRINT 'Bad query: ' + @exec_str
END CATCH
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply