Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Unanswered: Test MAPI Connection (Definitive Description)

    (Isn't this fun? Type MAPI and SQL into Google and it almost tilts!)

    Here is the situation:
    1- When SQL Agent starts, it establishes a MAPI session. For some bizarre design reason, it keeps that session open as long as it is running, rather than re-opening the session when it has mail to send. (this can be days, weeks, or months)
    2- Frequently, Exchange servers are clustered. When the virtual server moves to a different physical server in the cluster, - or the Exchange server is restarted for whatever reason - the (above) MAPI session of the SQL Agent gets jammed.
    3- SQL Agent tries, and fails to send an E-mail saying (for instance) that a database dump has completed (etc.)
    4- User posts a question to some forum, asking for help again.

    My solution: I created a job to run on a daily basis to restart the SQL Agent, and re-establish the MAPI session. Voila!

    The problem: Now we (sometimes) get the message:
    [241] Startup error: Unable to initialize error reporting system (reason: The EventLog service has not been started)
    This is incorrect - the service was never stopped!

    *sigh* No hits in Microsoft or Google - Back to the drawing board.

    SO: Can anyone think of a way to *test* the MAPI connection in a job step (without actually sending a message), so that the agent restart job is only executed if the MAPI connection is failing?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cheat?

    Write a stored procedure, something like:
    Code:
    CREATE PROCEDURE p_BFH
    AS
    
    EXECUTE master.dbo.xp_cmdshell 'NET STOP SQLAgent'
    WAITFOR DELAY '00:00:30'
    EXECUTE master.dbo.xp_cmdshell 'NET START SQLAgent'
    
    RETURN
    Trigger that as needed from a SQL Job... It seems rather rude to have SQL Agent "kick the ground out from under itself" this way, but...

    -PatP

  3. #3
    Join Date
    Feb 2005
    Posts
    3

    Cheaters Never Prosper!

    Hi - Thanks, that is pretty much but what I am doing, except that you have to "detach" the process. Else when the SQL Agent stops, it terminates all associated jobs and the NET START never gets a chance to execute!

    So I actually go out to a command shell and use the START command, which detaches the rest of the job.

    The real problem is that this results (sometimes) in the 'event log' error message I posted above. What I would like to do is only run this command if the MAPI connection is down - so I need a way to test the MAPI without sending an e-mail...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •