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

ID: Q214855

The information in this article applies to:
BUG #: 18577 (SQLBUG_65)

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

SYMPTOMS

When using the SQL Executive Alert option Raise An SNMP Trap When Alert Occurs, the following error message may occur if the Simple Network Management Protocol (SNMP) service is stopped and restarted:

SQLsnmpTraps error 4 occurred while attempting to raise an SNMP Trap for alert 'Alert Name'.


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: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys And Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).


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.



STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

Additional query words: err msg command sqlexec 


Keywords          : SSrvAdmin kbbug6.50 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: March 20, 1999