OLE Automation : Write a text file from TSQL

Create a text file on the file system from TSQL via OLE.

Usage :

DECLARE @destinationpath NVARCHAR(1000)
DECLARE @destinationfilename NVARCHAR(1000)
SET @destinationpath = N'C:\SQL Scripts' 
SET @destinationfilename = @destinationpath + '\' + N'filename.txt'

exec utils.usp_OLEwritefile  @FileName =@destinationfilename
, @TextData ='blah! blah! blah!'
, @FileAction = 'APPEND' -- CREATENEW or APPEND (to allow appends if the file exists, both options create the file if it doesn't).

Proc :

CREATE PROCEDURE [Utils].[usp_OLEWriteFile] (@FileName varchar(1000), @TextData NVARCHAR(MAX),@FileAction VARCHAR(12)) AS

BEGIN
DECLARE @OLEfilesytemobject INT
DECLARE @OLEResult INT
DECLARE @FileID INT

EXECUTE @OLEResult = 
sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF @OLEResult <> 0 
PRINT 'Error: Scripting.FileSystemObject'

-- check if file exists
EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @FileName 
-- if file esists
IF (@OLEresult=1 AND @FileAction = 'APPEND') OR (@OLEresult=0) 
BEGIN   

IF (@FileAction = 'CREATENEW')
PRINT 'New file specified, creating...'
IF (@OLEresult=1 AND @FileAction = 'APPEND') 
PRINT 'File exists, appending...'
IF (@OLEresult=0 AND @FileAction = 'APPEND') 
PRINT 'File doesnt exist, creating...' 

-- open file
EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT 'Error: OpenTextFile'

-- write Text1 to the file
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextData
IF @OLEResult <> 0 
PRINT 'Error : WriteLine'
ELSE
PRINT 'Success' 
END
IF (@OLEresult=1 AND @FileAction = 'CREATENEW')
PRINT 'File Exists, specify APPEND if this is the desired action'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject

END
GO

Note : Remember to enable OLE Automation for this to work.
Also bear in mind that the service account SQL is running under will need the correct permissions to be able to write a file in the specified location.
See also Creating a file folder from TSQL