Save String Variable Contents to a File

Saves the contents of a string variable to a text file.

Download Chilkat FileAccess ActiveX (freeware)

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @fileText nvarchar(4000)

    SELECT @fileText = 'Hello World!'

    --  Saves a string to a text file.
    --  The 3rd arg specifies the character encoding used for
    --  the contents of the text file.
    --  The last arg only applies when saving text using the
    --  utf-8 or Unicode character encodings. If 1,
    --  the preamble is included (a.k.a. BOM).
    DECLARE @bIncludePreamble int

    SELECT @bIncludePreamble = 0
    DECLARE @success int

    EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'helloWorld.txt', @fileText, 'ansi', @bIncludePreamble
    IF @success = 1
      BEGIN
        PRINT 'Success!'
      END
    ELSE
      BEGIN
      END
END
GO