January 28, 2003 at 10:25 am
Does anyone know how I can disable a stored procedure? Speceifically, I want to be able to disable xp_sendmail when our Exchange servers have a planned outage. There seems to be a problem if SQL Server tries to send mail while the Exchange server is down. We are having lots of random xp_sendmail problems right now. SQL2000 SP2.
I suppose that if there is a way to revoke privileges of the xp from sa for a period of time, that would suffice as well. But I don't see how to do that either.
Thanks very much - in advance.
January 28, 2003 at 10:29 am
You can either:
1) deny execute on it when Exchange is down and grant execute on it when Exchange is back (doesn't affect sysadmin role users)
or
2) drop xp_sendmail when Exchange is down and add it back when Exchange is back up. As long as the .DLL is in place you should be fine.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 28, 2003 at 10:31 am
What about using xp_stopmail when exchange is down, then xp_startmail when exchange is up?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 28, 2003 at 10:40 am
I've never really tested xp_startmail and xp_stopmail enough to trust it. Probably would work, though.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 28, 2003 at 12:37 pm
What about using MSMQ with the Exchange connector? That would give you guaranteed delivery. I have never done it, but would be curious to see it work with SQL Server, would remove the dependency upon creating a MAPI profile that SQL Mail imposes. Also nother option is using CDOSYS to communicate to the exchange server via SMTP, the local IIS SMTP server should do store and forwarding for you at that point.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 28, 2003 at 12:40 pm
One other solution is to create personal folders and having mail delivery go to there. I'll try and get Void to post what he did on that here. This ensures that even if the network is down, the emails get created (no error for SQL Server) and when the net comes back up, the mail goes out.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
January 28, 2003 at 12:45 pm
Brian, are you suggesting dropping into the SMTP server's "DROP" folder?, or
something custom? Mailing has always been such a pain when developing
applications for distribution for clients at their sites. I am always curious to learn a new methodology. I have used the "DROP" folder before, btw...
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 29, 2003 at 3:54 pm
We encountered this problem too, and the SQL Server would just hang if mail was sent out. Fortunately, there is a correction that solves this exact problem.
The solution is to use version 8.00.603 of Sqlmap70.dll. The details are in Q320407. Ever since I replaced that file, all of the xp_sendmail problems went away.
I also am presuming that SP3 for SQL2000 includes this fix.
January 30, 2003 at 5:44 am
Another way to handle this problem would be to build a single stored-procedure that all others would call to generate the xp_sendmail. You could then (if you know the scheduled outages) program the one source to process/ignore the EXEC xp_sendmail. Alternatively, you could build a job scheduled to run x minutes prior to outages and replace the procedure with xp_sendmail disabled and then after outage is over have a job restore the procedure with xp_sendmail enabled
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 30, 2003 at 9:25 am
Building on the suggestion of whoteegan -
We have found it useful to build a small application that accepts all the mail messages to be sent and queues them into a series of tables. A separate service reads from this queue and executes the mailing process.
The main advantages are:
1) You can stop the mailing but still allow all other processes to continue normally.
2) You can send the same message to thousands of receipients easily.
3) Failed messages are easier to detect and you can build intelligence for determining potential failures in the future.
4) In a test system, the process that grabs the messages can be configured to send the messages locally to assure proper handling or just "fake" the send.
Guarddata-
January 30, 2003 at 9:58 am
Guarddata,
Would it be possible for you to expand on your system a bit?
Of most interest would be why different tables are used, although any additional information would be welcomed.
This sounds like an intriging system.
Everett Wilson
ewilson10@yahoo.com
January 31, 2003 at 11:53 am
ewilson10,
We have four main tables:
MessageHeader: Holds subject, priority, "From", Date to Send, Status
MessageBody: Series of Text fields that combine to complete the message.
MessageTo: Recipient list (To, CC, BC, etc.)
MessageAttach: Attachments
We did have one "feature" that was accepted. Because of limits on the email service, we could not send a single message to 10,000+ recipients at once so we just sent one at a time. We could have easily combined 500 recipients together (which was about the limit of our service) but didn't take the time to add that enhancement.
As you can see from the MessageHeader. This also allowed us to enter messages that would be sent at a particular date in the future.
Hope this helps - I know it is a little sketchy but you probably have the idea from this much.
Guarddata-
January 31, 2003 at 4:13 pm
Thanks for the additional info, this is a neat idea.
Everett
Everett Wilson
ewilson10@yahoo.com
February 3, 2003 at 10:45 am
We always make our Outlook profile the servername and this helps this script to work. There is an undocumented stored procedure called xp_test_mapi_profile. You can see it being used in the test button for SQL Mail in the SQL EM GUI.
Embed this script in a proc wrapper that is used when you want to use XP_SENDMAIL. When Exchange is down, you get a controllable error.
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
Run XP_SENDMAIL
ELSE –- you have an error condition with
-- your MAPI Server
Try running a Net Send to one of your DBA's with XP_CMDSHELL, maybe
February 3, 2003 at 10:48 am
Another way is to junk XP_SENDMAIL altogether and use Gert Draper's xp_smtp_sendmail instead. It's much more reliable than a link to an Exchange server and works particularly well if the SMTP service is running on the SQL server and set up to relay only itself (127.0.0.1).
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply