How to print to a specified text file?

Question

How to print to a specified text file?

MarkThornton

SSCommitted

Points: 1731
More actions
March 26, 2008 at 10:39 am

#180419

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?

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

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

Srinivas Chilakapati SSC Enthusiast Points: 123 More actions · Answer 1

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

Bob Pinella SSCommitted Points: 1737 More actions · Answer 2

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