sql error 15138: Error 1429: A server cursor cannot be opened...

Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..

We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.

This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.

Once in a while we will get a return from SQL ..

"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."

The OLE error code is 1429. An OLE Exception code 3604 is also returned.

When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.

Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.

Here's the really fun part...

Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.

Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.

Help?

Bill Kuhn - MCSE

The Kuhn Group, Inc.

http://www.kuhngroup.com

Did you deallocate your cursors in your code ? Would be nice to see the skeleton code, not the whole one, but the pure cursor code you implemented.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes - both cursors are closed and deallocated

Following is the entire stored procedure. The cursors are curs_Exams_pkeys and curs_Raw_CompData ..

ALTER procedure [dbo].[Comp_Data_for_Person]

@.persons_pkey int,

@.Latest_Date datetime

with recompile

SET NOCOUNT ON

declare @.ExamCount smallint

declare @.TopExam_pkey int

declare @.Exam1pkey int, @.Exam2pkey int, @.Exam3pkey int, @.Exam4pkey int, @.Exam5pkey int, @.Exam6pkey int, @.This_Exam_pkey int

declare @.ExamIndex tinyint

declare @.CurrentClients_pkey smallint

declare @.CurrentPanel smallint

-- following vars are used when FETCHing data from curs_RAW_CompData

declare @.Session_date datetime, @.TestNumber int, @.DataType tinyint, @.Result varchar(65),@.Description varchar(200), @.Class varchar(100), @.PrintFlag char(1), @.Formatted varchar(200),@.NewFlag char(1),@.CheckText varchar(200),@.PanelTestGroup varchar(200),@.PrintLevel int, @.Exams_pkey int

declare @.Current_TestNumber int, @.Current_Session_date datetime

if @.Latest_Date is null

set @.Latest_Date = '12/31/2099'

set transaction isolation level read uncommitted -- added by wsk 8/14/2006 to see if this helps

-- make table of exams that will print on this report

create table #ExamTable (session_date datetime,van smallint,name varchar(60),exams_pkey int,persons_pkey int, clients_pkey int,Questionnaire_Title varchar(254),panel smallint)

set @.ExamCount =

(select count(*) from exams join testsession on exams.testsession_pkey=testsession.pkey where persons_pkey=@.persons_pkey and session_date<=@.Latest_Date)

if @.ExamCount <7

insert into #ExamTable

select testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc

else

insert into #ExamTable

select * from (select top 5 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc) as d1

union

select * from (select top 1 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date) as d2

order by session_date desc

-- output list of exams in this data

select * from #ExamTable

--@.TopExam_pkey is key to latest exam for this person - this exam governs what testnumbers are printed in compdata

