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
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.
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).
I am using runtime 2014.14
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.
To be sure I did an upgrade to runtime 2015.6, but the problem still arises? Anyone an idea how to fix this?
Please create a simple model with one SQL adapter call and a DLL script for the database table. Send both to email@example.com .
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?
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.
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.
I tried it with my more complex SQL statement and it works! Thanks for the support.