HowTo: Create a Text File with SQL Server 2005

I recently had the need to create a text file from within SQL Server. I have never heard of anyone doing this before, but since I started developing with SQL Server, it keeps amazing me on its capabilities. So when I decided this is what needs to happened, I put in the research to figure out how to do it.

It is actually much easier than anticipated. The first thing we had to do was to enable the xp_cmdshell stored procedure by executing the following (courtesy of Microsoft):

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Once we had enabled xp_cmdshell, we could utilize the built in stored procedure that allows you to perform external processing. Basically you are performing a Windows command to create / append a text file. The syntax to do this is:

xp_cmdshell { 'command_string' } [ , no_output ]

I created a stored procedure to use when writing to a text file:

create procedure dbo.writeToFile
@msg VARCHAR(7800) = null, @file VARCHAR(200) = null, @overwrite BIT = 0
AS
BEGIN
DECLARE @execStr VARCHAR(8000)

SET @execStr = RTRIM('echo ' + COALESCE(LTRIM(@msg),'-') + CASE WHEN (@overwrite = 1) THEN ' > ' ELSE ' >> ' END + RTRIM(@file))

EXEC master..xp_cmdshell @execStr
END
go