DTS or BCP 1

[SQLBill]

I am running a stored procedure via a job. My end-user would like the output as a Comma Separated Value (CSV) report. Running this as a job doesn't produce that type of output (I can do it via Query Analyzer, but not via a job).

I have heard that BCP or DTS can do this and am reading up on both (never used either one). Which is the prefered/best method of running a job and having the output file be csv format - and not using Excel? Any suggestions?

-SQLBill

Posting advice: FAQ481-4875

 

[MDXer]

BCP is my preffered method.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[JayKusch]

Kinda depends Bill.

If it is a straight dump from table to file, BCP may be the less complicated way to go.

Now if you want to "massage" a feild or two from the table before it get to the file ... DTS as a scheduled job works great.

Get into DTS ... create a SQL Server SOURCE connection. The create a Text or Excel DESTINATION object.

The choose the Execute Transformation task and connect the source to the destination. Pop open the task and you have a bunch of "things" you can do w/ the data before, during and after the data pump is executed.



Thanks

J. Kusch

 

[SQLBill]

Thanks all. I guess the best thing for me to do is 'play' with both methods and see what suits me best.

It's not going to be a straight dump. My stored procedure pulls selected columns from two tables and also uses a function to convert two of the columns from integers to user-readable formats.

I already have the stored procedure running fine as a job. I'm just looking to improve it and get the output as a csv text file.

-SQLBill

Posting advice: FAQ481-4875

 

[JayKusch]

If it it is in an SP already, BCP can leverage it.

Thanks

J. Kusch

 

[JayKusch]

Here is a taste ...

bcp "exec sp_Myprocedure" queryout territories.txt -n -t, -S MyServer -U MyUser -P MyPass

Thanks

J. Kusch

 

[SQLBill]

Thanks MDXer and especially Jay. Jay, thanks for the code sample...I found something similar to that in BOL, but you added more to it.

Now I just have to 'play' with it and see what works best.

Thanks.

-SQLBill

Posting advice: FAQ481-4875