BUG: SQL Executive Alerts Using SNMP Traps Stop Working After the SNMP Service Is Restarted (214855)

WORKAROUND

There are two workarounds to this problem; they can be used together or independently. The first workaround uses the xp_snmp_raisetrap extended stored procedure to send alerts. This procedure continues to work when the SNMP service is stopped and restarted. The second workaround ties the SNMP and SQL Executive service together. In this case, when SNMP is stopped, SQL Executive is also stopped.

Workaround 1

Create an alert with an alert definition for either an error number or a severity. To do this, perform the following steps:

1. From the Server Manager window, select a server, and then from the toolbar, click the Manage Alerts button.
2. Click the New Alert button.
3. In the Name box, type a name to assign to this alert.
4. To define the conditions that will cause an alert, specify either an error number or a severity level.
5. To specify the action that the system will take when the defined alert occurs, complete the Response Definition options. Instead of selecting the Raise An SNMP Trap When Alert Occurs response definition, create a new task to execute. The task is a Transact-SQL statement to execute a stored procedure that will accept either an error code or severity number as a parameter. The stored procedure executes the xp_snmp_raisetrap extended stored procedure.
6. Click OK to finish creating the alert.

The order of the alerts will determine which alert gets executed. For example, if an alert for severity 17 is created before an alert for an error number with a severity of 17, then the alert for the error number will never execute.

The following is an example of a stored procedure definition:

create procedure sp_trap @errormsg int as
DECLARE @db varchar(30)
DECLARE @user varchar(30)
DECLARE @msg varchar(30)
DECLARE @dt datetime
DECLARE @retval int
SELECT @dt = getdate()
SELECT @user = user_name()
SELECT @db = db_name()
SELECT @msg = 'Test SNMP message error: '
select @msg = @msg + convert(varchar(5), (@errormsg))
EXECUTE master..xp_snmp_raisetrap @@servername, @db,
@msg, @errormsg, 10, @user, 'This trap was a test message', @dt, retval
OUTPUT
SELECT Status = @retval
				

The following is an example of a Transact-SQL statement with a specific error number {for example, 1105} for a task to execute:

exec sp_trap 1105
				

Workaround 2

Because restarting the SQL Executive service corrects the failing SNMP alert, another workaround is to tie the SQL Executive service to the SNMP service. Therefore, if the SNMP service stops, the SQL Executive service is also stopped. Because of the dependency on SNMP, the SQL Executive service will not restart until the SNMP service is running. This workaround will only work if the SNMP service is stopped through the Windows NT Control Panel, a net start or a net stop command. If the SNMP service is abnormally terminated for some reason (for example, with Kill.exe from the Windows NT 4.0 Resource Kit), the SQL Executive service will continue to run. This workaround involves making a change to the system registry.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.


To implement this workaround, perform the following steps:

1. Start the Registry Editor (Regedt32.exe) and navigate to the following key:
   

         HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLExecutive
   						

2. Click the SQLExecutive subkey. On the Edit menu, click Add Value.
3. Enter the following:
   

         Value Name: DependOnService
         Data Type:  REG_MULTI_SZ
         Data:       SNMP
   						

4. Click OK and close the Registry Editor.
5. Shut down and restart the computer.