SQL Mail vs xp_sendmail

Question

Post reply

SQL Mail vs xp_sendmail

jhklink

Mr or Mrs. 500

Points: 582
More actions
July 11, 2005 at 12:46 pm

#110328

All,
Has anyone ever noticed that if you use SQL Agent to send a "mail alert" via SQL Job, SQL AlLert or DTS "Send Mail" package that it will send the mail via the SQL Agent Startup account and if you use xp_sendmail it will send through the MSSQLSERVER startup account?
We have two accounts per server (MSSQLSERVER & SQLSERVERAGENT). Sometimes the xp_sendmail does not function correctly. I wonder if there are certain rights the account that is starting MSSQLSERVER needs to have. Any thoughts?
Jim

Viewing 8 posts - 1 through 7 (of 7 total)

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

jf-239130 SSC Rookie Points: 31 More actions · Answer 1

You should always 'start' a mail session by xp_stopmail:

xp_stopmail

xp_startmail

xp_sendmail

xp_stopmail

If you have access to a smtp server, it is better to write your own e.g. sp_sendmail...

Peter Edmunds Grasshopper Points: 22 More actions · Answer 2

sp_ is System Procedure not stored procedure, read BOL Newbie

philcart SSC-Forever Points: 47796 More actions · Answer 3

Peter Edmunds, actually the sp_ prefix does not necessarily indicate a System Procedure. It just so happens that MS used that prefix for their system procedures. Users can quite easily create their own stored procedures using the sp_ prefix.

jf , "If you have access to a smtp server", why would you write your own when you can use something like xp_smtp_sendmail from http://www.sqldev.net ?

As for the posters original question, the problems could arise from having two seperate accounts (usually the SQL services run under the same account), or the simple fact that SQL Mail isn't that reliable anyway.

If a single mail profile is being used then both of these accounts would need to be able to access the Exchange mailbox that is defined in the profile. The account that isn't the owner of the mailbox would also need 'Send on Behalf' privelages.

If seperate profiles are being used, then there is most likely some other issues that cause xp_sendmail to "not function correctly". Without any specific error message we could only grasp at straws for a solution.

--------------------
Colt 45 - the original point and click interface

Peter Edmunds Grasshopper Points: 22 More actions · Answer 4

Yeah yeah yeah, my answer was "strictly" off thread, we have conventions to keep a very complicated existance as simple as possible, yer know, the KISS principle, and I don't mean the principles espoused by graduates of the University of Appalachia's Doctor of Information Technology in Modulo 12 courses like the weirdo's who say things along the lines that Ron Fagin's domain key normal form is "a theoretical ideal, but for most practical purposes ridiculous"

It doesn't "just happen" that MS used the sp_ prefix it's -fairly- well documented and a perfectly sane way to keep track of the complicated stuff that every shop eventually ends up with to leave the sp_ and xp_ prefixes for MS, there is lots of other choices.

You -can- buy a new set of tyres for your car then set at them with a grinder and cut all the tread off.

Just because you can do something doesn't imply there is a good reason why you should do it.

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 5

All of our user created stored procedures are prefixed sp_ and this doesn't confuse us at all.

philcart SSC-Forever Points: 47796 More actions · Answer 6

While you might not be confused, by using the sp_ prefix you are however slightly confusing SQL Server.

When SQL executes a stored procedure with the sp_ prefix it will look first in the master database and then in the user database.

--------------------
Colt 45 - the original point and click interface

Graham Macfarlane Mr or Mrs. 500 Points: 539 More actions · Answer 7

Thanks, I'll bear that in mind. Could call for a change in our standards!