Hello,

Have you checked the SQL log around the time you received this error? This will likely explain what the problem might be at the time the escalation rule tried to fire. The fields in your SQL clause look to be OK, it might be complaining abut the DATEDIFF function.

Thanks,

Ben

Hi Ben,

Thanks for the reply. I found the following in the SQL log that seemed to relate to the rule in question;

May 12 2016 13:10:01.491 14660 9968 3 execsql,time,sql 0 UPDATE WorkflowRules SET WkRl_Table=N'Opportunity',WkRl_Caption=N'Tender Deadline Approaching',WkRl_Cloneable=NULL,WkRl_Enabled=N'Y',WkRl_Image=NULL,WkRl_Channel=11,WkRl_Order=NULL,WkRl_RunInterval=NULL,WkRl_DotNet=NULL,WkRl_CustomFile=NULL,WkRl_CustomFunction=NULL,WkRl_WhereClause=N'oppo_assigneduserid=#U and datediff(d,getdate(),oppo_tenderdeadline) in (0,1,2,3,4,5) and (oppo_status = ''In Progress'') OR (oppo_stage <> ''Tender Sent'') and ((Escl_EscalationId is NULL ) and Escl_Datetime

May 12 2016 13:10:01.506 14660 9968 3 fselectsql,time,sql 0 Select Capt_CaptionId from Custom_Captions WITH (NOLOCK) where Capt_Family = N'WorkflowRule' AND Capt_Code = N'Tender Deadline Approaching'

That doesn't really give any error as such, it looks to be a log entry to the SQL log when full logging is turned on. Try deleting the old log files, switch SQL log to Errors Only, and then retry the escalation rule. When this fails, it should write to the SQL log with an error, and that hopefully will give more detail.

Try taking out this highlighted bracket.

oppo_assigneduserid=#U and datediff(d,getdate(),oppo_tenderdeadline) in (0,1,2,3,4,5) and (oppo_status = 'In Progress') OR (oppo_stage <> 'Tender Sent') and ((Escl_EscalationId is NULL ) and Escl_Datetime

If you copy the query into SSMS (and replace the #Us and #Ts with some dummy values) you'll see that there's a syntax error.

I think the bracketing in general is a little confused too - perhaps something like this would work better? (it depends what you had in mind)

oppo_assigneduserid=#U
AND DATEDIFF(d,GETDATE(),oppo_tenderdeadline) IN (0,1,2,3,4,5)
AND ((oppo_status = 'In Progress') OR (oppo_stage <> 'Tender Sent'))
AND Escl_EscalationId IS NULL
AND Escl_Datetime
AND Escl_UserId=#U