Issue with notifications

[netnut1234]

Hi

I have a question regarding notifications on SQL 2005. I have a server which is on a remote site and has no email client configured. I am trying to get SQL to notify me when a job has completed. Is there anyway to configure SQL to dump to a log instead of sending out an email, so I can then in turn use mapisend to send me this log. Or does anyone know if there an easier process that I can do to get this notification?
Thanks

 

[SQLDenis]

what about in the last step using an output file and writing to that (step-->edit-->advanced)



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

SQL 2005 doesn't need an email client. Use Database mail and sp_send_dbmail which uses SMTP to send email. No email client install is required.

Denis's method will work as well.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[netnut1234]

That's for the infor it has been of great help. I have now managed to get database mail to work, which is great. How do I then use this in my maintenance plan, as the only option I have is to notify operator. Does this use the database mail settings that I configured earlier. I have ran the job, but it doesn't send an email. Am I missing anything?

 

[mrdenny]

You have to change the notification from being sent on error to completion. (SQL Job propertys.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[netnut1234]

Hi Denny,
Thanks for all your help. I have done what you have said above. I have set up the maintenance plan, gone to the job properties and on the notifications tab, I have email to the operator selected, when the job succeeds and completed. When I go to view the job history, see the job has completed OK, but at the bottom of the job, I see a note that tells me, Failed to notify operator via email., Even thought I have this set up. Am I missing something? I have set up successfully the database mail, as I can send an email OK.

 

[mrdenny]

There are times when the SQL Agent fails to send an email for basically no reason. Is the SQL Agent able to send any emails out via the operators? What is logged in the database mail log?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[netnut1234]

Denny,

Thanks for this, I have now managed to resolve this issue. I had to reconfigure the SQL Agent to use database mail, tested and work. Not sure why it didnt work the first time, but its all sorted now. Thanks for your help with this.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog