I received this alert from one of my customer's servers today:
Msg 18054, Level 16, State 1, Line 1
SQL Server Alert System: 'Severity 016' occurred on \\SQLSERVER11
DESCRIPTION: Error 778441, severity 16, state 1 was raised, but no message
with that error number was found in sys.messages. If error is larger than 50000,
make sure the user-defined message is added using sp_addmessage.
with that error number was found in sys.messages. If error is larger than 50000,
make sure the user-defined message is added using sp_addmessage.
Odd. I didn't know who or what is trying to raise an error with a msg_id that doesn't exist yet, so I ran this to search all database objects for any reference to 778441:
SELECT o.name, o.id, c.text, o.type
FROM sysobjects o RIGHT JOIN syscomments c
ON o.id = c.id
WHERE c.text LIKE '%778441%'
It returned to me this trigger:
If we look at the trigger definition, you can see where the unknown msg_id's are being called:
CREATE TRIGGER [dbo].[tblOrderItems_ITrig]
ON [dbo].[tblOrderItems]
FOR INSERT AS
/* PREVENT INSERT IF NO MATCHING KEY IN
'tblItems' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblItems,
inserted WHERE (tblItems.ItemID = inserted.ItemID))
BEGIN
RAISERROR(778441, 16, 1)
ROLLBACK
TRANSACTION
END
/* PREVENT INSERT IF NO MATCHING KEY IN
'tblOrders' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblOrders,
inserted WHERE (tblOrders.OrderID = inserted.OrderID))
BEGIN
RAISERROR(778450, 16, 1)
ROLLBACK
TRANSACTION
END
Take a look in sys.messages to confirm that they don't exist:
SELECT * FROM sys.messages WHERE message_id IN (778441,778450)
What is the fix? I just run this to create two new user defined error messages using the numeric identifiers in the trigger:
USE master;
GO
EXEC sp_addmessage
@msgnum = 778441, @severity = 16, @msgtext = 'There is no matching key
in
tblItems, the insert may not be performed.', @lang = 'us_english',
@with_log = 'TRUE';
EXEC sp_addmessage
@msgnum = 778450, @severity = 16, @msgtext = 'There is no matching key
in
tblOrders, the insert may not be performed.', @lang = 'us_english',
@with_log = 'TRUE';
With these new error messages, the msg_id numbers are now known, and the sev 16 alert goes away.
See this for more details on sp_addmessage:
No comments:
Post a Comment