Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

xp_startmail and switching profiles Expand / Collapse
Author
Message
Posted Monday, May 13, 2002 11:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:12 AM
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






Post #4217
Posted Tuesday, May 14, 2002 5:43 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 1:22 PM
Points: 8,369, Visits: 734
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)



Post #33712
Posted Thursday, May 16, 2002 11:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:12 AM
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





Post #33713
Posted Friday, May 17, 2002 9:34 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 1:22 PM
Points: 8,369, Visits: 734
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)



Post #33714
Posted Saturday, May 18, 2002 10:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #33715
Posted Saturday, May 18, 2002 1:03 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:53 PM
Points: 6,705, Visits: 1,684
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
Post #33716
Posted Sunday, May 19, 2002 10:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #33717
Posted Sunday, May 19, 2002 5:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:12 AM
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





Post #33718
Posted Monday, May 20, 2002 4:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:53 PM
Points: 6,705, Visits: 1,684
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
Post #33719
Posted Monday, May 20, 2002 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #33720
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse