How to print to a specified text file?

  • I would like to print to a specified text file, e.g. "c:\Temp\tmp.txt", using T-SQL. However, I cannot find a suitable command to do this. Please can someone help me out?

  • Hi

    I think there is no direct command to print the data to a file from t-sql. you have an alternate approach which is little time consuming if you don't mind go ahead with the below steps and reply to me if you fail.

    1) Create a procedure to write the data to a file either .txt and .doc file using the below code and make sure you need to enable the cdmshell as well as OLEAutomation also from the Surface Configuration Area tool in 2005 or use sp_configure procedure to enable the cmdshell to tru and type reconfigure and run both at the same time.Here is the procedure which create a .txt file.

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

    CREATE Proc [dbo].[UCreateOrAppendTextFile](@Filename VarChar(100),@Text nVarchar(4000))

    AS

    DECLARE @FileSystem int

    DECLARE @FileHandle int

    DECLARE @RetCode int

    DECLARE @RetVal int

    DECLARE @CreateOrAppend int

    EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT

    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)

    RAISERROR ('could not create FileSystemObject',16,1)

    EXECUTE @RetCode = sp_OAMethod @FileSystem , 'FileExists', @RetVal out, @FileName

    IF (@@ERROR|@RetCode > 0)

    RAISERROR ('could not check file existence',16,1)

    -- If file exists then append else create

    SET @CreateOrAppend = case @RetVal when 1 then 8 else 2 end

    EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @Filename, @CreateOrAppend, 1

    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)

    RAISERROR ('could not create File',16,1)

    EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @text

    IF (@@ERROR|@RetCode > 0 )

    RAISERROR ('could not write to File',16,1)

    EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close'

    IF (@@ERROR|@RetCode > 0)

    RAISERROR ('Could not close file ',16,1)

    EXEC sp_OADestroy @filehandle

    IF (@@ERROR|@RetCode > 0)

    RAISERROR ('Could not destroy file object',16,1)

    EXEC sp_OADestroy @FileSystem

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

    2) run the procedure as below

    exec UCreateOrAppendTextFile 'd:\test.doc','hello'

    3) if you are using the sql 2005 then do som coding using CLR programming to create a procedure which reads the data from the file you created and prints. If you want the legacy COM technology look at the below code to print a file from the sql

    http://www.codeproject.com/KB/reporting-services/Print_from_SQL.aspx

    I think it will make some sense to you.

    Regards

    Srinivas Chilakapati

  • There are some additional options for writing to a text file from T-SQL that require less system resources and are quicker to set up. These options involve using the system stored procedure master.dbo.xp_cmdshell. This stored procedure allows you to run command prompt commands. SQL Server 2005 has this feature disabled by default. In order to enable this, an admin needs to run the following command:

    exec sp_configure 'xp_cmdshell', '1'

    Reconfigure

    Once this has been enabled you can write to a text file using several different methods:

    Method 1 - sqlcmd - This utility allows you to run a query and output to a new file or append to an existing file. You can even run a script file using or set variables using this utility. There are many different options available with sqlcmd which you can read about here:

    http://technet.microsoft.com/en-us/library/ms162773.aspx

    Example:

    exec master.dbo.xp_cmdshell 'sqlcmd -S YourServerName -Q "select * from master.dbo.sysobjects where abs(id) Test.txt & echo goodbye >> Test.txt'

    Bob Pinella

Viewing 3 posts - 1 through 2 (of 2 total)

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