Intro to Database Mail in SQL 2005

  • Can i just ask something to which i think i already know the answer: if i am managing my SQL 2000 servers in 2005 Management Studio, can i use Database Mail on these servers for SQL Alerting?

    Thanks

    Div

  • Is there anyone out there who has replaced the SQL2005 xp_sendmail with a custom SP that calls sp_send_dbmail?  As I only need send capability, I am thinking of doing this so we can loose MAPI-based mail on our SQL Server boxes. 

    eplacing xp-sendmail with a wrapper for sp_send_dbmail would allow us to de-couple the move to DB Mail from the application changes needed to move away from xp_sendmail.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • If this was stated above anywhere sorry for the repeat...some Virus scanners block port 25 so you might need to add the follow exe to the Virus scanner port 25 exception list (if you don't disable that feature altogether; hopefully not) - DatabaseMail90.exe.

    Also, if you have to set up a lot of servers here is some basic code, and I stress basic, that will add a public, default mail profile and account. The Profile name is picked up as the server name and the account name simply adds 'SQL ' to the front of that for an easy Outlook rule check...

    Be sure to modify where stated before using.

     

    /** Enables Database mail on SQL 2k5 & Creates a Mail Profile and Account *********/

    /*

    ** assumes no profiles and accounts exist; New SQL 2k5 installtion

    ** assumes you are creating the first profile and account

    ** sets this profile as a public, default profile

    ** I use the <server name> as the profile name

    ** I use SQL + <server name> as the account name; make for easy Outlook rules

    ** be sure to add these executables, DatabaseMail90.exe,sqlservr.exe, to the Virus Scanner port 25 exception list

    ** be sure to add your corporate SMTP relay server name in the 2nd Exec command where needed

    ** be sure to replace <myplace> with your company domain name (yourcompany.com)

    */

    use msdb

    exec sp_configure 'Database Mail XPs', 1

    reconfigure with override

    waitfor delay '00:00:02'

    declare @mailprofilename sysname

    declare @mailprofileid int

    declare @mailaccountname sysname

    declare @mailaccountid int

    declare @email_address sysname

    set @mailprofilename = @@servername -- change as desired

    set @mailaccountname = 'SQL ' + @mailprofilename  -- change as desired

    set @email_address = @mailprofilename + '@<myplace>

    -- create a SQL mail profile

    Exec dbo.sysmail_add_profile_sp

           @mailprofilename

         , 'Local default mail profile'

         , @mailprofileid output

    -- create a SQL mail account

    Exec dbo.sysmail_add_account_sp

           @mailaccountname

         , @email_address

         , @mailaccountname

         , ''  -- reply to address

         , 'Local SQL smtp mail account'

         , '<corporate SMTP relay server>'  -- corporate SMTP relay server

         , 'SMTP' -- mail type

         , 25  -- port #

         , null

         , null

         , 0 -- 0 anonymous auth, 1 NT auth

         , 0 -- 1 enable ssl

         , @mailaccountid output

    -- associate the SQL mail profile and SQL mail account

    Exec dbo.sysmail_add_profileaccount_sp

           @mailprofileid

         , @mailprofilename

         , @mailaccountid

         , @mailaccountname

         , 1  -- sequence #

    -- set the SQL mail profile as public and default

    Exec dbo.sysmail_add_principalprofile_sp

           2

         , 'guest' -- public profile

         , @mailprofileid

         , @mailprofilename

         , 1  -- default profile

    go

  • This is an excellent article and instruction on setting up Database mail. Very impressed and I was able to setup and have my emailing working after following the article step by step guidelines.

    Keep up the good works and continue to share your knowledge.

    Alfred owusu(Canada)

  • The article is good.

    I set up the Database mail account and tested it and it worked fine.

    I then set up a maintenance plan with a Notify Operator Task only and executed it to see if the message would be sent to me.

    The task failed.

    What am I missing?

     

  • Is your operator mail list in SMTP format?

  • Does anyone else have the problem that messages generated from database mail get caught in the exchange spam filter? Is there an extra step I need to do to configure it? The DB is on the same intranet, all the users we're emailing are in our own domain, I provided a valid Exchange username when I set up the profile. Our network admin says there's nothing he can do to change the spam filter settings--does anyone have any idea what I should try on the db side?

    Thanks very much in advance!

  • Good article. Can someone tell me how to use these mailaccounts in jobs? Let's say I make a job that has 2 steps. First is dump transaction log, second is make a backup of a database. Watching the notifications window, it looks like I can only send an E-mail depending of the result of the whole job, not per step. When I try to select someone to send the email to, I can only select an operator. Should I use operators?

  • Great article. I got my Database Mail up in running!

    I have a question on how to approach re-sending of Failed Mails.

    I'm in the process of developing an app (VB.net, ASPX, and SQL2005). The users updated some tables and I want to send out emails. I have some triggers that fire on the Update of a table.

    In testing I noticed 2 emails have failed : "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-12-20T11:48:28). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.). )"

    Can someone give me ideas on how resend?

    I've been told that the SMTP server can go down and up like a yo-yo. My company is supported by 'HP' :P, so we have no infrastructure support

    If the server is down then the Trigger is fired, I would expect the email to fail. If this is a the case, is there an error code I can trap for?

    If I put the sp_send_DBMail in a Try...Catch and if there is an Error I can write an entry to an exception table, on that I can run a daily job against trying to resend the Mail. I think that would be a good idea, assuming I can trap for an error? Come to think, I would need to know a success code so I can delete the entry from the exception table is the email is sent successfully.

    I'm very new to SQL Programming. My experiences are simple selects/updates and deletes with ADO.NET, so my SQL Programming experience is a Newbie.

    Here is a snip-it of code from my trigger that is working.

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    Alter TRIGGER [dbo].[tr_Request_UPDATE]

    ON [dbo].[tblRequest]

    AFTER UPDATE

    AS

    --Check for Request_Status Change

    --Possible Changes: Pending B3 Approval --> Approved or Denied

    --Possible Changes: Pending B4 Approval --> Approved or Denied

    IF NOT UPDATE(Request_Status)

    RETURN

    --Determine if Request_Status was changed

    IF EXISTS (SELECT a.Request_StatusFROM inserted a Where a.Request_Status IN('Approved', 'Denied'))

    BEGIN

    --Check for Approval or Denied Status

    --IF Denied, Send out Email of Denial

    --IF Approval, Update tblRegistrations, Insert a new Row with the Appropiate Field.

    DECLARE @Request_IDINT

    DECLARE @Requester_Namenvarchar(50)

    DECLARE @Request_App_IDINT

    DECLARE @Request_Statusvarchar(10)

    DECLARE @Bodynvarchar(255)

    DECLARE @App_Ownernvarchar(50)

    DECLARE @Recipientsnvarchar(50)

    Select

    @Request_ID=a.Request_ID

    ,@Requester_Name=a.Requester_Name

    ,@Request_App_ID=a.Request_App_ID

    ,@Request_Status=a.Request_Status

    FROM inserted a Where a.Request_Status IN('Approved', 'Denied')

    If @Request_Status='Approved'

    BEGIN

    --Request Approved

    --Send out emails to the Requester and the Application Owner.

    Select @App_Owner = App_Owner from dbo.tblApplications where App_ID = @Request_App_ID

    Select @Body= 'Your Request for access to the Tool/Application has been approved by'

    Select @Recipients= @Requester_Name + '; ' + @App_Owner

    --Send the Mail

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'bdc-665_mail',

    @recipients = 'rwiethorn@XXXX.XXXX',

    --@query = 'Select * From [PSRA].[dbo].[tblRequest] Where Request_ID=12',

    @subject = 'Request',

    -- @attach_query_result_as_file = 1,

    @Body = @Body ,

    @body_format='HTML'

    END

    END;

    Any ideas on where to look/read is appreciated,

    Thanks,

    rwiethorn 'AT' g Mail

  • Hi guys,

    I was trying to send a mail using Dataase mail option in sql server.I have configures the user account and profle for that database mail.I could able to send the mails to the user who are under our server, but couldn't send to others(gmail or yahoo).I am getting the following error

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2008-07-16T12:00:54). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: No such user here). )

    Help me out please................

    Kiran

Viewing 10 posts - 16 through 24 (of 24 total)

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