SQL Server Agent and SQL Mail for SQL 2000 Server requires a MAPI client (out of the box) to begin with — a simple, yet unavoidable fact. MAPI, for those who like to time travel is the Messaging Application Programming Interface. To send notifications with SQL 2000, say when a SQL Server (Secret) Agent job fails, the service account must have access to a MAPI client, or a mail profile to be precise. The mail profile is created with a MAPI client (Outlook or Outlook Express). MAPI, which is an MS beast that adheres to the “Embrace, extend and exterminate” practice of developing proprietary code. I won’t go into that further but will say, MAPI, like TAPI (Telephony Application Programming Interface) and SAPI (Speech fill out the rest) is something that will not lay over and die. Thankfully SQL Server 2005 embraces a fully compliant SMTP interface in Database Mail (death to SQL Mail). At any rate, inheritance being what it is, the marriage of the SQL Server Agent and MAPI was one that brought a bit of frustration today. If you ever stop and attempt to restart the SQL Server Agent to get the notifactions to work (especially on Windows 2000) beware. And I would recommend to not set the SQLServerAgent service to restart if it stops unexpectedly.
I say all of this because today I could not start the SQL Agent Service at all. It timed out every time, could not / would not with a house, start. I used “SQLServerAgent -c -v “to try start is outside of the service and recieved an error regarding a failed login to the MAPI client. I had to use Task Manager to KILL the sqlagent process many times. Service account was good, with all permissions to log files and registry. Rebuilding the profile and “ugg” Personal Folders for the mail profile did nothing.
So…is it true..dare I say..dare I? When in doubt…reboo…
no I can’t say it.
But alas, a scheduled reboot cured ALL ails. Sql Server Secret Agent started as if it had not pissed me off all afternoon with it’s inability to fire off from Service Manger, Enterprise Manager and command line.
Here is my advice if you find yourself with a Windows 2000 SQL Server 2000 SQL Server Agent that will not start. Tell your IT team (assuming you are not the IT team) you need to schedule a reboot for late in the evening, go home, get a glass of wine or a 6 pack and wait for the expected success after said reboot. Don’t waste time, as I did, dorking around with MAPI, registry, file permissions, log files, event logs, SQL Server logs, RDP, and command lines. Get the Hell out and look for the power button.