Create a scheduled task to email from SQL

  • Hi,

    is it possible to get SQL to email information that it has collected from a query as an attachment in csv format?

    I am able to email the information in question using an SQL query however I only know how to put it into the body of the email and not as a csv attachment. Please could someone help me?

    Arif

  • Lookup xp_sendmail in BOL. Set @query, @attach_results, and @separator parameters.

    OR

    If you are using 2000 and have Reporting Services you could setup a subscription in RS.

  • Thanks for this,

    I have used the query below:

    EXEC master.dbo.xp_sendmail

    @recipients = N'name@domain.com',

    @query = N'EXEC ComSys..RPT_3_DailyEmail ''17-oct-2008''',

    @subject = N'SQL Server Report',

    @message = N'Test',

    @attach_results = 'TRUE',

    @width = 250 ;

    and it throws back an error of:

    Server: Msg 18030, Level 16, State 1, Line 0

    xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

    I have since installed outlook on the SQL server and set microsoft outlook to the default mail client but it still displays the error above.

    What else have I missed?

    Thanks again

  • Outlook has to be installed using the SQL Server service account and have a profile for that account. So you need a domain account for SQL Server to send this email.

  • Fair warning: Installing Outlook on a Server is no small feat. It takes some work.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks for the help,

    I have managed to install an outlook client on the server and restarted the services and the above code works however it attachs the query as a .txt file, is there a way for me to make it send as a csv?

  • So you can control the name of the attachment, use the @attachments parameter for xp_sendmail.

    I suggest you also review the online help for the @separator parameter since you mention you would be using CSV extension.

    ------

    Robert

  • Thanks to everyone for their help, all working now.

    😀

  • Glad it worked out for you.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • And lastly.. i promise

    is there a way to make the email being sent using the guidelines above in HTML format? and if so how?

    Thanking you!!!!!

  • Log in to the SQL Server's Outlook client & mailbox yourself on the Server and try to set your defaults that way.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • You can send emails without outlook just with cdosys. Take a look at How to send e-mail without using SQL Mail in SQL Server http://support.microsoft.com/kb/312839/en-us

    Regards Ramon

  • Yeah I think i will stick to the xp_sendmail function for now as it seems to be a bit quicker and easier to use. Will defo read up on it tho.

    Cheers!!

    With the code below

    EXEC master.dbo.xp_sendmail

    @recipients = N'',

    @query = N'EXEC RPT_3_Daily ''21-oct-2008''',

    @subject = N'UK1428',

    @message = N'Please find new information attached.',

    @attach_results = 'TRUE',

    @width = 250,

    @separator = ',',

    @no_header = 'TRUE',

    @attachments = 'New.csv'

    the attachment I receive doesn't sit in the columns properly, I have opened the file up using notepad++ which displays a lot of "NULL" values when the stored procedure to be run contains characters as well as numerics in the results, however if I use the same code as above and use a stored procedure that will only produce numbers then the csv displays fine and even opens in excel, is there something else that I have missed off to allow the file produced display and organise the alphanumeric characters correctly within excel?

  • This is probably caused by commas in your string-based columns. You will need to put quotation marks around the columns that might have commas embedded in their text. Alternatively, use a different separator character (like TAB) that you are sure will not be in any of the columns.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi,

    I want to send mail's from sql ,

    eg: Consider table as Table1 , with 1 column as modifiedon, When the modifiedon date is greated than 2 days of last modified date than mail has to be automatically sent to users.

    In google i read this can be done in SQLMAil,

    Can you please give me details steps to do this.

    Thanks in Advance,

    Archana

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply