How do you write to a text file from a stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do you write to a text file from a stored procedure?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-07 : 08:22:56

Donita writes "We are trying to write a stored procedure that will run database maintenance and write the results to a text file. What is the command to write a text file from inside of a stored proc?"

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 08:43:43

You could use isql from a batch file

isql
[-?] |
[-L] |
[
{
{-U login_id [-P password]}
| –E
}
[-S server_name] [-H wksta_name] [-d db_name]
[-l time_out] [-t time_out] [-h headers]
[-s col_separator] [-w column_width] [-a packet_size]
[-e] [-x max_text_size]
[-c cmd_end] [-q "query"] [-Q "query"]
[-n] [-m error_level] [-r {0 | 1}]
[-i input_file] [-o output_file] [-p]
[-b] [-O]
]

The input file is the sqlquery.sql and the output file is the output to text file.

If you want to run it from a stored procedure
you need to put the isql statement into a batch file
and run that batch file from the master database using xp_cmdshell('thebatchfile.cmd')

You can also use a DTS task and send the result to a text file of your choice, but not ideal to run from within a stored procedure.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 09:23:14

I think there is a better way.

You can use the sp_OA* set of system stored procedures to instantiate a filesystemsobject and then stream data to a file.

<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-07 : 10:48:40

Heres a quick example. Please forgive the GOTO's - its just an example
If you incorporate it in a sproc just use begin..end blocks and return

HTH
Jasper Smith


DECLARE @fso		int
DECLARE @file		int
DECLARE @filename	varchar(255) ; SET @filename='c:\test.txt'
DECLARE @text		varchar(255) ; SET @text='This is some text from SQL'
DECLARE @hr		int
DECLARE @src 		varchar(255)
DECLARE @desc 		varchar(255)

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 GOTO ERROR

EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @filename,1
IF @hr <> 0 GOTO ERROR

EXEC @hr=sp_OAMethod @file,'WriteLine',NULL,@text
IF @hr <> 0 GOTO ERROR

EXEC @hr=sp_OADestroy @file
IF @hr <> 0 GOTO ERROR

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 GOTO ERROR

GOTO DONE

ERROR:
EXEC sp_OAGetErrorInfo @fso
SELECT hr = convert(varbinary(4),@hr), Source = @src, Description = @desc

DONE:
PRINT 'File  written'

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-07 : 12:11:12

Another possiblity... Something I've done in the past (I began coming to this site for help, I came up with some interesting solutions)

Write the results of your stored procedure to a global temp table... You then have the storedprocedure execute a dts package that moves the data from the temp table to a text file (I forget how exactly the syntax went to do this... I could look it up if you need)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources