February 11, 2009 at 4:36 am
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
February 11, 2009 at 9:45 am
Hi there.
If you are working with SQL Server 2005 then you should really consider using the newer Database mail instead of SQL mail.
February 11, 2009 at 9:48 am
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...
February 11, 2009 at 9:52 am
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.
February 12, 2009 at 4:13 am
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
February 12, 2009 at 4:28 am
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
February 12, 2009 at 4:43 am
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...
February 12, 2009 at 10:31 am
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.
February 13, 2009 at 1:00 pm
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
February 15, 2009 at 11:02 pm
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
February 16, 2009 at 6:03 am
Already tried that...Job completes but no notification is sent...
February 16, 2009 at 6:28 am
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?
February 16, 2009 at 6:37 am
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.
February 16, 2009 at 7:27 am
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.
February 16, 2009 at 7:52 am
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