Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: SQL Server connection - MARS - Multiple Result Sets


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Mar 17, 2016 4:22 AM Last Post By: Dmitry Arefiev
Pascal Bellebou...

Posts: 21
Registered: 6/12/04
SQL Server connection - MARS - Multiple Result Sets  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 16, 2016 8:06 AM
Here is one very annoying issue we've juste discovered when moving to FireDac (From old BDE) :
We are using "SQL Server" Client (the one installed on the system by default) to connect to a SQL Server (MSSQL) database.
Trying to open 2 Datasets (FDQueries) at the same time give the following error :
[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.

Ok, then we should enable MARS....but we never could acheive this.
The only way is to install a recent SQL Server client....OK, but :
- We have hundred of clients using thousand of computers
- It used to work correctly with the "good" old BDE

Any way to avoid a SQL Client installation ?

Pascal.

Edited by: Pascal Bellebouche on Mar 16, 2016 8:06 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL Server connection - MARS - Multiple Result Sets  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 17, 2016 1:54 AM   in response to: Pascal Bellebou... in response to: Pascal Bellebou...
Try to change:
* FetchOptions.Mode to fmAll:
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDFetchOptions.Mode
* FetchOptions.CursorKind to some non-default value:
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDFetchOptions.CursorKind

But best will be to install SQL Server Native Client.

--
With best regards,
Dmitry
Pascal Bellebou...

Posts: 21
Registered: 6/12/04
Re: SQL Server connection - MARS - Multiple Result Sets  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 17, 2016 3:53 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Try to change:
* FetchOptions.Mode to fmAll:
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDFetchOptions.Mode
* FetchOptions.CursorKind to some non-default value:
http://docwiki.embarcadero.com/Libraries/Seattle/en/FireDAC.Stan.Option.TFDFetchOptions.CursorKind

But best will be to install SQL Server Native Client.

--
With best regards,
Dmitry

Thanks for your answer.
We did try to enable theses options, it did fixed our issue.

Indeed, we are currently deploying the SQL Native Client with our application (via a silent installation of the MSI), since fmAll is not really acceptable with many rows.

Thank you anyway.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQL Server connection - MARS - Multiple Result Sets  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 17, 2016 4:22 AM   in response to: Pascal Bellebou... in response to: Pascal Bellebou...
Indeed, we are currently deploying the SQL Native Client with our application (via a silent installation of the MSI)

This is right decision.

since fmAll is not really acceptable with many rows.

Yes and no. No, because "many rows" means you need
all of them on the client. If you does not need all of them,
then your query is not correct, because you will load the
server more than needed.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02