Write Query Results to a File

This pair of stored procedures writes query results to a text file from inside SQL Server, using the Scripting.FileSystemObject COM object. WriteTableToFile writes an existing table row by row with a configurable delimiter (TAB by default), building the column list via the GTC procedure so every value is safely converted and nulls become empty strings. WriteSQLToFile wraps it: run any SELECT as a string, capture it into a temp table, write that to the file, and clean up.

Example: EXEC WriteSQLToFile @Path = 'C:\Export\result.txt', @SQL = 'SELECT name, create_date FROM sys.tables'

CREATE PROCEDURE WriteSQLToFile
(
	@Path VarChar(255),
	@SQL nVarChar(4000),
	@Delimiter VarChar(10) = NULL --Will default to Char(9) (TAB)
) AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @tSQL nVarChar(4000)
	DECLARE @TempTable VarChar(40) SET @TempTable = '##t_' + Replace(Convert(VarChar(36), NewId()), '-', '_')

	SET @tSQL = 'SELECT * INTO ' + @TempTable + ' FROM (' + @SQL + ') AS extent'
	EXEC(@tSQL)

	EXEC WriteTableToFile
		@Path = @Path,
		@Schema = 'dbo',
		@Table = @TempTable,
		@Delimiter = @Delimiter

	SET @tSQL = 'DROP TABLE ' + @TempTable
	EXEC(@tSQL)
END
GO

ALTER PROCEDURE WriteTableToFile
(
	@Path nVarChar(512),
	@Schema nVarChar(4000) = null,
	@Table nVarChar(4000),
	@Delimiter nVarChar(10) = NULL --Will default to Char(9) (TAB)
) AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @objFileSystem	INT
	DECLARE @objTextStream	INT
	DECLARE @Result			INT
	DECLARE @Columns		nVarChar(4000)
	DECLARE @tSQL			nVarChar(4000)

	SET @Delimiter = '+''' + IsNull(@Delimiter, Char(9)) + '''+' --Default to TAB

	--Create the Scripting.FileSystemObject object.
	EXEC @Result = sp_OACreate  'Scripting.FileSystemObject', @objFileSystem OUT
	IF @Result <> 0
	BEGIN
		PRINT 'Failed to allocate the scripting object'
		RETURN
	END

	-- object.OpenTextFile(filename, iomode, create, format)
	-- imode: 1=ForReading, 2=ForWriting, 8=ForAppending
	-- create: True=create file if it doesn't exist, False=file isn't created
	EXEC @Result = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @Path, 2, true, 0
	IF @Result <> 0
	BEGIN
		PRINT 'Failed to open the specified file "' + @Path + '"'
		RETURN
	END

	SET @tSQL =  ' + ''' + Char(9) + ''' +'

	EXEC [GTC] @Table=@Table, @Schema=@Schema,
		@Format= 'IsNull(Convert(nVarchar(4000),<column>),'''') COLLATE SQL_Latin1_General_CP1_CI_AS',
		@PrintOutput = 0, @Delimiter=@Delimiter, @Output=@Columns OUT
		
	SET @tSQL =
		'DECLARE @TextData nVarChar(4000)' +
		' DECLARE CData CURSOR FAST_FORWARD FOR' +
			' SELECT ' + @Columns + ' AS RowText FROM [' + @Table + ']' +
		' OPEN CData' +

		' FETCH FROM CData INTO @TextData' +

		' WHILE(@@FETCH_STATUS = 0)' +
		' BEGIN' +
			' SET @TextData = @TextData + CHAR(13) + CHAR(10)' +
			' EXEC sp_OAMethod ' + Convert(VarChar, @objTextStream) + ', ''Write'', NULL, @TextData' +
			' FETCH FROM CData INTO @TextData' +
		' END' +

		' CLOSE CData DEALLOCATE CData'
	EXEC(@tSQL)

	--Close the file.
	EXEC @Result = sp_OAMethod @objTextStream, 'Close'
	IF @Result <> 0
	BEGIN
		PRINT 'Failed to close the file handle.'
		RETURN
	END

	--Destroy the created objects.
	EXEC sp_OADestroy @objTextStream
	EXEC sp_OADestroy @objFileSystem
END
GO