xp_snmp_raisetrap (version 6.5)

Permits a client to define and send a trap (an SNMP alert) to an SNMP client.

Syntax

xp_snmp_raisetrap server, database, error_message, message_id, severity, user, comment, date_and_time, return_status OUTPUT

where    

server

Is the name of the server where the trap (SNMP alert) originated. The server string can have a maximum of 30 characters and must have a varchar datatype.

database

Is the name of a database where the trap originated. The database string can have a maximum of 30 characters and must have a varchar datatype.

error_message

Is the message for the trap. The error_message string can have a maximum of 255 characters and must have a varchar datatype.

message_id

Is the message error number for the trap. It corresponds to an error number in the sysmessages table, which is associated with the error_message. The message_id must have an int datatype.

For more information about message_id, see sp_addmessage in the Microsoft SQL Server Transact-SQL Reference.

severity

Is the severity of message_id.. The SNMP client views this value as the trap number. The severity must have an int datatype.

For more information on severity, see sp_addmessage in the Microsoft SQL Server Transact-SQL Reference.

user

Is the name of the user raising the trap. The user string can have a maximum of 30 characters and must have a varchar datatype.

comment

Is additional text associated with the trap. The comment string often will include information that is important to interpreting the trap event. The comment string can have a maximum of 255 characters and must have a varchar datatype.

date_and_time

Specifies the date and the time the trap occurred. The date_and_time column must have a datetime datatype.

return_status OUTPUT

Returns the completion status of the statement. This parameter must have an int datatype. These are the possible status values.

Status	Description
0	The SQL agent is running and the trap was sent successfully.
2	A memory error occurred.
3	The network has not been started.
4	The SQL SNMP agent has timed out while waiting for a response from the SNMP client.
5	The SQL SNMP agent is not running.

Remarks

This procedure provides communication about SQL Server conditions to SNMP management workstation(s). You can create your own error messages, message error numbers, and severity levels, although it is recommended that you use the error messages, message error numbers, and severity levels provided in the sysmessages or sysservermessages system tables. In the Network control panel, you can define which SNMP management workstations (clients) will receive traps under SNMP service. Clients are set up on a per SQL Server basis.

Traps are usually administrative alerts about conditions or events within the SQL Server. Applications may also raise traps to notify SNMP clients of application specific conditions. For example, this procedure allows the system administrator to alert an SNMP workstation of an action that the system administrator needs to fix.

For more information about SNMP, consult your Microsoft Windows NT operating-system documentation.

Example

This example sends a trap to the SNMP management workstation.

DECLARE @db varchar(30)
DECLARE @user varchar(30)
DECLARE @dt datetime
DECLARE @retval int
SELECT @dt = getdate()
SELECT @user = user_name()
SELECT @db = db_name()
EXECUTE xp_snmp_raisetrap @@servername, @db,
'The authors table was updated', 
50001, 10, @user, 'This trap could have been raised by  an update 
trigger on the authors table', @dt, @retval OUTPUT
SELECT Status = @retval