Monday, December 19, 2005

SQLServer2000: Capture output of EXEC statements

About 2 years ago, I discovered that I could capture the results of an EXEC (@string_variable) statement. We had a server process that logged in as System Admin (SA), so it could perform a directory scan, bulk insert any files it found, and move those files to a "processed" subdirectory.

This process can be used to run any DOS command, and inserts each line of text as a record into a table. We still use this trick to log program responses to a journal table. When reading the sample code, notice that we are using two single-quote characters back-to-back to cause a single quoted argument to be sent to the xp_cmdshell extended command (really a registered DLL accessible only to SA).

Here's an example:

DECLARE @s varchar(255);
CREATE TABLE #cmdOutput(Line varchar(200)) ;
SET @s='INSERT INTO #cmdOutput EXEC master.dbo.xp_cmdshell ''dir c:\temp\*.txt /b''';
EXEC (@s) ;
DROP TABLE #cmdOutput;

A word of caution: Do not use this code in front-line production. It is best suited to maintenance tasks, where the system admin password does not need to be shared with half of the office. If you need to convert this to front-line production code, write a simple application to read the directory, and either parse lines and call a stored procedure for each line, or call a bulk insert for each file (this still requires special permission, but it doesn't require SA).


Post a Comment

Subscribe to Post Comments [Atom]

<< Home