Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: SQL Mail

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: SQL Mail

    Hi I was wondering about setting sql to email me when a job fails (backups fail) I tried to set up Database mail but it didnt work when I tried to send a test email. Any suggestions??

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    better question. do you have an exchange server on your network?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is dbmail enabled in the SAC?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    hi guys, yes we have an exchange server

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Which version of SQL?

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sql server 2005

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have the Database Mail XPs on in sp_configure?
    Code:
    exec sp_configure 'show advanced options', 1
    reconfigure with override
    
    exec sp_configure 'Database Mail XPs', 1
    reconfigure with override
    
    declare @profileid int
    declare @accountid int
    declare @email varchar(255)
    declare @server varchar(100)
    declare @environment varchar(30)
    declare @display nvarchar(255)
    set @email = 'put an email address here'
    set @server = replace(@@servername, '\', '_')
    set @environment = 'Staging'
    
    set @display = 'SQL Server ' + @environment + ' server alerts'
    
    exec msdb..sysmail_add_profile_sp @profile_name = 'SQL Server Alerts',
    	@description = 'Profile used for SQL Server alerts',
    	@profile_id = @profileid output
    
    exec msdb..sysmail_add_account_sp @account_name = @server,
    	@email_address = @email,
    	@display_name = @display,
    	@replyto_address = 'Put an email address here',
    	@description = 'Mail account used to send alert emails',
    	@mailserver_name = 'Put in a mailserver name here.  SMTP preferred',
    	@port = 25,
    	@account_id = @accountid output
    
    exec msdb..sysmail_add_profileaccount_sp @profile_id = @profileid,
    	@account_id = @accountid,
    	@sequence_number = 1
    
    
    exec msdb..sp_send_dbmail @profile_name = 'SQL Server Alerts',
    	@recipients = 'Put in an email address here',
    	@subject = 'Test eMail',
    	@body = 'test email.  Please ignore'
    had to clean up some of my company specific values, so some of the variables are not apparently used.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face

    Quote Originally Posted by MCrowley
    Do you have the Database Mail XPs on in sp_configure?
    Code:
    exec sp_configure 'show advanced options', 1
    reconfigure with override
    
    exec sp_configure 'Database Mail XPs', 1
    reconfigure with override
    
    declare @profileid int
    declare @accountid int
    declare @email varchar(255)
    declare @server varchar(100)
    declare @environment varchar(30)
    declare @display nvarchar(255)
    set @email = 'put an email address here'
    set @server = replace(@@servername, '\', '_')
    set @environment = 'Staging'
    
    set @display = 'SQL Server ' + @environment + ' server alerts'
    
    exec msdb..sysmail_add_profile_sp @profile_name = 'SQL Server Alerts',
    	@description = 'Profile used for SQL Server alerts',
    	@profile_id = @profileid output
    
    exec msdb..sysmail_add_account_sp @account_name = @server,
    	@email_address = @email,
    	@display_name = @display,
    	@replyto_address = 'Put an email address here',
    	@description = 'Mail account used to send alert emails',
    	@mailserver_name = 'Put in a mailserver name here.  SMTP preferred',
    	@port = 25,
    	@account_id = @accountid output
    
    exec msdb..sysmail_add_profileaccount_sp @profile_id = @profileid,
    	@account_id = @accountid,
    	@sequence_number = 1
    
    
    exec msdb..sp_send_dbmail @profile_name = 'SQL Server Alerts',
    	@recipients = 'Put in an email address here',
    	@subject = 'Test eMail',
    	@body = 'test email.  Please ignore'
    had to clean up some of my company specific values, so some of the variables are not apparently used.

    No McCrowley I dont, and I better get crackin
    Last edited by desireemm; 01-15-09 at 14:00.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Assuming you have SQL 2005 (not SQL 2000), then the basic steps are: (you can read more in SQL Books On Line)

    1) You need a valid SMTP server with a valid SMTP account (Exchange, which was mentioned above, is NOT required)
    2) Log in to your SQL Database Server using SSMS
    3) Navigate to the Management | Database Mail node]
    4) Right-click and click on configure (if the Database Mail XPs are not enabled in Surface Area Configurator, you can select the option to enable them now)
    5) You will need to create a Profile and an SMTP account. Basically:
    a) A profile contains one or more SMTP accounts
    b) Profiles objects that you can use to specify when sending mail and can be public or not public
    c) The SMTP account is where you will specify the mail server and login information
    6) Navigate to SQL Server Agent and create an Operator
    7) Now right-click on SQL Server Agent and click properties
    a) On the Alert System tab, click "Enable Mail Profile"
    b) Select the mail profile you want to enable
    8) You may also want to enable the "FailSafe" operator option; though I have never had this actually work

    You will now need to restart SQL Server Agent (just the Agent)

    Now when you create a job, you can navigate to the Notifications Tab and specify to send an e-mail to an operator when the job fails, completes or succeeds.

    Separately, you can also create alerts to send you notifications when specific events or performance conditions exist.

    I also like to add a startup procedure that sends me an e-mail when the SQL instance starts.

    Regards,

    hmscott
    Have you hugged your backup today?

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Oh yeah, one more thing. If you have Anti-Virus software installed, make sure that it is not set to block port 25!

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    This was a major pain in the butt for us when I came to work here and set up the servers to send email for job failures (etc) also. We use SQL 2000 but it was still a pain. I found a few sites online that had step-by-step guides in how to set it up, and also used BOL and microsoft's online knowledge base. I probably should have written down what I did to make it work. The first time was a pain in the butt, but subsequent times have been easier.

    On 2000, you gotta have a client running on the server also (Outlook 2000 or later). We also had to call the network support group to set up an exchange user ID (account) for the outlook client to use.

    Maybe 2005 is easier, eh?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    also, make sure EHLO is supported by your SMTP server for DBMail.

  13. #13
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by TallCowboy0614

    Maybe 2005 is easier, eh?

    Absolutely no doubt about it. SQL 2000 was a total PITA when it came to configuring database mail. SQL 2005 is a HUGE step forward. One thing I liked was that each profile supports multiple SMTP servers; if you have an issue, it will automatically try the next one on the list.

    Regards,

    hmscott
    Have you hugged your backup today?

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank you so much for your help guys I really appreciate it

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well I am happy to report that my backups are working great. I want to learn how to use script instead of the mainteance wizard that SQL Server 2005 provides thoughl. I want to try Tara script

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •