Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Cursor Invlaid State 24000

    First of all hi to everyone this is my first post on here and sincere apologies if its in the wrong place. I couldnt find anywhere to post a business objects question so I hope this is remotely close.

    I have a freehand SQL query in B.O which returns the above error, I will paste the code below. However if I copy and paste this code into SQL Management Studio it works fine.

    Any help anyone can give will be much appreciated

    Cheers

    Carl.

    CODE:

    drop table tmp1
    drop table tmp2

    SELECT EmsDynamicForms.dbo.Response.Site_ID, EmsDynamicForms.dbo.Questionnaire_Template.Title,
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID, EmsDynamicForms.dbo.Question.Question_ID,
    EmsDynamicForms.dbo.Question.Question_Display_Text , EmsDynamicForms.dbo.Answer.Text
    ,max(EmsDynamicForms.dbo.Response.Completed_DateTi me) as [CompDate]

    INTO tmp1
    FROM EmsDynamicForms.dbo.Questionnaire_Template INNER JOIN
    EmsDynamicForms.dbo.Question ON
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID = EmsDynamicForms.dbo.Question.Questionnaire_Templat e_ID INNER JOIN
    EmsDynamicForms.dbo.Response ON
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID = EmsDynamicForms.dbo.Response.Questionnaire_Templat e_ID INNER JOIN
    EmsDynamicForms.dbo.Answer ON EmsDynamicForms.dbo.Question.Question_ID = EmsDynamicForms.dbo.Answer.Question_ID AND
    EmsDynamicForms.dbo.Response.Response_ID = EmsDynamicForms.dbo.Answer.Response_ID
    WHERE (EmsDynamicForms.dbo.Questionnaire_Template.Title LIKE N'%concess%') AND
    (EmsDynamicForms.dbo.Question.Question_Display_Tex t LIKE N'%concess%')
    --AND(EmsDynamicForms.dbo.Questionnaire_Template.Que stionnaire_Template_ID = 251) AND (EmsDynamicForms.dbo.Response.Site_ID = 8653)
    GROUP BY EmsDynamicForms.dbo.Response.Site_ID, EmsDynamicForms.dbo.Questionnaire_Template.Title,
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID, EmsDynamicForms.dbo.Question.Question_ID,
    EmsDynamicForms.dbo.Question.Question_Display_Text , EmsDynamicForms.dbo.Answer.Text




    SELECT EmsDynamicForms.dbo.Response.Site_ID, EmsDynamicForms.dbo.Questionnaire_Template.Title,
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID, EmsDynamicForms.dbo.Question.Question_ID,
    EmsDynamicForms.dbo.Question.Question_Display_Text
    ,max(EmsDynamicForms.dbo.Response.Completed_DateTi me) as [CompDate]

    INTO tmp2
    FROM EmsDynamicForms.dbo.Questionnaire_Template INNER JOIN
    EmsDynamicForms.dbo.Question ON
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID = EmsDynamicForms.dbo.Question.Questionnaire_Templat e_ID INNER JOIN
    EmsDynamicForms.dbo.Response ON
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID = EmsDynamicForms.dbo.Response.Questionnaire_Templat e_ID INNER JOIN
    EmsDynamicForms.dbo.Answer ON EmsDynamicForms.dbo.Question.Question_ID = EmsDynamicForms.dbo.Answer.Question_ID AND
    EmsDynamicForms.dbo.Response.Response_ID = EmsDynamicForms.dbo.Answer.Response_ID
    WHERE (EmsDynamicForms.dbo.Questionnaire_Template.Title LIKE N'%concess%') AND
    (EmsDynamicForms.dbo.Question.Question_Display_Tex t LIKE N'%concess%')
    --AND(EmsDynamicForms.dbo.Questionnaire_Template.Que stionnaire_Template_ID = 251) AND (EmsDynamicForms.dbo.Response.Site_ID = 8653)
    GROUP BY EmsDynamicForms.dbo.Response.Site_ID, EmsDynamicForms.dbo.Questionnaire_Template.Title,
    EmsDynamicForms.dbo.Questionnaire_Template.Questio nnaire_Template_ID, EmsDynamicForms.dbo.Question.Question_ID,
    EmsDynamicForms.dbo.Question.Question_Display_Text




    SELECT tmp1.Site_ID,tmp1.Title,tmp1.Questionnaire_Templat e_ID,tmp1.Question_ID,tmp1.Question_Display_Text,t mp1.Text,tmp1.CompDate
    FROM tmp1 INNER JOIN
    tmp2 ON
    tmp1.CompDate = tmp2.CompDate

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on what version of Business Object you're using. There are some significant differences in how different versions do things, so code that works in one may or may not work in another.

    Since your Transact-SQL works in SSMS, I'm not giving it much thought. That is really the "acid test" to ensure that code is syntactically correct.

    My guess would be that your instance of Business objects can't process code with a SELECT statement that has no result set (because the result set goes into a temp table).

    The simple test to see if my assumption is correct would be to hide the code in either a stored procedure or a user defined function. If that works, then my assumption is correct. If it fails, please post whatever error messages you get.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •