I wrote a SQL query to evenly distribute leads to the user. The query runs perfectly on SQL Server, but not Sage CRM 7.1SP2. Any feedback is welcome! Thanks!

The query is below. Here is what I did ,and got the SQL errors. The expected result is when the user click on Reassign Lead button, , the SQL query will be run to evenly distribute the leads, update the Assigned To field, and display the newly Assigned To user on Assigned To field on Workflow Progress screen.

First, in Leads Workflow, I copied pasted the SQL Query to a new Execute SQL Query action for the default Reassign Lead rule. When I clicked on the Reassign Lead button, the newly assigned field did not show up on Assigned To field on Workflow Progress screen as expected, when I further clicked on Save button, I got the SQL error:

select Lead_LeadID, Lead_AssignedUserID, lead_RoundRobinAssignFlag FROM Lead WHERE (lead_RoundRobinAssignFlag = 'Y')order by Lead_CreatedDate ASC FETCH NEXT FROM LeadAssignedId
A cursor with the name 'LeadAssignedId' does not exist

Next , I tried a different approach by creating a SQL job on SQL Server, and copied pasted the following statement to a new Execute SQL Query action for the default Reassign Lead rule.

USE msdb;

GO

EXEC

dbo.sp_start_job N'Lead Assignment';

GO

When I clicked on the Reassign Lead button, the newly assigned field did not show up on Assigned To field on Workflow Progress screen as expected as previous attempt, when I further clicked on Save button, I got the SQL error not like the previous one:

Feb 26 2015 16:22:28.819 1636 4876 1 fselectsql,time,sql,errormsg 16 select * from vWorkflowTransition WITH (NOLOCK) where WkTr_TransitionId=2313 Invalid object name 'vWorkflowTransition'

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DECLARE LeadAssignedId CURSOR FOR

select

Lead_LeadId, Lead_AssignedUserId, leadRoundRobinAssignFlag FROM Lead

WHERE (lead_RoundRobinAssignFlag = 'Y')

order by Lead_LeadID ASC;

OPEN LeadAssignedId

FETCH NEXT FROM LeadAssignedId;

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE Lead

SET Lead_AssignedUserID =

(CASE

WHEN ((SELECT TOP 1 Lead_AssignedUserID FROM Lead order by Lead_LeadID DESC) = '1')

THEN '2'

ELSE

CASE

WHEN ((SELECT TOP 1 Lead_AssignedUserID FROM Lead order by Lead_LeadID DESC) = '2')

THEN '1'

ELSE '2'

END

END), lead_RoundRobinAssignFlag = 'Y'

from Lead

WHERE CURRENT OF LeadAssignedId;

FETCH NEXT FROM LeadAssignedId;

END

CLOSE LeadAssignedId;

DEALLOCATE LeadAssignedId;

GO