« Xojo 2017 Release 2 | Home | Windows, Xojo and the… »

Multiple recordsets with Microsoft SQL Server

If you try to run two SQL statements on one connection, you often see this error message:

"HY000 [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command"

We regularly see the problem with Microsoft SQL Server, but it also happens with Sybase ASE and others.

To solve you can set the option "SQL_ATTR_CURSOR_TYPE" with the value "SQL_CURSOR_DYNAMIC" to get a server side cursor. This will tell the connection to use a dynamic cursor and you can have several of those.
In Xojo:

dim cmd as SQLCommandMBS
cmd.Option("SQL_ATTR_CURSOR_TYPE") = "SQL_CURSOR_DYNAMIC"

in FileMaker:

MBS( "SQL.SetCommandOption"; $Command; "SQL_ATTR_CURSOR_TYPE"; "SQL_CURSOR_DYNAMIC" )

For next plugins we will include code to make sure you can set the option on the connection and pass it down to all commands on that connection.

Alternatively you can use the flag MARS_Connection when connecting inside the connection string:

"bedlam-m\\sql2014en@test;MARS_Connection=yes"

This is global option for the connection.
18 08 17 - 10:39