How to write to a text file using a stored procedure.

[arnierudy]

Is it possible to open a disc file for read/write using a SQL script in a stored procedure? For example, I would like to open a cursor, format each record and then write it out to a csv file. I know how to format the record but I can't figure out how to open the csv file for output.
Thanks in advance.

 

[organicg]

I've never done this, but there's a sample in ASP.NET Unleashed that does write to a file from a trigger, maybe it will help you.

"SQL Server includes an extended sp_ named xp_cmdshell that executes a string as an operating system command. You can use this sp_ to write a file to a directory."

This is a 'Cache Dependency' example. The dependency is to the file, and the file is triggered from a DB change, hence cirumventing the fact that you can't directly tie a cache dependency to a DB table change.

Here's that trigger:

CREATE TRIGGER UpdateCache
ON Products
FOR UPDATE, DELETE, INSERT
AS
DECLARE @cmd varchar(200)
SELECT @cmd = 'echo' + Cast(getDATE() as Varchar(50)) +
' > c:\tableChange.txt'
EXEC master..xp_cmdshell @cmd, no_output

 

[mrdenny]

In order to write out from a SQL proc to a text file you will need to call the proc from osql or isql and redirect the output to a text file. Or run the proc from a job, and configure the job to redirect the output from the job step to a text file.

BTW the sample provided from ASP.Net Unleashed is a horrible peice of sample code. You would never want to put xp_CMDShell in a trigger. There are tons of security issues with doing this. Most of which involve the fact that the account that your web based app is using has access to the command shell of your SQL Server. And using a text injecting attack, a user could do some nasty things to your server.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[nigelrivett]

Cursor - yuk.
Here are a few methods

http://www.nigelrivett.net/WriteTextFile.html

If you are going to use a cursor you may as well use a client app like VB, dts, .net...

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[arnierudy]

Lots of examples here. Thanks to all of you for your help!