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,131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 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