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

• judy.strong 24969

  Old Hand

  Points: 314

  More actions

  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?

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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

• judy.strong 24969

  Old Hand

  Points: 314

  More actions

  June 24, 2011 at 7:12 am

  #1343640

  Thank you.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  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