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
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:srivatsavaye
    LVL 20

    Expert Comment

    You can setup a DTS Package
    LVL 20

    Accepted Solution

    Check this link

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

    Assisted Solution

    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 = '',
        @subject = 'Weekly extract',
        @message = 'The weekly extract is attached',
        @attachments = '\\fileserver\data\filename.csv'
    LVL 9

    Author Comment


    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 = ""
    objMail.To = ""
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now