February 18, 2003 at 5:14 pm
When I run the following query which came right out of BOL, I get this error -- "Server: Msg 14262, Level 16, State 1, Procedure sp_verify_alert, Line 76
The specified @message_id ('55001') does not exist."
USE msdb
EXEC sp_add_alert @name = 'Test Alert', @message_id = 55001,
@severity = 0,
@notification_message = 'Error 55001 has occurred. The database will
be backed up...',
@job_name = 'Back up the Customer Database'
Any ideas?
February 21, 2003 at 8:00 am
This was removed by the editor as SPAM
September 20, 2007 at 1:39 pm
I need some help on this one too. The lease is up on our current server and I am trying to export all of the current alerts to the new SQL instance.
I scripted out the alerts into one file, but when I run the create alerts script:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'56800 - CWS Reminder E-mails'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'56800 - CWS Reminder E-mails'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'56800 - CWS Reminder E-mails', @message_id = 56800, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Baggot_email_Reminder', @category_name = N'[Uncategorized]'
END
GO
I get
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_alert, Line 76
The specified @message_id ('56800') does not exist.
Is there any way to script out the message_ids? If I copied all the data from msdb.dbo.sysalerts to the new server, would there be a problem?
September 20, 2007 at 3:00 pm
You need to run the following select:
select * from sysmessages where error > 50000
This will give you all of the custom error essages that need to be created in your new box. Then the alerts will load correctly.
January 30, 2008 at 10:03 am
In order to automate this instead of creating each message by hand, I needed to 1. make the system tables editable 2. copy sysmessages with errors greater than 50,000 to my new server.
Enable edit on system tables
EXEC sp_configure 'allow updates', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
I then created and ran a DTS package that would copy [master].[dbo].[sysmessages] records that had error > 50000 over to the new server
Disable edit on system tables
EXEC sp_configure 'allow updates', '0'
GO
RECONFIGURE WITH OVERRIDE
GO
Viola, you can then run the script to create the RAISERRORS
August 6, 2009 at 3:56 pm
What form does this DTS take to copy sysmessages? I'm running the below code and getting the error "Ad hoc updates to system catalogs are not allowed"
--Copy custom messages from other SQL Server
insert master.dbo.sysmessages
select *
from [OtherServer].master.dbo.sysmessages
where error > 50000
August 10, 2009 at 8:09 am
John,
Reread my post right above yours. You need to run the sp_configures to allow updates, then run your insert, then be sure to reconfigure to turn off the updates to system tables.
Terrence
August 10, 2009 at 4:21 pm
I did run the sp_configures as you specified. I still got the same error:
"Ad hoc updates to system catalogs are not allowed"
Why does the insert SQL need to be in a DTS package? Why not just run it from Query Analyser?
August 11, 2009 at 7:32 am
As for why in a DTS, what can I say? I'm a GUI junkie... Are you running this against SQL 2000? SQL 2005 still protects you from editing the system tables even when running sp_configure, I believe.
You could always create a script that reiterates sp_addmessage for each of the raiserrors you are wanting to recreate.
August 11, 2009 at 3:33 pm
I am running SQLServer 2005, that explains why it didn't work
Thanks for the sp_addmessages idea. I recently discovered I could use the Transfer Error Messages task in SSIS (as a GUI junkie I'm sure you would prefer this to Plan B: writing a cursor to pull from sys.messages then use sp_addmessages, which sounds like it should work)
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply