export to csv using t-sql

Question

Post reply

export to csv using t-sql

Debbie Lethbridge

Grasshopper

Points: 13
More actions
June 6, 2005 at 8:47 pm

#66608

I would like to export some SQL Server table data into a csv file.
I found an article about using the xp_execresultset in conjunction with bcp. It also states that this is an undocumented extended procedure and it's not advisable to use it in a production environment.
Does anyone know of any other way to do this without having to use cursors?
Thanks in Advance
Deb

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

You can do this with the data export wizard in enterprise manager!

**ASCII stupid question, get a stupid ANSI !!!**

Debbie Lethbridge Grasshopper Points: 13 More actions · Answer 2

Thanks for you reply, but I want to be able to do it using a stored procedure so that it can be called from cold fusion.

Regards

Deb

Ben Fry-216381 Newbie Points: 3 More actions · Answer 3

Debbie,

Here is a TSQL code snippet which might help;

DECLARE @bcpResult INT

DECLARE @bcpCommand

NVARCHAR(4000)

SET @bcpCommand = 'bcp select * from Accounts queryout c:\temp\test.csv -U username -P password -c'

-- Export to CSV

EXEC @bcpResult = master..xp_cmdshell @bcpCommand

IF (@bcpResult = 0)

BEGIN

-- Do Cleanup here

END

In my stored proc I just replace the select statement in the bcp command with a variable and build up a dynamic sql statement to select the data I need to export.

Cheers,

Ben

Erich Brinker SSCrazy Points: 2973 More actions · Answer 4

I used something similar to the above code, but you may want to select the data into a table first so you could validate your result set.

steve goluchowski SSC Veteran Points: 280 More actions · Answer 5

I use xp_sendmail - here's an example that's TAB-delimited.

DECLARE @tab VARCHAR(10)

SET @tab = CHAR(9)

EXECUTE master.dbo.xp_sendmail

@recipients = 'me'

@subject = 'here is your data',

@width = 1024,

@attach_results = 'TRUE',

@attachments = 'vr_dups.csv',

@separator = @tab,

@query = 'select * from pubs.dbo.authors'

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

If you can call DTS package from coldfusion (We can do it with ASP) create a DTS Package and Execute the package.

Regards,
gova

Debbie Lethbridge Grasshopper Points: 13 More actions · Answer 7

Thankyou to all who replied to my post.

Have tested the bcp option and works like a charm

Thanks again

Deb