bildwelt
E2E FORUM
E2E Bridge E2E Commerce

Error opening database cursor

E2E Forum Modeling & Development Error opening database cursor

This topic contains 9 replies, has 2 voices, and was last updated by  hatemp 2 years, 4 months ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #1928

    hatemp
    Participant

    When I try to get the handle of a cursor on a MS SQL Server 2008 database, I receive the following error:

    Error Message: 16955 “42000 Could not create an acceptable cursor. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available

    My query:

    SELECT Message.id, Message.messageTypeId, MessageType.name AS messageType, Message.messageText, Message.additionalInfo, Message.producerId, MessageQueueIdentity.name as producer, Message.batchId, Message.dtCreate AS creationDateTime FROM Message LEFT JOIN MessageReceiver ON Message.id = MessageReceiver.messageId INNER JOIN MessageType ON Message.messageTypeId = MessageType.id INNER JOIN MessageQueueIdentity ON Message.producerId = MessageQueueIdentity.id LEFT JOIN (SELECT MIN(id) AS notReleasedId, messageTypeId FROM Message WHERE isReleased = 0 GROUP BY messageTypeId) minReleasedByType ON Message.messageTypeId = minReleasedByType.messageTypeId WHERE Message.id > -1 AND ((Message.messageTypeId = 1 AND Message.id > 21 AND (notReleasedId IS NULL OR Message.id < notReleasedId))) AND (MessageReceiver.consumerId IS NULL OR MessageReceiver.consumerId = 1) ORDER BY Message.id ASC

    Does anyone know how to fix this?

    I could change to a non-cursor implementation, but in this case this is somewhat more inefficient.

    In MS SQL Management studio, the query behaves normal (and works); also when I declare a cursor based on this query.

    #1929

    Alfred
    Moderator

    Which xUML Runtime version do you use?

    Old Runtime versions did use OLE DB and newer versions do use ODBC to access SQL Server (http://sqlapi.com/ServerSpecific/SQLServer.html).

    #1930

    hatemp
    Participant

    I am using runtime 2014.14

    #1931

    Alfred
    Moderator

    Runtime 2014.14 uses SQLAPI 4.1.1 which uses ODBC as default.

    So at the moment I have no idea how-to fix this issue.

    #1932

    hatemp
    Participant

    To be sure I did an upgrade to runtime 2015.6, but the problem still arises? Anyone an idea how to fix this?

    #1933

    Alfred
    Moderator

    Please create a simple model with one SQL adapter call and a DLL script for the database table. Send both to support@e2ebridge.com .

    #1934

    Alfred
    Moderator

    Thank you for the test model. I can reproduce the error.

    If you use <<SQLAdapter>> getHandle the following
    cursor options are set to enable server cursors:
    – SQL_ATTR_CURSOR_TYPE: SQL_CURSOR_DYNAMIC
    – SQL_ATTR_CONCURRENCY: SQL_CONCUR_LOCK

    According to Microsoft documentation https://msdn.microsoft.com/en-us/vstudio/ms403282(v=sql.90) some

    SELECT statements cannot use clauses not supported by server cursors: COMPUTE, COMPUTE BY, FOR BROWSE, and INTO

    So in this case you have to stick to <<SQLAdapter>> execute. If the result set is not to large you shouldn’t run into problems.

    Is the workaround acceptable for you?

    #1949

    hatemp
    Participant

    I do not quite understand. I do not use any of the mentioned constructs in the cursor.

    When I change it to an execute form, the algorithm will be less efficient; I will get it working, but will frequently get too many rows from the database not necessary for the process.

    #1951

    Alfred
    Moderator

    I found a solution. It worked with the test model you send us.
    I added UseAPI=ODBC to the tag options of <<SQLAlias>>.

    With this option you change the SQL Server client library from OLEDB to ODBC.

    My own explanations in this thread were not completely correct.
    The default client library in SQLAPI changed but the xUML Runtime set it explicit to UseAPI=OLEDB because we had issues with some SQL Server types.
    Tests with SQL Server types TEXT, NVARCHAR(max) and UNIQUEIDENTIFIER failed with error

    HY104 [Microsoft][SQL Server Native Client 11.0]Invalid precision value

    A modeler still can override the default.

    #1952

    hatemp
    Participant

    I tried it with my more complex SQL statement and it works! Thanks for the support.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.