database mail: problems around xp_sendmail and mail profile
-
Hello you all. Here is my first post since i have joined SQL Server Central. I have consulted this website for many time! Good work!!
This post is some weird problem problem about emailing from the database server. We are running SQL server standard x64 version SP2.
xp_sendmail:
When trying to email with xp_sendmail command it returns the message "SQL Mail does not work with the 64-bit version of SQL Server". That's a bummer. But I have read somewhere that some people got it to work. Can anyone confirm this information, or is there a workaround for this?
No mail profile defined
Like every DBA we want to get messages when maintenance plans, SSIS packages, or jobs are failing on the database server. We have correctly defined an public email profile and sending a test email is working just fine. But there are no emails send from the server within these DB jobs or SSIS packages definied to send an email notification on failure. Many DBA-ers suggest to restart the SQLServerAgent but this doesn't do the trick. What we do see is an alert in the server log after a restart from the SQL Server Agent service: "Unable to start mail session (reason: No mail profile defined)". So i guess the problem is laying somewhere else.
[offtopic]
Is it possible with this software what is used for the forum to search just in this or a specific forum?
[/offtopic]
Kind regards,
Henri Flokstra
-
is there any reason why you can't use database mail which comes with SQL 05? It has full 64 bit support.
-
@Dustin, no i can't find a reason unfortunaly
What i have found out is that this procedure is not working:
EXEC master.dbo.xp_sendmail
@recipients=N'[my emailaddress]',
@message=N'test' ;
And this procedure is working fine!
exec msdb.dbo.sp_send_dbmail
@profile_name=[profilename],
@recipients=N'[myemailaddress]',
@body='test'
So the procedure sp_send_dbmail is doing what is it suppose to do.
-
dbmail is the new native sql server mail system (smtp based) for SQL 2005. The xp_sendmail extended stored procedure is an extension developed because the native SQLMail system on SQL Server 2000 relied on Exchange.
-
Thank you Dustin for explaining!
Database mail is working now using msdb.dbo.sp_send_dbmail. Now it still leaves me one problem, sending notification emails within jobs, SSIS packages and Maintenance plans.
In the SQL server Agent LOG i can find the following messages, after restarting SQL Server agent:
- Warning: "[260] Unable to start mail session (reason: No mail profile defined)"
Running a job with email notification on failure (eg select * from from , which will fail)
- Error: "[264] An attempt was made to send an email when no email session has been established"
We have correctly defined a public email profile (which can send test emails!). Here is something wrong but what..?
-
In the jobs you have to use the database mail profile to send mails to you once they are completed. You need to just add another step and use the sp_send_dbmail task to it. thats it and you are done.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
@sugesh: Thanx for your reply. It sounds like a nice solution, and in some cases it is a nice solution.
But for the most part we don't want a email when things go right. Then i'm gonna be spammed a lot by our server. We want to be notified when things go wrong. Eg. when a backup plan has failed because of low disk space we want to get notified, and we don't want to get notified each time a backup has succeeded.
-
in the past I've used a standard error step in all jobs that is only executed when 1 step fails. This error step contains the database mail call to send the email.
-
You can define the job step to goto the mail when it fails and end succeed. this should be so simple in your case.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Problem solved! Just found the solution for: "Unable to start mail session (reason: No mail profile defined)"
Yeeha!
In SQL server Management Studio -> SQL Server Agent -> properties -> Alert System -> Enable mail profile. Give SQL Server Agent a restart and it works!
The mail profile was correctly definied but you have to enable is specificly in SQL Server Management Studio.
@sugesh. Thanks for your reply! I was implementing it for some jobs, but i ran into the solution by accident. Making a new step for failure and implementing sp_sendMail is a nice workaround. If we have a lot of jobs running it is a big job to implement a new step for each job for failure which is just gonna tell me that a job had failed. No specific information on what went wrong or what so ever. When job notification is enabled a email is send to the DBA with specific information on what step went wrong and additional information what caused this error.
Thank you all for helping!
Regards,
Henri
-
Yes thats right Henri. I was just telling you an additional solution that was possible. Moreover you can write to the log and send them in the mail step which is possible. i hope you can give a try next time if you have a chance
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
[font="Comic Sans MS"]Thanks for the solution , this post really helped me as I too faced with the same problem.
Recently i moved the System Databases from their default location and after Agent restart, it stopped sending me the notification mail. Now i changed back the settings with the help of your post.
But ,similar to notification through mail, i also configured NET SEND option. It used to work previously, but its not working now.
Any suggestion/ help is appreciated.[/font]
Thanks,
Vampire
--In 'thoughts'...
Lonely Rogue -
I had set this up on another sql box but forgot the step to enable the profile in the alert confit.
Thanks for the reminder!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply