Is is possible for a Stored Procedure to write to a text file?

[orbitkgf]

I have an asp page that is firing off a Transact-SQL stored procedure that selects rows, etc. What I need for it to do is write to a CSV file. Can I code this in the stored procedure? Can't seem to find anything for this....

Please help : [sig][/sig]

 

[foxdev]

You might be able to create a DTS package that writes a CSV file, then invoke that package from within a SP. I'm certain you can do the first part, but I'm not sure if you can call DTSRUN from within a SP. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[ColinM]

You should be able to use xp_cmdshell to execute dtsrun [sig][/sig]

 

[jnicho02]

...or similarly you could use xp_cmdshell to call bcp [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[Deadline]

Couldn't help noticing the date of the previous two posts. Sept 11,2000 :-(

Hi,
I'm trying to use the following code to create a TextFile. But it won't work.

Could you please tell me, where the mistake is ?

CREATE PROCEDURE [dbo].[sp_CreateTextFile] 
@FileName varchar(100),
@FileContent varchar(8000)

AS
Declare @FSO int
Declare @TextFile int
Declare @hr int


EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
EXEC @hr = sp_OAMethod @FSO, 'CreateTextFile', @FileName,NULL,  @TextFile OUT
EXEC @hr = sp_OAMethod @TextFile, 'WriteLine', @fileContent, NULL
EXEC @hr = sp_OADestroy @FSO
GO

Thank you,
RR.
__________________________________
In my life, what should not happen, usually happens in the worst possible moment.

 

[tlbroadbent]

Deadline,

Please start a new thread with your question. When you do, provide more info. Which version of SQL Server? What happens when you execute the procedure? Do you get an error, invalid result or what?

Thanks, Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.