Post reply

How to extract results of a query into a text file (equivalent to Oracle's spool command)

  • June 23, 2011 at 10:16 am

    #252415

    We have an Oracle script that fixes data, it writes time started and time ended plus other messages regarding what records were updated to a log file. It uses the Oracle Spool command. We would like to do the same thing in SQL Server. How can we write to a text file from a SQL Server Script?

  • June 23, 2011 at 11:23 am

    #1343263

    I just looked up Oracle's SPOOL and I don't know of anything equivalent in native T-SQL. You could write or borrow a SQLCLR function that can write to a text file, or you could use one of the extended stored procedures bundled with SQL Server, but I would not recommend either of those methods because they break the SQL Server security domain from within a T-SQL routine which introduces a slew of security and configuration concerns.

    Here is one option for you to consider:

    1) Port your Oracle script as a SQL Server stored procedure.

    2) Add RAISERROR statements throughout your stored procedure with a severity of 10 and a state of 1 to output messages to the information stream. Like this:

    DECLARE @dt VARCHAR(25) ;

    SET @dt = CONVERT(VARCHAR(25), GETDATE(), 121) ;

    RAISERROR('%s: Some stuff is about to happen...',10,1,@dt) WITH NOWAIT ;

    -- ... stuff is happening here

    SET @dt = CONVERT(VARCHAR(25), GETDATE(), 121) ;

    RAISERROR('%s: Some stuff just happened',10,1,@dt) WITH NOWAIT ;

    Note: output from PRINT statements are also sent to the information stream and would be visible inline with RAISERROR output however I like the C-style argument substitutions RAISERROR offers.

    3) Execute the stored procedure using SqlCmd and pipe the output to a text file.

    -or-

    Execute the stored procedure using SQL Agent and configure the job step to capture all output to a file or table.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 24, 2011 at 7:12 am

    #1343640

    Thank you.

  • June 24, 2011 at 7:53 am

    #1343660

    You're very welcome 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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