export data from sql server (from stored procedure) in to an excel sheet and email that excel sheet every week

Posted on 2006-05-11
Medium Priority
Last Modified: 2010-08-05
Hi Experts,

I have some data from my database which is to be exported to an excel sheet and email it to couple of people every week.

I dont know how to start it.
Can i do it in the sql server itself?

Could somebody show me a right way?

Question by:Raju Srivatsavaye
  • 2
LVL 20

Expert Comment

ID: 16661957
You can setup a DTS Package
LVL 20

Accepted Solution

Sirees earned 1200 total points
ID: 16661982
Check this link


You can create an activex script to email the excel spread sheet to recipients.

Assisted Solution

itdrms earned 300 total points
ID: 16667549
If the data lends itself to opening in Excel from a comma delimited file, .csv file extenstions look like Excel spreadsheets to the PC, you can run this job step and schedule it.

DECLARE @QRY nvarchar(500)
DECLARE @cmdTXT varchar(1000)
set @QRY = 'SELECT * from tblx'
set @cmdTxt = 'bcp "' + @QRY +
                    '" queryout \\fileserver\data\filename.csv -T -c -t"," -S serverA\instanceA'
exec master..xp_cmdShell @cmdTxt, NO_OUTPUT

EXEC master..xp_sendmail @recipients = 'user@company.org',
    @subject = 'Weekly extract',
    @message = 'The weekly extract is attached',
    @attachments = '\\fileserver\data\filename.csv'

Author Comment

by:Raju Srivatsavaye
ID: 16668167

When i run the package it runs fine with out any errors. It updates the excel sheet with the data but it never sends the email.

I got this vbscript for emailing from the internet

Function Main()
Dim iMsg

set iMsg = CreateObject("CDO.Message")

Dim objMail      
Set objMail = CreateObject("CDO.Message")

objMail.From = "srivatsavaye@gmail.com"
objMail.To = "srraju15@yahoo.co.in"
objMail.AddAttachment ( "C:\Inetpub\wwwroot\Book1.xls")
objMail.Subject="Authors Spreadsheet"
objMail.TextBody = "Spreadsheet"
Set objMail = nothing

Main = DTSTaskExecResult_Success
End Function

Could you please help me

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

569 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