@".NET Developer, ERP and
     Database Analyst"

SQL: Write to File

I’m not sure of the original creator of this script (Thank you whomever you are!), but over time I have tweaked it for my own use and use it quite heavily and without complaint.

CREATE PROCEDURE spWriteToFile
(
@String VARCHAR(max),
@Path VARCHAR(5000),
@Filename VARCHAR(500)
)
AS
DECLARE @objFileSystem INT,
@objTextStream INT,
@objErrorObject INT,
@Command VARCHAR(5000),
@hr INT,
@fileAndPath VARCHAR(800)

SET NOCOUNT ON

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT

SELECT @fileAndPath=@Path+'\'+@Filename
IF @hr=0 SELECT @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@fileAndPath+'"'
IF @hr=0 EXECUTE @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
, @objTextStream OUT, @fileAndPath,2,TRUE

IF @hr=0 SELECT @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@fileAndPath+'"'
IF @hr=0 EXECUTE @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

IF @hr=0 SELECT @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@fileAndPath+'"'
IF @hr=0 EXECUTE @hr = sp_OAMethod  @objTextStream, 'Close'

IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo  @objErrorObject,
@Source output,@Description output,@Helpfile output,@HelpID output
SELECT @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objFileSystem