Not able to access all the records in a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Not able to access all the records in a cursor

Author

Topic

aeonian_joe
Starting Member

5 Posts

Posted - 2007-12-20 : 19:12:15

I have a cursor which fetches 3000+ records. The data from the records is used to execute stored procs within the cursor loop.
But i am not able to run the stored procs on all the data fetched in the stored procs. My cursor statement is below

------------------

/*
-- Autocompletion of tasks
-- Business Accounts
*/

declare @account_id int , @workflow_tracking_id int, @task_tracking_id int

-- UPDATE TASKS CURSOR DECLARATION
declare @status_code varchar(10)
set @status_code = 'CMPL'

DECLARE sqlCursor CURSOR LOCAL FOR

select

wtt.account_id as COMX_Account_Id,
wt.workflow_tracking_id as Workflow_Id,
wtt.task_tracking_id as WBP_Task_Id

from

-- Workflow and Task Groups joins
-- Workflow and Task Groups joins
comx_user_group cug with (nolock)
inner join workflow_tracking_task wtt with (nolock) on wtt.assigned_group_id = cug.group_id
inner join workflow_tracking wt with (nolock) on wt.workflow_tracking_id = wtt.workflow_tracking_id
inner join code_workflow_type cwt with (nolock) on cwt.type_code = wt.type_code and cwt.category_code = wt.category_code
inner join workflow_task wtsk with (nolock) on wtsk.task_cfg_id = wtt.load_task_cfg_id
inner join workflow w with (nolock) on w.workflow_cfg_id = wtsk.workflow_cfg_id
inner join workflow_tracking_task_active wtta with (nolock) on wtta.task_tracking_id = wtt.task_tracking_id
inner join workflow_tracking_task_history wtth with (nolock) on wtth.task_tracking_id = wtta.task_tracking_id

-- Account and Customer joins
inner join customer cust with (nolock) on cust.account_id = wtta.account_id
inner join person per with (nolock) on per.person_id = cust.person_id
inner join custom_main_account_partner_management ceapm with (nolock) on ceapm.account_id = wtta.account_id and ceapm.account_type = 'B'
inner join custom_main_account_partner_management_detail custDetail with(nolock) on custDetail.account_id = wtta.account_id and custDetail.partner_code = 1

-- Product and portfolio joins

inner join service_order_item soi with (nolock) on soi.order_id = wt.order_id and soi.account_id = wtta.account_id
inner join account_to_prd_portfolio orderedproduct with (nolock) on orderedproduct.portfolio_id = soi.portfolio_id

inner join custom_main_prd_root orderedproductParent with (nolock) on orderedproductParent.portfolio_id = orderedproduct.parent_portfolio_id and orderedproductParent.account_id = orderedproduct.account_id

inner join account_to_prd_portfolio parentRoot with (nolock) on parentRoot.account_id = wtta.account_id and parentRoot.display_order = 2
inner join custom_main_prd_root parentRootATN with (nolock) on parentRootATN.portfolio_id = parentRoot.portfolio_id

inner join product prd with(nolock) on prd.product_id = orderedproduct.product_id

left outer join custom_main_prd_earthlink_dialup ELDial with (nolock) on ELDial.account_id = wtta.account_id and ELDial.portfolio_id = orderedproduct.portfolio_id
left outer join custom_main_prd_earthlink_dialup_dtl ELDialDtl with (nolock) on ELDialDtl.portfolio_id = ELDial.portfolio_id

left outer join custom_main_prd_earthlink_hsd ELhsd with (nolock) on ELhsd.account_id = wtta.account_id and ELhsd.portfolio_id = orderedproduct.portfolio_id
left outer join custom_main_prd_earthlink_hsd_dtl ELhsdDtl with (nolock) on ELhsdDtl.portfolio_id = ELhsd.portfolio_id

left outer join custom_main_prd_isp syn with (nolock) on syn.account_id = wtta.account_id and syn.portfolio_id = orderedproduct.portfolio_id

where
-- Filters
cug.group_id = 14
and cwt.type_code = 2
and wt.category_code <> 'Echostar'
and soi.status_code in('PENDFLFL','PENDCANCEL')
and wt.status_code = 'ERR'
and wtt.error_code <> 'Timeout expired. Th'

