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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply