Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_startmail and switching profiles


xp_startmail and switching profiles

Author
Message
uday
uday
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
Guys!
I'm using SQL Mail to send out Emails to Customers re: outstanding Invoices. I also use SQL Mail on the same box to send out emails to our Sales Reps with summaries of outstanding invoices. Now, I've set up multiple outlook profiles on the server. I tried using

xp_startmail @user = 'CreditControlDept'
xp_sendmail .... (to customers)
xp_stopmail @user = 'CreditControlDept'
xp_startmail @user = 'InvoiceTracker'
xp_sendmail...(to internal sales reps)
xp_stopmail @user = 'InvoiceTracker'

DOES NOT WORK!!! the profile does'nt switch at all. SQL Mail sends out all the emails using the profile that it started with. How can I use xp_startmail and xp_stopmail to switch between different user profiles.

Regards
Uday



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: Moderators
Points: 8426 Visits: 780
COuld be an issue with the hooks to stop and the start the mail process that is causing this. Have you tried in QA to run the first start and stop and wait say 10 secs and try the next giving mail to clear completly. If this works you may want to put a wait in your process after the stop and before the next start. Also how is this fired. Can multiple pieces fire this same code at the same time potentially stepping on each other? If so you will have to build a process to handle these message by itself like maybe a staging table with a job that picks up the messages.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



uday
uday
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
Thanks for your input and guess what...I just put in some code to pause for 15secs. The profile switches on my normal SQL7.0 Server on NT4.0 Server. But, the same does'nt work on my real production envriroment which is NT4.0 CLUSTER SERVER runnign SQL 7.0. The profiles don't switch in this environment. MOre suggestions?
I have a simple but effective process flow to avoid the "stepping over" effect - thankx!

REgards
Uday



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: Moderators
Points: 8426 Visits: 780
Sorry I cannot think of anything ese offhand. Will post if I do though.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36164 Visits: 18751
Are the profiles on the active server? On both? I think you may have issues with the virtual server performing this switch.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
In my opinion a better way to do this is to use CDO to send the mail so that you can set the from/to options. Changing profiles back/forth could cause some delays in processing, doesnt seem like a good use of resources.


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36164 Visits: 18751
Tend to agree with Andy. Don't forget the SQL Mail is a subsystem added on to SQL Server. I'd be wary of switching profiles as you may cause instability on the server, which wouldn't be worth it. I'd go with Andy's suggestion and use CDO or CDONTS to send the mail.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
uday
uday
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 32
Andy and Steve!
Guys thankx for your input. I've given CDONTS already a shot, simply can't use it cause it can't execute queries on the fly. So, I either hunt down a product that can execute queries like xp_sendmail, or, figure out a way to make the profile switching work. Hey one of you guys mentioned profiles on the "active" server. I created an outlook profile only on one "node" of the cluster not on BOTH nodes. Are you suggesting create the same profile on both nodes and then try it.
-Also guys, I noticed when I issue a xp_stopmail @user ='blah' followed by a WAITFOR DELAY 15 seconds on node1, the cluster server actually kick-starts sqlmail using the profile I >>>dont want<<<. Is this something that is possible?

Regards
uday



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
As far as CDO, you should take a look in the script library, might find something already made. If not, still not hard to write a wrapper around it that gives it the ability to return a recordset and loop through it to build up the message - or maybe just save as xml and combine with XSL to generate content.


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36164 Visits: 18751
Haven't done that much work with Clusters, but I suspect the profile should be on both.

I agree with Andy. Give CDO a shot and then start a thread here with your code if you can't get it to work. Andy and others should be able to help.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search