Write to File

  • LadyRuna

    SSCrazy

    Points: 2174

    Comments posted to this topic are about the item Write to File

  • LadyRuna

    SSCrazy

    Points: 2174

    You can also modify the script to loop through rows in a temp table (selected as a single long concatenated string into the @Text variable) and repeatedly call the "writeLine" command ( [font="Courier New"]EXECUTE sp_OAMethod @FileID,'WriteLine',NULL,@Text[/font] ) to generate a formatted file that matches any output file requirements you need.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33283

    Nice Script but sp_OAMethod scripts are old com+ object types. Do you have any other latest scripting way to do the same?

    Thanks.

  • mryan1001

    SSC Journeyman

    Points: 99

    Here is a procedure that I wrote to encapsulate the error handling.

    The test example is embedded in the comments

    As with all software, It is a work in progress.

    CREATE PROCEDURE [Tools].[TextFile] (

    @Action SYSNAME = NULL, -- Open, Write, Close

    @FileHandle XML = NULL OUTPUT,

    @Buffer VARCHAR(MAX) = NULL

    ) AS

    SET NOCOUNT ON;

    /*

    ======================================================================================================================================

    Author: MRyan

    Created: 05/07/2016

    Modified: 05/07/2016

    Description: Write a file to the OS

    Testing:

    DECLARE @Filename VARCHAR(64) = 'C:\SQL\Scripts\Test',

    @XFile XML = NULL;

    EXECUTE Tools.TextFile [Open], @XFile OUTPUT, @Filename;

    ---

    EXECUTE Tools.TextFile [Write], @XFile , '1One';

    EXECUTE Tools.TextFile [Write], @XFile , '2Two';

    EXECUTE Tools.TextFile [Write], @XFile , '3Three';

    ---

    EXECUTE Tools.TextFile [Close], @XFile ;

    --------------------------------------------------------------------------------------------------------------------------------------

    EXECUTE ('EXEC sp_configure ''show advanced options'', 1; RECONFIGURE WITH OVERRIDE;');

    EXECUTE ('EXEC sp_configure ''Ole Automation Procedures'',1; RECONFIGURE WITH OVERRIDE;');

    EXECUTE ('EXEC sp_configure ''show advanced options'', 0; RECONFIGURE WITH OVERRIDE;');

    ======================================================================================================================================

    */

    DECLARE

    @FSO INT,

    @RetVal INT,

    @FID INT,

    @Path SYSNAME = NULL,

    @Name VARCHAR(255) = NULL;

    ----------------------------------------------------------------------

    IF @Action = 'Open' BEGIN

    SELECT @Name = LEFT(@Buffer,255);

    ----------------------------------------------------------------------

    SELECT @Path = LEFT(@Name, LEN(@Name) - CHARINDEX('\',REVERSE(@Name)));

    EXECUTE @RetVal = xp_create_subdir @Path;

    IF @RetVal <> 0 RAISERROR('xp_create_subdir: Error Creating Path [%s]', 18, 1, @Path) WITH NOWAIT;

    ----------------------------------------------------------------------

    -- create fso object

    EXECUTE @RetVal = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT;

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Creating Object', 18, 1) WITH NOWAIT;

    ----------------------------------------------------------------------

    -- Open a file

    EXECUTE @RetVal = sp_OAMethod @FSO, 'OpenTextFile', @FID OUT, @Name, 2, 1; -- 2 write, 8 append

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Opening File [s%]', 18, 1, @Name) WITH NOWAIT;

    ----------------------------------------------------------------------

    SELECT @FileHandle = (SELECT @Name AS [@Name], @FSO AS [@FSO], @FID AS [@FID] FOR XML PATH('File'));

    ----

    END ELSE BEGIN

    SELECT

    @Name = X.C.value('@Name', 'VARCHAR(255)'),

    @FSO = X.C.value('@FSO', 'INT'),

    @FID = X.C.value('@FID', 'INT')

    FROM @FileHandle.nodes('/File') AS X(C);

    END;

    --======================================================================================================

    IF @Action = 'Write' BEGIN

    IF @Buffer IS NOT NULL BEGIN

    ----------------------------------------------------------------------

    -- Write

    EXECUTE @RetVal = sp_OAMethod @FID, 'WriteLine', Null, @Buffer;

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Writing to File [s%]', 18, 1, @Buffer) WITH NOWAIT;

    END;

    END ELSE IF @Action = 'Close' BEGIN

    ----------------------------------------------------------------------

    -- close file

    EXECUTE @RetVal = sp_OAMethod @FID, 'Close';

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Closing to File [s%]->[%i]', 18, 1, @Name, @FID) WITH NOWAIT;

    ----------------------------------------------------------------------

    EXECUTE @RetVal = sp_OADestroy @FID;

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Destroying File Handle [%i]', 18, 1, @FID) WITH NOWAIT;

    --

    EXECUTE @RetVal = sp_OADestroy @FSO;http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

    IF @RetVal <> 0 RAISERROR('Scripting.FileSystemObject: Error Destroying Object [%i]', 18, 1, @FSO) WITH NOWAIT;

    END;

    --------------------------------------------------------------------------------------------------------------------------------------

    RETURN(0);

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply