February 12, 2007 at 10:39 am
I have a clustered sql 2000 server on windows 2003 I support. The netadmins had to failover the active node to the passive to perform some maintenance. Once they restored back the original node, sql mail is now reporting an error when I try to test from the operators test window. I can open up outlook and send an email without error. the error is 22022: sqlserveragent error. the client operation failed.
February 12, 2007 at 8:46 pm
I have had a similar problem. Do you have the exact same mail profile name on each node. and have that profile configured identically. The sql server is clustered but the mail client is not and must be configured on both nodes
February 13, 2007 at 12:43 am
SQL Mail is a MAPI connection, here is my reset T-SQL:
DECLARE @Err int
EXEC master.dbo.xp_stopmail
WAITFOR DELAY '0:0:02'
EXEC @Err = master.dbo.xp_startmail
IF @Err <> 0
RAISERROR('xp_startmail: Failed to start SQL Mail session.',19,1) WITH LOG
ELSE
BEGIN
EXEC dbo.xp_cmdshell 'net stop sqlserveragent'
WAITFOR DELAY '0:0:01'
EXEC dbo.xp_cmdshell 'net start sqlserveragent'
END
This is used whenever the persistant MAPI connection is lost (Exchange restarted, etc.).
If the stop mail errors I run it again just to be sure.
Note that this is also a good trick for SQL Agent Jobs, ever come in and find that one has hung all night trying to send an email? Change the RAISERROR parameter from 19 to 20, which will end the Job.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply