Home Forums SQL Server 2005 SQL Server 2005 Integration Services How can automate report from sqlserver to be sent on email everyday. RE: How can automate report from sqlserver to be sent on email everyday.

  • June 20, 2013 at 9:21 am

    #1625971

    tnnelzo (6/20/2013)

    Thank you for your help...however i want to send this report in excel ..is there any way i can do that? thanks you so much.

    well, it depends.

    have you set up Database mail? is it working?

    That's a prerequisite to everything you are asking.

    As Far as Excel you can create a CSV file, which opens by default in Excel very easily with sp_sendDBMail.

    If the Excel needs custom formatting, headers, bold, etc, then no, not easily. it's much more involved. Automating Excel, and opening an existing template is required; lot easier to do html than anything else.

    a basic example for a CSV File:

    declare @body1 varchar(4000)

    set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

    ' '

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='MyGmailProfileName',

    @recipients='lowell@somedomain.com',

    @subject = 'SQl 2008 email test',

    @body = @body1,

    @body_format = 'HTML',

    @query = 'SELECT top 3 * from sysobjects where xtype=''U''',

    @query_result_header = 1,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'qry.csv',

    @query_result_no_padding = 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!