February 4, 2003 at 11:03 am
Thanks to everyone for your input. I'm glad there are alternatives out there for these xp_sendmail problems that seem to be so prevalent.
DrNetwork: Can you elaborate on your code? I'm thinking of putting this at the beginning of a new SP and then call xp_sendmail if @rc = 0:
DECLARE @rc int, @profilename sysname
SET @profilename = @@servername
-- If you profile name isn’t your server
-- name, you need to adjust
EXEC @rc = master.dbo.xp_test_mapi_profile @profilename
IF @rc = 0
But how can I do this without having to pass every parameter for xp_sendmail each time we want to run the wrapped SP?
Thanks again very much.
February 4, 2003 at 5:09 pm
For the wrapper, have the same input parameters as XP_SENDMAIL with the same data types. Have the parameters automatically default to the values that XP_SENDMAIL defaults for. That way when you explicitly pass a parameter it will take that value, otherwise, it would take the default. See BOL for the defaults.
As an example,
create proc sp__sendmail$wrap (
@recipients varchar(1000) = NULL,
,@message varchar(8000) = NULL,
,@query varchar(8000) = NULL ....
February 6, 2003 at 7:42 am
Ahh yes, the ol' personal folders trick.... Thanks, Brian!
Yeah, this is kinda tricky, but the net result is that the server will cache e-mail messages before it sends them. I actually tested this theory out here by disconnecting the network cable from a SQL box for about an hour, while calling multiple xp_sendmail calls. I reconnected the cable and all e-mails were sent!
The way this is accomplished is that the outlook client basically acts like it would if someone were using outlook from the "road" and had intermittent access to the e-mail server. This is what I would call the "road warrior" configuration.
You will have to have sql mail and agent stopped to do this. If any service is currently using mapi, those services will have to be stopped.
Here's how it works.
1. Set up microsoft outlook personal folders for the SQL Server Service Account locally. You can do this by right-clicking the outlook icon and going to properties, and click add.
2. Set the delivery point to personal folders. Go to the delivery tab and select personal folders. Click OK to exit and save your settings.
3. Here's the tricky part. Start Outlook. under personal folders, go to contacts. Enter in all the contacts that would be possible e-mail recipients. These are recipients that are listed on your exchange address list on the exchange server. There are multiple ways of doing this, but the best way by far is if your exchange admin has created an offline address book that you can simply download into your outlook client. I'm not going to get in depth about how to do this here, but anyone who is familiar with configuring "road warrior outlook" can help you with this.
4. Right click contacts on the folder list (select view->folder list if you can't see it) and click the Outlook address book tab. Make sure "Show this folder..." is checked. In the text box just below, add a "-Personal" to the text. This way you can differentiate between the on-line address book and the offline. You'll see why this is importnat later. Click OK.
5. Now, click tools->options->E-mail options->Advanced E-mail options. Disable automatic name checking. Click OK 3 times. Exit Outlook.
6. Finally, right click the outlook icon once again, and go to properties. Click the addressing tab. Show this address list... = "Contacts - Personal", Keep personal... = "Contacts -Personal", and even though we disabled automatic name checking, just for laughs, we'll set the address book order to "Contacts - Personal", then "Global Address Book". You may have to add the personal contacts address book to the list.
That's all as far as configuration. Test it by purposely making your exchange box unavailiable (I always just disconnect my network cord form the back) and sending a bunch of xp_sendmail messages. Typically it will return a "mail sent." message. When you plug the network cord back in, all the messages you sent with xp_sendmail will come flooding through!
EDIT TO THE ABOVE - on step 3, you only have to do this if the xp_sendmail recipients are exchange mapi e-mail recipients. For smtp style addresses, it is not neccesary to do this. So yeah, hypothetically you can program whatever application that uses xp_sendmail to always use the smtp style, but that would mean that you would lose functionality if you ever wanted to go the other direction. Confused? I hope not... 😉
Edited by - void on 02/06/2003 12:57:37 PM
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply