March 13, 2003 at 9:45 am
Sorry to bother you all, but somewhere between home and work I misplaced my brain. Whats wrong with this?
Declare @ServerName Varchar(30)
Declare @AlertStmt Varchar(280)
set @ServerName = (Select @@servername)
--Print @ServerName
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full msdb log'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full msdb log'
SET @AlertStmt = 'msdb.dbo.sp_add_alert @name = N''' + @ServerName + ' : Full msdb log'', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 30, @include_event_description_in = 5, @database_name = N''msdb'', @category_name = N''[Uncategorized]'''
BEGIN
EXECUTE @AlertStmt
END
GO
Server: Msg 203, Level 16, State 2, Line 17
The name 'msdb.dbo.sp_add_alert @name = N'ABCD : Full msdb log', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 30, @include_event_description_in = 5, @database_name = N'msdb', @category_name = N'[Uncategorized]'' is not a valid identifier.
I know it has to be simple. Maybe I will find my brain latter.
Thanks for you help
Stacey
Stacey W. A. Gregerson
Stacey W. A. Gregerson
March 13, 2003 at 6:46 pm
Put a print statement in the place of your execute statement and it becomes obvious... 🙂 You've got an extra space between the server name the and colon in your dynamic sql result.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 16, 2003 at 9:02 am
try EXECUTE (@AlertStmt)
Glad to see someone else does that too :).
Cursors never.
DTS - only when needed and never to control.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply