Monday, October 03, 2005

Capture output from SqlServer 2000

Here is a method of capturing the output of a command executed by the system shell. It is typically called from the SA account, and used to automate maintenance tasks. I do not recommend that you allow other users to send statements to a command shell.

DECLARE @s varchar(255);

-- our command
SET @s = 'DIR C:\TEMP\*.EXE' ;

-- the temporary table
CREATE TABLE #tCmdOutput( output varchar(1024) NULL ) ;

-- capturing output from the command
INSERT INTO #tCmdOutput( output )
EXEC Master..XP_CmdShell @s ;

-- delete blank lines
DELETE FROM #tCmdOutput WHERE output is null ;

-- show output as a result set
SELECT * FROM #tCmdOutput ;

-- clean up our session
DROP TABLE #tCmdOutput ;


Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home