Stored procedure to CSV file - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Stored procedure to CSV file

Author

Topic

Guestuser18
Starting Member

34 Posts

Posted - 2009-08-26 : 16:22:54

Hi

I need to create a sp which outputs the result in a csv file. Does anyone know any good articles or tips on this?

Thanks in advance.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-26 : 16:30:16

You can use BCP or SQLCMD to output the results of a stored procedure to a file, e.g.
BCP "exec dbo.my_procedure" QUERYOUT "C:\File.csv" -T -c -t","

Google BCP or SQLCMD. Or look it up in BOL.
You can also use SSIS.

Guestuser18
Starting Member

34 Posts

Posted - 2009-08-26 : 16:38:09

But is there a way to specify how the file is to be formated.
For example if oracle you can specify what order they appear in the file.
Does sqlcmd create the csv file on the fly yeah?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-26 : 17:13:15

This explains all:
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

Guestuser18
Starting Member

34 Posts

Posted - 2009-08-27 : 04:19:31

Hi

I have been recommeneded this article several times however it doesnt seem to help me. I need to specify in a sp the format of the text file.
I have the following code which doesnt work:

AS
BEGIN
select field1,field2 from table;
SET NOCOUNT ON;

declare @sql varchar(8000)
select @sql = 'bcp "exec Textfile "
queryout C:\result.txt -c -t, -T -S'
+ @@servername

exec master..xp_cmdshell @sql
END
GO

Guestuser18
Starting Member

34 Posts

Posted - 2009-08-27 : 05:25:28

OK
I have managed to get it working now.
However I need to have two sql statements in my procedure to put into the textfiles.
It seems that only the first 1 is executed in the statement.
Is it possible for 2 to be executed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources