Tuesday, January 29, 2013

Stored procedure for exporting query results to text file

First you need to enable xp_cmdshell( I realize it can be a security issue so I always enable it on demand, do what I need to do and disable it immediately)


sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'xp_cmdshell', 1
reconfigure
go

And now the procedure -

CREATE PROCEDURE dbo.ExportText
AS
BEGIN
DECLARE @bcp AS VARCHAR (8000)
DECLARE @status AS INT

PRINT ''
PRINT 'Lets generate the text file'

SELECT @bcp = 'bcp "SELECT * FROM master..sysdatabases" QUERYOUT "D:\output.txt" -T -c'

EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
IF @Status <> 0
BEGIN
 PRINT 'Something went wrong. No text file for you!'
 RETURN
END ELSE
 PRINT 'Successful! Go check your text'
END