cursor not open error

Question

Post reply

cursor not open error

SSM-242970

Mr or Mrs. 500

Points: 547
More actions
August 8, 2008 at 4:08 pm

#71800

declare get_patient_id CURSOR for
select distinct patient_id from #patient_eligibility_info
OPEN get_patient_id
FETCH NEXT FROM get_patient_id INTO @patient_id1
WHILE @@FETCH_STATUS = 0
BEGIN
Print @patient_id1
EXEC [iscribedb1].[dbo].[pr_web_patient_matched_health_plans_delete] @patient_id = @patient_id1
--Sort them by rank_nbr
--self first and then by rxhub_eligibility_id
--patient_relation_code='18' refers to self
Insert into #sorted_patient_eligibility_info(patient_id,rxhub_eligibility_id,health_care_plan_id)
select patient_id,rxhub_eligibility_id,health_care_plan_id from #patient_eligibility_info
where patient_id=@patient_id1 and patient_relation_code='18' order by rxhub_eligibility_id
Insert into #sorted_patient_eligibility_info(patient_id,rxhub_eligibility_id,health_care_plan_id)
select patient_id,rxhub_eligibility_id,health_care_plan_id from #patient_eligibility_info
where patient_id=@patient_id1 and patient_relation_code<>'18' order by rxhub_eligibility_id
Insert into #patient_assoc_health_plan(patient_id,rxhub_eligibility_id,health_care_plan_id,rank_nbr)
select patient_id,rxhub_eligibility_id,health_care_plan_id,rank_nbr from #sorted_patient_eligibility_info
drop table #sorted_patient_eligibility_info
FETCH NEXT FROM get_patient_id INTO @patient_id1
END
CLOSE get_patient_id
DEALLOCATE get_patient_id
I keep getting cursor not open error.What am I doing wrong?
Thanks
ssm

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 1

It could be that you are dropping the temporary table inside the cursor. try:

Close mycursor

Deallocate mycursor

Drop Table #mytemptable

-Marlon Ribunal

SQL Server Database Administrator

SSM-242970 Mr or Mrs. 500 Points: 547 More actions · Answer 2

thanks.I tried that but still getting the error.

I get the error when I use any DML statement inside the cursor loop.doe the cursor close on any dml statement?

Thanks,

SSM

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 3

Try to get you cursor value from a table. I think the problem lies in here:

declare get_patient_id CURSOR for
select distinct patient_id from #patient_eligibility_info

Try to get "get_patient_id" from your permanent table or view, not from the temp table.

I did the same scenario for a report on SSRS without any problem.

Create Table #temptable
(
--my structure
)
DECLARE @myvariable AS [DATA TYPE]
Declare mycursor FOR
Select Distinct myvalue FROM mypermanenttable
OPEN mycursor
FETCH NEXT FROM mycursor INTO @myvariable
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temptable
-- my operation that aggregates data and inserts the value into my temp table
FETCH NEXT FROM mycursor INTO @myvariable
END
CLOSE mycursor
DEALLOCATE mycursor
SELECT * FROM #temptable
DROP TABLE #temptable

OR

redefine your cursor:

DECLARE mycursor CURSOR FOR
SELECT DISTINCT [patient_id], [patient_relation_code]
FROM mytable
-- OPEN
--FETCH
--BEGIN
-- DO ONLY ONE SET OF INSERT-SELECT HERE
-- FETCH
--END
--CLOSE
--DEALLOCATE
--Do your Sorting in the Temp Table
SELECT * FROM #temptable
DROP TABLE #temptable

-Marlon Ribunal

SQL Server Database Administrator

SSM-242970 Mr or Mrs. 500 Points: 547 More actions · Answer 4

Thanks.I can actually execute the same sp from the query anlyser from my machine pointing to the dev server without any error but the same sp throws an error when I execute from query analyser from the dev server.

my machine XP sql server version is Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

dev server is 2003

SSM-242970 Mr or Mrs. 500 Points: 547 More actions · Answer 5

phew..found the issue.

The cursor_close_on_commit was set on the server and not in my machine.That was why it executed fine from my machine but did not from the server.

Thanks.

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 6

please document the issue and solution and post it somewhere...optionally, you can post in my blog: http://dbalink.wordpress.com 🙂

SQL Server Database Administrator