SQL Server - How to Write to a Text File from SQL Server

Source, big thanks to author.

It covers some tips and tricks but also goes over how you must turn on the permissions in SQL Server for this to work. You must be an admin.


Here is the code to turn on permissions for Ole Automation:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Note: You will need to run this code as an administrator or else SQL Server will return an error.

This next block of code is the Stored Procedure that I created to encapsulate the specific code that does the actual writing to the flat file or text file. Feel free to steal the code, but please have some dignity and maybe change like the name of it at least, so you don't look like a total bum. (Partial bum-ness beats total bum-ness.)

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[WriteToFile]

 

@File        VARCHAR(2000),

@Text        VARCHAR(2000)

 

AS 

 

BEGIN 

 

DECLARE @OLE            INT 

DECLARE @FileID         INT 

 

 

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 

       

EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1 

     

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text

 

EXECUTE sp_OADestroy @FileID 

EXECUTE sp_OADestroy @OLE 

 

END 

 

These are some real world examples of how you could call the code and see it work.

1. The first one is just a straightforward and simple EXEC statement with no bells and whistles:

EXEC WriteToFile 'C:\Users\Mertman\Documents\ACME\Log Files\Log.txt','Did it work?'

2. The second one is breaks out the arguments (parameters) into variables and then sets them right before the EXEC statement. This makes reading the code a little easier on the eyes:

 

DECLARE @Path  VARCHAR(2000)

DECLARE @Txt  VARCHAR(2000)

 

SET @Path = 'C:\Users\Mertman\Documents\ACME\Log Files\Log.txt'

SET @Txt = 'Did it work the next time?'

 

EXEC WriteToFile @Path, @Txt

 

3. The last example goes all out and adds a datestamp to the file name. This is in cases where you may not want to be writing to the same log file day after day as it will get enormous, so instead you create a new one each day:

 

DECLARE @Path       VARCHAR(2000)

DECLARE @Txt        VARCHAR(2000)

 

DECLARE @YYYY       INT

DECLARE @MM         INT

DECLARE @DD         INT

 

DECLARE @YYYY_Txt  VARCHAR(2000)

DECLARE @MM_Txt    VARCHAR(2000)

DECLARE @DD_Txt    VARCHAR(2000)

 

DECLARE @DateStamp VARCHAR(2000)

 

SET @YYYY = DATEPART(yy,GETDATE())

SET @YYYY_Txt = CAST(@YYYY AS VARCHAR(2000))

 

SET @MM = DATEPART(mm,GETDATE())

SET @MM_Txt = CAST(@MM AS VARCHAR(2000))

 

SET @DD = DATEPART(dd,GETDATE())

SET @DD_Txt = CAST(@DD AS VARCHAR(2000))

 

SET @DateStamp = @YYYY_Txt + '_' + @MM_Txt + '_' + @DD_Txt

 

SET @Path = 'C:\Users\Mertman\Documents\ACME\Log Files\Log_' + @DateStamp + '.txt'

SET @Txt = 'Did it work?'

 

 

EXEC WriteToFile @Path, @Txt

 

 

Try this code out for yourself and seehow easy it is!