Traditionally, SQL Server allowed only one active statement per connection and Attunity Connect had several mechanisms to transparently work around this limitation. Recent versions of SQL Server offered a concept of server-side cursors that allowed for multiple active statements to be multiplexed over a single connection. Implementation of server side cursors have improved over time and versions of SQL Server so using it have slightly different results on different versions (though mostly the newer versions remove restrictions).

Version 5.0.1 of Attunity Connect added support for the server side cursor this facility using a special 'temp feature' (which will probably become a standard feature in a future version).

To use server side cursors with Attunity Connect v5.0.1 and later, define a temp feature mssqlOdbcGetCursorsMode with the a value of 1 to 4 as follows:

1 -- client side cursor with multiple connection - default and previous behaviour

2 -- server side cursor with multiple connection

3 -- client side cursor with single connection

4 -- server side cursor with single connection

The initial setting of this temp feature should be '4'. If this causes problems other modes can be attempted with value '1' turning this temp feature off. Depending on the version of SQL Server, some limitations existed whereby you may need to use server side cursors with one connection per statement (for example the limitation of multiple result sets with SP).

Note that The use of server side cursors may be very significant in scenarios where data is read and updated since it allows you to make all data access within the scope of a single connection and, therefore, also the same transaction. Without the use of server side cursor, one would need to resort to distributed transaction for this kind of activity.