Urgent : Can I create/Open text file from Stored Procedure ?

[snr]

Hi,
I want to create a text file with a complicated format, so it will be very difficult by DTS. Can I open or create file from a stored procedure ? If yes , how ?

Please help.
Thanks

 

[ajc123]

You can use SQL Agent and create a Job. You can direct the output of each Job Step. This is done through Enterprise Manager.

If you want, you can manipulate the Job Step's OutputFileName programatically using C (or, perhaps VB.net).

 

[snr]

Thanks for the reply.
I want to schedule the job , and I don't want vb or any other exe to run on the server.
What I wnt to do is
-----
1) Open text file
2) pull out data from various tables by more than 1 queries
in loop.
3) write the data to file.
4) close the file

I can not divide the job in more that one step, as it uses the same recordset.
How can I do that ?

 

[SQLSister]

This is by far best to do in DTS. You may need to actually create a new package rather than using the wizard. I'd look at using the data driven query task or the execute SQL taks or a combination of both. By the way, you need to stop thinking of using looping when using SQL server. Much better to use a set-oriented insert or update statement than to create a loop and run through each record individually.

 

[wepisan]

Hi,

Here's the way to write to a file from stored proc:

Make sure your are having Service Pack 2 or higher installed.

CREATE PROCEDURE usp_WriteToFile(
@FileName varchar(255),
@Text1 varchar(255)
) AS

BEGIN

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file
@FS, 'OpenTextFile', @FileID OUT, @FileName, 2, True
IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

END
GO

Hope this helps.

Thanks,
Vaiyapuri Subramanian

 

[snr]

Thanks.
But it is giving me syntax error at
@FS, 'OpenTextFile', @FileID OUT, @FileName, 2, True

Please help

 

[clapag22]

I may simplifying the hell out of this, but for basic writing to a text file, this works great:


exec master..xp_cmdshell 'echo write line 1>filename.txt'
exec master..xp_cmdshell 'echo write line 2>>filename.txt'

I use this all the time to create FTP scripts on the fly so a file doesn't have to exist on the file server all the time with the FTP site username and password.

What you're doing may be too complicated for this, but using some sort of read-only cursor (heavens no!) and looping through your result set and writing this out line by line to a file may be of some use. Just remember, a single &quot;>&quot; means overwrite, a double &quot;>>&quot; means append.

 

[tlbroadbent]

I can't imagine a file format too complicated for DTS in conbination with SQL views and stored procedures. There is more than one way to write a text file using DTS. Exporting table to file isn't the only thing DTS does. Complex transformations are possible.

There are ways to output a text file from a view or stored procedure using BCP, OSQL or ISQL. Any of these methods would outperform a cursor based process that writes one line at a time to a text file. It's time to start using the power of SQL and stop thinking only of prodedural, step-by-step, looping methods of accomplishing tasks. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm