Solved

How to Generate CSV file from SQL server tables and send it by email  to recipients in an automated process

Posted on 2010-11-11
3
1,121 Views
Last Modified: 2012-05-10
I have to generate a report using two tables in SQL server 2008 and send it in CSV format to recipients.
 I have already written the query(Thats Okay) . How can I use reporting services ,SSRS to do this automatically . That is send the CSV file to email automatically everyday.
or SSIS or what other process can I use to do this and automate the whole process to go everyday.

Pls can you outline the steps very well and processes I should use.if there are little code involved too, pls let me have it.

0
Comment
Question by:marvo2010
3 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 34109719
Use BCP
Create a folder and place the file there.
Use Database mail to send mail with attachment. Below the reference

http://blogs.msdn.com/b/sqlagent/archive/2010/11/10/sql-database-mail-send-emails-with-attachment.aspx

Use a batch file to achieve the above steps. And schedule the batch file if needed.

Note; Use SQLCMD.exe to run sql scripts inside batch file.
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 500 total points
ID: 34109738
Use SSIS and create a package to export to csv and email and schedule a job to run it daily

using SSIS to export to csv
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_24002244.html

using SSIS to send email
http://www.mssqltips.com/tip.asp?tip=1731
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34110039
Hi,

Export data using the "BCP" and send mail using db_sendmail SP.

else

best way to Create SSIS packages.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question