« Zwei Monate bis zur F… | Home | Video from MBS Plugin… »

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
No comments

  
Remember personal info?

Emoticons / Textile


Notify:
Hide email:

Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.