September 8, 2008 at 6:34 am
Hi,
Using SQL Server 2000 and MS Outlook as the client, is it possible
to send using a mail profile other than the default?
I want to set up a T-SQL stored procedure to send email from different addresses,
dependant on one of the arguments passed. The server routinely sends out emails from
the server (default) profile but we need to send out emails from our accounts and customer
services teams.
I know this is possible in SQL 2005 but xps_sendmail does not have an @Profile_name
argument.
Thanks in advance.
Dave
September 8, 2008 at 6:45 am
Ahhh, figured it out.
use master
exec xp_stopmail
exec xp_startmail @user = 'GrayD'
exec xp_sendmail
@recipients = 'grayd@turpin-distribution.com', @message='Hello'
I just setup a new mail profile called 'GrayD', stopped/started mail.
September 8, 2008 at 6:49 am
Wondering if you were to set up another profile in Outlook then within the script do the following;
xp_stopmail
go
xp_startmail 'Non Default Profile' --This is the name for the non-default profile
go
--Do your send mail statement
xp_stopmail
go
xp_startmail 'Default Mail Profile' --Typically "MS Exchange Settings"
go
The one problem that I would see with that is that if there are other connetions open through Outlook you will still have a mapi.dll open on the server and it may not let you complete the above statement successfully but I don't know for sure.
I have never tested what I recommend above but theoretically it should work, I think. 🙂
I would be curious to hear how this goes. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 8, 2008 at 6:50 am
Ok, looks like I was too late...
Good to remember to put it back to the default profile after you complete your task though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 8, 2008 at 6:54 am
That's a good point. I've not changed the default profile in the GUI as
lots of SQL jobs still need to use this profile.
Do you think I should just run
exec xp_stopmail
exec xp_startmail
At the end of the stored procedure to reset everything back?
Dave
September 8, 2008 at 6:57 am
I have always found it to be more consistent if I specify the default mail profile name which is "MS Exchange Settings" unless you did it differently.
So, I would use
xp_stopmail
go
xp_startmail 'MS Exchange Settings'
go
just to be sure that I am hitting the correct profile.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 8, 2008 at 7:05 am
Yes that makes sense, better to be 100% safe.
Thanks for your help.
Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply