Microsoft KB Archive/214855

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

Article ID: 214855

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q214855



BUG #: 18577 (SQLBUG_65)

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



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: 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:   Start the Registry Editor (Regedt32.exe) and navigate to the following key:

     HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLExecutive  Click the SQLExecutive subkey. On the Edit menu, click Add Value.  Enter the following:

     Value Name: DependOnService Data Type: REG_MULTI_SZ Data:      SNMP  Click OK and close the Registry Editor. Shut down and restart the computer.</li></ol>

<div class="status_section">

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

Additional query words: err msg command sqlexec

Keywords: kbbug kbpending KB214855

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.