none
Error 1429: A server cursor cannot be opened...

    Question

  • 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

     

     

    Tuesday, September 12, 2006 2:20 PM

All replies

  • 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
    ---
    Tuesday, September 12, 2006 2:33 PM
  • 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

    as

    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,

    (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) 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,

    (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) 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

    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

    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)

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

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

    where testnumber = @TestNumber and datatype = @DataType

    else

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

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

    else if @Exams_pkey = @Exam2pkey

    begin

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

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

    update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    else

    update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

    else if @Exams_pkey = @Exam3pkey

    begin

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

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

    update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    else

    update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

    else if @Exams_pkey = @Exam4pkey

    begin

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

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

    update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    else

    update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

    else if @Exams_pkey = @Exam5pkey

    begin

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

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

    update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    else

    update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

    else if @Exams_pkey = @Exam6pkey

    begin

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

    where testnumber = @TestNumber and datatype = @DataType

    if @NewFlag > ''

    begin

    if @Exams_pkey = @TopExam_pkey

    update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    else

    update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @TestNumber and datatype=@DataType) + char(42) + char(42)

    where testnumber = @TestNumber and datatype = @DataType

    end

    end

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

    END

    close curs_Raw_CompData

    deallocate curs_Raw_CompData

    -- knock off preceeding CR's (ugly way to do it but it works for the moment)

    update #Final_CompData

    set Exam1Result=substring(Exam1Result,2,len(Exam1Result)-1),

    Exam2Result=substring(Exam2Result,2,len(Exam2Result)-1),

    Exam3Result=substring(Exam3Result,2,len(Exam3Result)-1),

    Exam4Result=substring(Exam4Result,2,len(Exam4Result)-1),

    Exam5Result=substring(Exam5Result,2,len(Exam5Result)-1),

    Exam6Result=substring(Exam6Result,2,len(Exam6Result)-1)

    select * from #Final_CompData where printflag in ('P','S') order by printlevel,testnumber

    drop table #Final_CompData

    -- output history data

    exec dbo.Historydataforexam @TopExam_pkey

    -- output any custom field info from clientadmin table

    exec dbo.Get_Custom_ClientAdmin_Fields_TextBlock @TopExam_pkey

    -- get ILO info for ILO report

    select ts.session_date,ts.van as van,

    dbo.pidAtExamDate(exams_pkey) as pid,

    dbo.fullnamenormalatexamdate(exams_pkey) as name,

    dbo.genderatexamdate(exams_pkey) as gender,dbo.ageatexamdate(exams_pkey) as age,

    d1.testnumber,d1.datatype,

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

    d1.result,

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

    d1.oldflag,d1.checkflag as newflag, d1.checktext,datatable,datatable_pkey,d1.exams_pkey,

    (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

    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 between 2261800 and 2264999

    )

    as d1

    join testsession ts on ts.pkey=d1.testsession_pkey

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

     drop table #ExamTable  -- should not be needed

    drop table #ReportTests  -- should not be needed

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Tuesday, September 12, 2006 4:19 PM