April 11, 2012 at 8:22 am
/*This updates the on call operator*/
DECLARE @onCall varchar(20), @offCall varchar(20),@onCallPager varchar(100),@offCallPager varchar(100)
Set @onCall = 'On Call Operator'
Set @offCall = 'Off Call Operator'
Set @onCallPager = (select pager from DMS.dbo.Operators where onDuty = 1)
Set @offCallPager = (select pager from DMS.dbo.Operators where onDuty = 0)
print @onCallPager
print @offCallPager
IF EXISTS(SELECT name FROM msdb.dbo.sysoperators where name = @onCall)
EXEC msdb.dbo.sp_update_operator @name=@onCall,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address= @onCallPager
GO
ELSE
EXEC msdb.dbo.sp_add_operator @name=@onCall,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address=@onCallPager
GO
I'm receiving the following errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ELSE'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@onCall".
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'end'.
April 11, 2012 at 8:25 am
You have a GO (batch terminator in SSMS) in the middle of the IF ELSE.
IF EXISTS(SELECT name FROM msdb.dbo.sysoperators where name = @onCall)
EXEC msdb.dbo.sp_update_operator @name=@onCall,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address= @onCallPager
ELSE
EXEC msdb.dbo.sp_add_operator @name=@onCall,
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address=@onCallPager
April 11, 2012 at 8:27 am
awesome, thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy