May 13, 2002 at 11:02 pm
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
May 14, 2002 at 5:43 am
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)
May 16, 2002 at 11:59 pm
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
May 17, 2002 at 9:34 am
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)
May 18, 2002 at 10:03 am
Are the profiles on the active server? On both? I think you may have issues with the virtual server performing this switch.
Steve Jones
May 18, 2002 at 1:03 pm
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
May 19, 2002 at 10:48 am
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
May 19, 2002 at 5:28 pm
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
May 20, 2002 at 4:51 am
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
May 20, 2002 at 9:34 am
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
May 20, 2002 at 8:01 pm
Andy + Steve!
I tried out the scripts library, no luck cuz the script there requires SQL2K and W2K environment and we don'nt have it yet here.
Steve, do you think I need to start a new thread, cuz I'll be posting exactly the same question.
Thanks and Regards
Uday
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply