How to store output of a Script in a .csv file in sql 2005?

Question

How to store output of a Script in a .csv file in sql 2005?

rahul.soni2

Grasshopper

Points: 20
More actions
July 5, 2009 at 2:27 am

#384915

Hi All, I have a script that generates Server job details on execution.
I need to store the data in .csv file.
I need to send the Output file in a .csv format to the client and that too via automation....
can anyone plz tell me how to do that?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Animal Magic SSChampion Points: 13964 More actions · Answer 1

use SSIS.

use a data flow task to output your query results to file. use a send mail task to attach the file (the csv you just made) to the mail and send it to whoever.

Its fairly easy to do if you always output to the same filename with overwrite, if you wanted to keep a copy for archive then you could start using variables to change the name of the output file and then use those variables in sql expressions on the mail task.

WayneS SSC Guru Points: 95460 More actions · Answer 2

rahul.soni2 (7/5/2009)
Hi All, I have a script that generates Server job details on execution.
I need to store the data in .csv file.
I need to send the Output file in a .csv format to the client and that too via automation....
can anyone plz tell me how to do that?

You should be able to use the OpenRowset function. You may need to test different providers...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Gosh... I just use good ol' BCP for this type of thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)