set @.TopExam_pkey = (select top 1 exams_pkey from #ExamTable order by session_date desc)

set @.CurrentClients_pkey = (select top 1 clients_pkey from #ExamTable order by session_date desc)

set @.CurrentPanel = (select top 1 panel from #ExamTable order by session_date desc)

-- get session_dates for all exams that will be reported on

set @.ExamIndex = 1

declare curs_Exams_pkeys cursor STATIC for select exams_pkey from #ExamTable order by session_date

open curs_Exams_pkeys

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

while @.@.FETCH_STATUS = 0

begin

if @.ExamIndex = 1

set @.Exam1pkey = @.This_Exam_pkey

else if @.ExamIndex = 2

set @.Exam2pkey = @.This_Exam_pkey

else if @.ExamIndex = 3

set @.Exam3pkey = @.This_Exam_pkey

else if @.ExamIndex = 4

set @.Exam4pkey = @.This_Exam_pkey

else if @.ExamIndex = 5

set @.Exam5pkey = @.This_Exam_pkey

else if @.ExamIndex = 6

set @.Exam6pkey = @.This_Exam_pkey

set @.ExamIndex = @.ExamIndex + 1

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

end

close curs_Exams_pkeys

deallocate curs_Exams_pkeys

-- output report header info

select dbo.fullnamenormalatexamdate(ex.pkey) as completename,dbo.ssnatexamdate(ex.pkey) as ssn,dbo.justdate(ts.session_date) as testdate,ts.van,ex.pid,ex.panel,

dbo.addressatexamdate(ex.pkey) as address,dbo.citystatezipatexamdate(ex.pkey) as citystatezip,adm.telephone,dbo.justdate(p.dob) as DOB, left(isnull(adm.employment,' '),2) as employmentyears,substring(isnull(adm.employment,' '),3,2) as employmentmonths,

isnull(cladm.payrollnum,'') as payrollnum,isnull(cladm.payrollnumber,'') as payrollnumber,isnull(cladm.jobcode,'') as jobcode,cl.client,cl.clientname,

clloc.location,clloc.description as locationdescription,dbo.genderatexamdate(ex.pkey) as gender,dbo.ageatexamdate(ex.pkey) as age,

isnull(cladm.memberssn,'') as memberssn, isnull(cladm.employeeid,'') as employeeid,

dbo.lastnameatexamdate(ex.pkey) as lastname,

dbo.firstnameatexamdate(ex.pkey) as firstname,

dbo.middlenameatexamdate(ex.pkey) as middlename

from exams ex join testsession ts on ex.testsession_pkey=ts.pkey

join clients cl on cl.pkey=ex.clients_pkey

join clientlocations clloc on ts.clientlocations_pkey=clloc.pkey

join clientadmin cladm on cladm.exams_pkey=ex.pkey

join administrative adm on adm.exams_pkey = ex.pkey

join persons p on ex.persons_pkey=p.pkey

where ex.pkey=@.TopExam_Pkey

create table #ReportTests (testnumber int)

insert into #ReportTests (testnumber)

select distinct medicaldata.testnumber from medicaldata where medicaldata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(medicaldata.testnumber,medicaldata.datatype,medicaldata.result) in ('P','S')

union

select distinct labdata.testnumber from labdata where labdata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(labdata.testnumber,labdata.datatype,labdata.result) in ('P','S')

create table #Final_CompData (TestNumber int, DataType tinyint, Description varchar(200), Class varchar(100), PrintFlag char(1), NewFlag char(1), CheckText varchar(200), PanelTestGroup varchar(200), PrintLevel int, Exam1Result varchar(2000),Exam2Result varchar(2000), Exam3Result varchar(2000), Exam4Result varchar(2000), Exam5Result varchar(2000), Exam6Result varchar(2000))

create index [testnumber_datatype] on #Final_CompData (testnumber,datatype)

declare curs_Raw_CompData cursor STATIC FOR

-- output medical compdata

select ts.session_date,

d1.testnumber,d1.datatype,d1.result,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.checkflag as newflag, d1.checktext,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel,d1.exams_pkey

from

((select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

)

union

(select 'LABDATA' as datatable,l.pkey as datatable_pkey,exams_pkey,l.testnumber,l.datatype,result,

dbo.referencerangecheck(e.pkey,l.testnumber,l.datatype,l.result) as checkflag,

dbo.referencerangetext(e.pkey,l.testnumber,l.datatype,l.result) as checktext,l.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join labdata l on l.exams_pkey=e.pkey

join tests on l.testnumber=tests.testnumber and l.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

))

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

open curs_Raw_CompData

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag,@.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

while @.@.FETCH_STATUS = 0

BEGIN

if @.Exams_pkey = @.TopExam_pkey -- use class,description,checktext,printflag,etc from this one

begin

if exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

update #Final_CompData

set Description = @.Description,Class = @.Class,PrintFlag = @.PrintFlag,NewFlag = @.NewFlag,CheckText = @.CheckText,PanelTestGroup = @.PanelTestGroup,PrintLevel = @.PrintLevel

where testnumber = @.TestNumber and datatype=@.DataType

end

else

begin

insert into #Final_CompData (testnumber,datatype,description,class,printflag,newflag,checktext,paneltestgroup,printlevel)

values (@.testnumber,@.datatype,@.description,@.class,@.printflag,@.newflag,@.checktext,@.paneltestgroup,@.printlevel)

end

else -- @.Exams_pkey is not = @.TopExam_pkey - only carry testnumber, datatype, and result info

begin

if not exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

insert into #Final_CompData (testnumber,datatype) values (@.TestNumber,@.DataType)

end

-- update correct Exam?Result

if @.Exams_pkey = @.Exam1pkey

begin

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)