and wtth.history_id in
(
select max(wtth.history_id) as history_id from
workflow_tracking_task_history wtth with (nolock) inner join workflow_tracking_task_active wtta with (nolock) on wtth.task_tracking_id = wtta.task_tracking_id
where wtth.status_code in('PEND', 'PENDRETRY') and wtta.workflow_type_code = 2 and wtta.assigned_group_id = 14 and wtta.category_code <> 'Echostar'
and wtta.error_code <> 'Timeout expired. Th'
and wtta.error_code in

(
' <xml-fragment>Custo',
'UDBI0287',
'UDBI0292',
'SYST2099',
'USAD0271',
'UDBI0111',
'UAUT0548',
'UDBI0144',
'SYST2014',
'SPICE ERROR',
' <xml-fragment>Order',
' <xml-fragment>Accou',
'Failed to request SP',
' <xml-fragment>Inval',
' <xml-fragment>060-O',
' <xml-fragment>805-S',
' <xml-fragment>029-O',
' <xml-fragment>013-O',
' <xml-fragment>SO060',
' <xml-fragment>002-O',
' <xml-fragment>Error',
'No Earthlink Procduc'
)

and
wtth.additional_info Not like
'%<xml-fragment>029-ORDER OUT FOR CHANGE BY UUE6884A - CURRENT SIGNON IS: #CLECSP2%'

and
wtth.additional_info not like
'%<xml-fragment>TRANSACTION UNAVAILABLE - TRY AGAIN LATER%'

and
wtth.additional_info not like
'%The operation has timed-out%'
and
wtth.additional_info not like
'%YD-EDGEOUT (ED) exchange requires PROV fid%'

and
wtth.additional_info not like
'%KB-ONLY 1 HSD RATE ALLOWED PER RTN/CKT%'

and
wtth.additional_info not like
'%DATABASE UNAVAILABLE%'

and
wtth.additional_info not like
'%The underlying connection was closed:%'

and
wtth.additional_info not like
'%The request failed with HTTP status 503%'

and
wtth.additional_info not like
'%Error in telephoneNumber Method: No Order Information was returned for this product%'

and
wtth.additional_info not like
'%Error in UpdateSynacor method%'

and
wtth.additional_info not like
'%Timeout expired.%'

and
wtth.additional_info not like
'%No TN or Order Action Code was returned for this product%'

and
wtth.additional_info not like
'%Failed to request SPICE service%'

and
wtth.additional_info not like
'%Order not found%'

group by wtth.task_tracking_id

)

and wtt.error_code in

(
' <xml-fragment>Custo',
'UDBI0287',
'UDBI0292',
'SYST2099',
'USAD0271',
'UDBI0111',
'UAUT0548',
'UDBI0144',
'SYST2014',
'SPICE ERROR',
' <xml-fragment>Order',
' <xml-fragment>Accou',
'Failed to request SP',
' <xml-fragment>Inval',
' <xml-fragment>060-O',
' <xml-fragment>805-S',
' <xml-fragment>029-O',
' <xml-fragment>013-O',
' <xml-fragment>SO060',
' <xml-fragment>002-O',
' <xml-fragment>Error',
'No Earthlink Procduc'
)

and
wtth.additional_info Not like
'%<xml-fragment>029-ORDER OUT FOR CHANGE BY UUE6884A - CURRENT SIGNON IS: #CLECSP2%'

and
wtth.additional_info not like
'%<xml-fragment>TRANSACTION UNAVAILABLE - TRY AGAIN LATER%'

and
wtth.additional_info not like
'%The operation has timed-out%'
and
wtth.additional_info not like
'%YD-EDGEOUT (ED) exchange requires PROV fid%'

and
wtth.additional_info not like
'%KB-ONLY 1 HSD RATE ALLOWED PER RTN/CKT%'

and
wtth.additional_info not like
'%DATABASE UNAVAILABLE%'

and
wtth.additional_info not like
'%The underlying connection was closed:%'

and
wtth.additional_info not like
'%The request failed with HTTP status 503%'

and
wtth.additional_info not like
'%Error in telephoneNumber Method: No Order Information was returned for this product%'

and
wtth.additional_info not like
'%Error in UpdateSynacor method%'

and
wtth.additional_info not like
'%Timeout expired.%'

and
wtth.additional_info not like
'%No TN or Order Action Code was returned for this product%'

and
wtth.additional_info not like
'%Failed to request SPICE service%'

and
wtth.additional_info not like
'%Order not found%'

order by wtta.account_id

OPEN sqlCursor
FETCH NEXT FROM sqlCursor
INTO @account_id, @workflow_tracking_id, @task_tracking_id
print 'Number of cursor records : ' + CONVERT(VARCHAR, @@CURSOR_ROWS)
WHILE (@@FETCH_STATUS = 0)
BEGIN

--if((SELECT status_code from workflow_tracking_task with (nolock) where task_tracking_id = @task_tracking_id) <> 'CMPL')
-- begin

-- Steps to update a workflow task status in DB

-- Step 1:

exec wflow_upd_taskTrckStatusAndLogHistItem @account_id, @workflow_tracking_id, @task_tracking_id, @status_code, NULL, NULL, NULL

-- Step 2:

exec wflow_upd_wflowStatusFromTask @account_id, @workflow_tracking_id, @task_tracking_id

-- Step 3:

exec wflow_upd_taskTrckActive @account_id, @workflow_tracking_id, @task_tracking_id

--end

FETCH NEXT FROM sqlCursor
INTO @account_id, @workflow_tracking_id, @task_tracking_id

END
CLOSE sqlCursor

--clean up variables
DEALLOCATE sqlCursor

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-25 : 17:38:57

What line(s) are causing the problems?

What is the data causing the problem?

We don't know everything you do matey!

George
<3Engaged!

aeonian_joe
Starting Member

5 Posts

Posted - 2007-12-26 : 12:34:07

thanks for your reply!!!

there is no specific line or data causing this problem...

some time the 10 records are affected or 200 records etc..

when i printed @@CURSOR_ROWS it printed out the correct number of records

if u carefully follow my sql stmt you can see i am using the values fetched from each row as input parameters to stored procedures...the problem is the stored procedure is not getting executed for the number of records present in @@CURSOR_ROWS.

Am i clear. Please let me know if u need more information.

Appreciate your quick response as this is for a production issue in my project.

Thanks
Joe

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-26 : 17:01:26

Are the stored procedures you are calling updating the tables your cursor is selecting from? If so you might have a "side-affect." Here is an example, if you run it you will notice that the cursort only does one iteration not two:

CREATE TABLE Foo (ID INT, Bar VARCHAR(50))

INSERT Foo
SELECT 1, 'Hi'
UNION ALL SELECT 2, 'Bye'
UNION ALL SELECT 3, 'Hi'
UNION ALL SELECT 4, 'Bye'

DECLARE @ID INT

DECLARE sqlCursor CURSOR LOCAL FOR 
SELECT
	ID
FROM
	Foo
WHERE
	Bar = 'Bye'
OPEN sqlCursor
FETCH NEXT FROM sqlCursor
INTO @ID

print 'Number of cursor records : ' + CONVERT(VARCHAR, @@CURSOR_ROWS)
WHILE (@@FETCH_STATUS = 0)
BEGIN
	PRINT 'In Cursor'
	
	UPDATE Foo
	SET Bar = 'Hi'
	WHERE Bar = 'Bye'
	
	FETCH NEXT FROM sqlCursor
	INTO @ID
END

CLOSE sqlCursor
DEALLOCATE sqlCursor


DROP TABLE Foo

aeonian_joe
Starting Member

5 Posts

Posted - 2007-12-26 : 18:47:45

yes the stored procs update the tables used in the select stmt of the cursor.

all the records fetched in the select stmt is unique.

but when i run the same sql cursor on fewer number of records say abt 100 then the stored procs runs perfectly.

Apart from my update statements there are also other process using the same tables. I guess there is a lock created here.

Is there any ideal way to lock the tables when i run the above sqls.

Thanks
Joe

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-28 : 17:48:22

So, if you run the query you posted above:

SELECT 
	wtt.account_id as COMX_Account_Id,
	wt.workflow_tracking_id as Workflow_Id,
	wtt.task_tracking_id as WBP_Task_Id
FROM 
...

do you get the same number of rows if you run

SELECT DISTINCT
	wtt.account_id as COMX_Account_Id,
	wt.workflow_tracking_id as Workflow_Id,
	wtt.task_tracking_id as WBP_Task_Id
FROM 
...

I still think you are getting side-affect, but I don't know much more about the behavior of curors as I never use them. Nor do I know anything about the stored procedures that are being called.

That being said, the easy solution would be to not use a cursor. One option could be to select into a table (temp or perm) and use a WHILE loop to process the table making the needed sproc calls...?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 19:33:18

I agree with the side affect comment. Just change to this:

DECLARE sqlCursor CURSOR LOCAL STATIC FOR

Be One with the Optimizer
TG

aeonian_joe
Starting Member

5 Posts

Posted - 2007-12-28 : 19:39:00

Thanks all for the replies.

Yes both the select statements returned the same number of records.

I didn't try the STATIC one..can you please explain what that is??

I went out and figured out a way to do this.

I did a select top 100 wtt.account_id etc... and put this into a scheduled dts package to run every 30 mins. this solved the issue.

Thanks all for the help.

But still i don't know why i couldn't do it in a single step. I'll do my investigation and post it here so it would be helpful to all !!!

Thanks again and Wish you all a Happy New Year 2008.

Joe

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 19:46:20

>>I didn't try the STATIC one..can you please explain what that is??

From Books Online:

quote:
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

Be One with the Optimizer
TG

aeonian_joe
Starting Member

5 Posts

Posted - 2007-12-28 : 23:38:22

Thank you. Appreciate your response.

Joe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources