cursor not open error
-
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
-
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
-
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
-
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
-
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
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
-
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.
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply