Post reply

sending email with SQL

  • January 22, 2007 at 10:57 pm

    #368875

    Hi there,

    I am no expert looking for advise if the following is posible and what should I look into in order to get it done.

    Can I set a job for every night that would look at a date field of all my records in a table and if today - DateFieldValue = 30 days then I would send a email to sales@myCompany.com ?

    If you have any links on the subject that would be great too.

    Thanks in advance.

    Ric

  • January 23, 2007 at 3:54 am

    #684871

    Hi Ric,

    There are various ways you could do this.  I'll provide one such way that will at least help you get started.

    First of all you'll need to set up a database mail account within SQL Server.  You can do this in a couple of ways but I suggest you look up database mail in books online as it's well documented there.

    I've included an example script at the bottom of this post that will set up database mail for you, assuming you have access to an smtp mail server.  You'll need to change various fields so that they are specific to your environment.  I haven't gone into detail with what the stored procs do as they're well documented in BOL.

    Now, once you've got database mail set up sending an email from within SQL Server is as easy as executing sp_send_dbmail procedure.  I've included an example below.

    Note that there are various different parameters for sp_send_dbmail so I suggest you review it's use in BOL.

    Regarding your particular scenario, you just need to set up the job and determine what criteria needs to be met for you to fire off the mail.  Perhaps something like:

    if

    exists(select 1 from mytable where datediff(day, DateFieldValue, getdate()) = 30)

    begin

    --send the mail

    EXEC

    msdb.dbo.sp_send_dbmail

    @profile_name

    = 'SQLMailProfile', --OR WHATEVER YOU CALLED THE PROFILE YOU CREATED

    @recipients

    = 'sales@myCompany.com',

    @body

    = 'a message.',

    @subject

    = 'a subject line'

    end

    This is obviously just a guess as to what you might need but hopefully it gives you an idea.

    Anyway, here's an example of how you'd set up a db mail account and profile.

    Hope this helps to get you started.........

     

    EXECUTE

    msdb.dbo.sysmail_add_account_sp

    @account_name

    = 'SQLMailAccount',

    @description

    = 'SQL Mail Account,

    @email_address

    = 'someone@somewhere.com', --**CHANGE

    @display_name

    = 'SQLMailAccount',

    @mailserver_name

    = 'smpt mail server' --**CHANGE

    go

    EXECUTE

    msdb.dbo.sysmail_add_profile_sp

    @profile_name

    = 'SQLMailProfile',

    @description

    = 'Profile used for sending mail.'

    go
     

    EXECUTE

    msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name

    = 'SQLMailProfile',

    @account_name

    = 'SQLMailAccount',

    @sequence_number

    = 1

    go

    Karl
    source control for SQL Server

  • January 26, 2007 at 10:38 am

    #685901

    Thanks for your reply this was very helpful information and got me going on the right track for sure.  However I seem to be stuck.  I set my Database Mail according to your information and as well the ones found here but when I try to send test emails I receive nothing at the other end.  So my question is how do I troubleshoot this or what should I be looking at in order to find my problem?  Not sure if the problem could be related to that but I am using SQL Sever Develloper Edition.

    Thanks again,

    Ric

    Send test email

  • January 26, 2007 at 12:56 pm

    #685942

    Check the database mail log for any errors..

    Right click Database Mail/View database Mail log...

     

    MohammedU
    Microsoft SQL Server MVP

  • January 26, 2007 at 1:37 pm

    #685956

    The logs I shows:

    8h15 AM "DatabaseMail process is started".

    8h25  AM "DatabaseMail process is shutting down".

    I see no other kind of message.

    Ric

  • January 26, 2007 at 1:48 pm

    #685960

    Are you using right email address?

    SQL Server 2005 Books Online 
    Troubleshooting Database Mail: Mail queued, not delivered 

    http://msdn2.microsoft.com/en-us/library/ms190630.aspx

     

    MohammedU
    Microsoft SQL Server MVP

  • January 29, 2007 at 9:26 am

    #686190

    To answer your last question, yes my email address used in my profile is fully fonctionnal. 

    After sending a test email I looked at my SMTP Mail Server Logs and I saw no entry of my test email.  Also I checked my SQL Database Mail Logs and my email status was mark as sent.

    So what steps would you recommend I do from here?

    Thanks

  • January 30, 2007 at 1:19 pm

    #686590

    how many mail servers in the system? you might have to allow relaying.

  • January 30, 2007 at 1:33 pm

    #686596

    We only have one mail server and it is located on a different machine so in my profile the server name is something like 10.10.1.3

     

  • October 30, 2007 at 9:55 am

    #746721

    Did you ever get it running? I have a similar problem getting Database Mail going on SQL 2005.

  • October 30, 2007 at 10:15 am

    #746737

    What sort of problems are you having? The very first step I recommend in troubleshooting any SMTP related issues with database mail is to first confirm you can send mail from the MSSQL host via telnet - i.e. http://support.microsoft.com/kb/153119

    Tommy

    Follow @sqlscribe

  • October 30, 2007 at 11:15 am

    #746773

    Hi

    Just a reminder that SQL is not a server to echange mail. What I mean by that is in some rare circumstances it can make hang the entire server / instance.

    In many cases it would have been a wonderful solution but because of that bug could happen, we always developed workaround solution to prevent a possible hang of the instance / server.

    If your server is critical, I would advise using something else for your mail.

  • November 5, 2007 at 12:32 pm

    #748381

    I had to use a relay in order for my database mail to work. There is another way you can email that is using xp_sendmail.

    bring up sql server 2005 surface area configuration -> surface area configuration for features -> under sql mail enable sql mail stored procedures

    then within your procedures you can add this code:

    xp_sendmail {[@recipients =] 'recipients [;...n]'}

    [,[@message =] 'message']

    [,[@query =] 'query']

    [,[@attachments =] 'attachments [;...n]']

    [,[@copy_recipients =] 'copy_recipients [;...n]'

    [,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'

    [,[@subject =] 'subject']

    [,[@type =] 'type']

    [,[@attach_results =] 'attach_value']

    [,[@no_output =] 'output_value']

    [,[@no_header =] 'header_value']

    [,[@width =] width]

    [,[@separator =] 'separator']

    [,[@echo_error =] 'echo_value']

    [,[@set_user =] 'user']

    [,[@dbuse =] 'database']

  • November 5, 2007 at 1:04 pm

    #748396

    I thought SQL Mail required Office to be installed. That is what many of us want to get rid of. My Database Mail does not work either and the troubleshooting has been unhelpful.

  • November 5, 2007 at 1:33 pm

    #748403

    are you using exchange server?

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

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