Email Notifications

  • Hello All

    Was hoping someone could help me with a problem I have....

    I have set up a job to run every Monday @ 9am - And what I want to do is when the job completes I want it to email me to let me know...

    I have set-up SQL Mail - And have received the 'Test Email' - However when I run the job it says failed to notify user

    I'm guessing I have some settings wrong somewhere - but if someone could guide me on how to setup the email notification - Then that would be fantastic!!

    Thanks in advance

  • Hi there.

    If you are working with SQL Server 2005 then you should really consider using the newer Database mail instead of SQL mail.

  • Andy Hogg (2/11/2009)


    Hi there.

    If you are working with SQL Server 2005 then you should really consider using the newer Database mail instead of SQL mail.

    Sorry I misled you with my post - I am actually using Database Mail...

  • It sounds to me like SQL 2005

    Steps for email using DBMail

    1) Go into MSDB and allow the user SQL Agent is running as to use the Database Mail Role

    2) Go into SQL Agent and allow Database mail using the profile you have set up

    3) Restart SQL Server Agent - this is the one I forget all the time

    If you have to use SQL Mail consider using usp_send_cdosysmail to send mail using SMTP.

    I should add I usually set up a public profile just for this.

    To use with notifications you will have to set up an operator with the destination email addresses.

  • Hello

    My DB Mail is set up and I can send emails using the following code:

    EXECUTE Msdb..sp_send_dbmail

    @recipients = 'someone@hotmail.com',

    @subject = 'Test DB Mail',

    @body = 'SQL Database Mail Test Sucess',

    @profile_name = 'email exchange mail' ,

    @body_format = 'TEXT',

    @importance = 'HIGH'

    However I want my weekly job to send a notification on completion - How do I do this?

    At the moment I have it set up using the code above in a seperate step however would like to make use of the notification part

    Thanks

  • I call database mail in a separate step as well, I find it being the simplest way.

    If it works, don't touch it! 😉

    -- Gianluca Sartori

  • Gianluca Sartori (2/12/2009)


    I call database mail in a separate step as well, I find it being the simplest way.

    If it works, don't touch it! 😉

    Fair point - Although would be nice to know how to...

  • There is a Notify Operator task available in SQL 2005 maintenance plans (which are really SSIS packages). It allows you to send a message to one or more operators, and since it is an SSIS task, it can be made dependant on any other tasks in the job. The task executes the system procedure sp_notify_operator. You are limited to emailing those defined as operators within the server instance, and also to standard text (no variable output) for the body of the email.

    Agent jobs also allow notifications, but you can only select one operator, and are limited to a smaller set of conditions upon which to send the email.

    Using the Execute T-SQL task with either sp_notify_operator or sp_send_dbmail provide much more flexibility, especially the latter.

  • Call the Send Mail Task as a second step of the SQL Job, on success of step 1 go to step 2.

    Amol

    Amol Naik

  • if you have created database mail account then in job you can set notification......... crete new operator and in that set your mail id ... now in job properties goto notifications and select email and select when job succeeds ... it will send u mail whenever your job will succeed ....tell me if is not work

    Raj Acharya

  • Already tried that...Job completes but no notification is sent...

  • I, too, have a problem with Send Mail Task in SSIS.

    I have a Send Mail task at the end of a data transfer package which works perfectly on success of the package.

    If the package fails I want to send a different email to the same recipient saying that

    there was a failure. I put this email in the OnError event and forced a simple error situation to test it.

    The email was sent but had lost its Subject and Body texts. So I tried connecting the email task to the

    failure precedence constraint from the data flow task and got exactly the same result -no subject, no body.

    In trying to understand what was going wrong I just changed the precedence constraint to success.

    The full email was sent.

    So the summary is when the email is sent from either the OnError event or from a failure precedence

    on a data flow task both summary & body texts are blanked.

    Anyone got any ideas?

  • Did you set the properties of SQL Server Agent Alert System to Enable the mail profile you set up?

    Right click on SQL Server Agent, Select Alert System and ensure that Enable Mail Profile is checked and the Mail System points to the account you set up.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • Thought I had better report that this problem has gone away! It looks as if, somehow or other, the SQL Agent job which was

    executing the package was running a previous version. Not sure how, but it has now got fixed.

  • For anyone who might be interested in the above - the problem was with the fact that my Visual Studio is bound to Source Safe.

    It seems that if I changed the package, built it, saved it and copied it to the live server without checking in the altered version

    then the old version was still the one that was getting used! I checked the datestamps on the dtsx files and they were correctly

    on the time I would expect, but still the version saved was not the modified one!

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

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