[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

[gkobler 31]

I have a MSSQL Connection via FireDAC with D12 and the newest ODBC Driver 19.3.2

 

The connection to my MS-SQL Server 2017 is working.

 

Many Select Statements are running well, but when i execute an Update Statement via TFDCommand component i got the follow exception.

[FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

 

I have no problems at my developper PC, this happens on the customer PC!

 

does anyone have any experience with this? Thankfull for any hint.

Gregor

[Anders Melander 1639]

16 minutes ago, gkobler said:

Connection is busy with results for another hstmt

Did you Google the error message?

 

As the error message hints at, it's a concurrency issue; Your application attempted to call the DB server with a request while the connection was busy with a prior request. This happens if you are executing statements asynchronously.

If you have MARS enabled I believe that can also cause the problem. Disable it in your connection string.

[gkobler 31]

16 minutes ago, Anders Melander said:

Did you Google the error message?

Yes i did, but i can't get a specific answer

 

    fdcAmmann.Open;
    TRY
      fdqImportData.Open;
      WHILE NOT fdqImportData.Eof DO BEGIN

... some other code to store the data to a Firebird database ...

          fdcUpdateAmmann.CommandText.Clear;
          fdcUpdateAmmann.CommandText.Add(Format('UPDATE dbo.TDexAllocationTicketOut SET ReadDT = GETDATE() WHERE IDInt = %d', [fdqImportData.FieldByName('IdInt').AsInteger]));
          fdcUpdateAmmann.Execute; <<--- here i got the exception

        fdqImportData.Next;
      END;
      fdqImportData.Close;
    FINALLY
      fdcAmmann.Close;
    END;

when i remark the Execute as a comment, there no exception are raises. So the connection must be active and working.

 

The "TFDCommand.ResourceOptions.CmdExecMode" is set to "amBlocking", I think i have no Async commands. Otherwise D12 has made some changes.

 

23 minutes ago, Anders Melander said:

MARS

I tryed "MARS=Yes" and "MARS=No", still same exception.

[Anders Melander 1639]

Check ResourceOptions.CmdExecMode; It should not be amAsync. Sorry. You already checked that.

 

If you can, monitor the activity on the SQL Server to see if there is activity on your connection that you can't account for.

 

I still think it's MARS (I've been bitten by that one many times). I can't remember if it can be disabled on the server, but that's one thing to try (just to rule it out).

Edited December 12, 2023 by Anders Melander

[gkobler 31]

Unfortunately, I do not have access to the server, which is managed by another company. I can ask if they see anything and if they can turn off MARS on the server side.

I'm not sure if it could have something to do with D12. Because it used to work. But with D11.3 and older ODBC drivers. And there were also Windows updates on the PC and I don't know if there were changes on the server.

[Brian Evans 83]

FireDac does have tracing and monitoring, it is very verbose by default, but you can reduce which events it outputs. This could help you see what query is still open. 

Tracing and Monitoring (FireDAC) - RAD Studio (embarcadero.com)

 

 

[weirdo12 16]

Try executing you commend on your TFDConnection and see what you get:

FDConnection1.ExecSQL('UPDATE dbo.TDexAllocationTicketOut SET ReadDT = GETDATE() WHERE IDInt = :idInt', [fdqImportData.FieldByName('IdInt').AsInteger]);

You might have to fix my Pascal. I didn't test it.

 

[gkobler 31]

7 hours ago, weirdo12 said:

Try executing you commend on your TFDConnection and see what you get:

Same exception 😞

[gkobler 31]

12 hours ago, Brian Evans said:

FireDac does have tracing and monitoring, it is very verbose by default, but you can reduce which events it outputs. This could help you see what query is still open. 

Have added the TFDMoniFlatFileClientLink component, and als added the parameter to my MSSQL connection

'MonitorBy=FlatFile'

But when i open my connection, i got now the follow exception

 

 

You can try to run the program with F9, but it hangs.

 

I think FBMonitoring are buggy

[gkobler 31]

Meanwhile i have a workaround for my problem.

 

I changed my code to the follow

    fdcAmmann.Open;
    TRY
      fdqImportData.Open;

      fdmtImportAmmann.Data:= fdqImportData.Data;
      fdqImportData.Close;
      fdmtImportAmmann.Open;

      fdmtImportAmmann.First;
      WHILE NOT fdmtImportAmmann.Eof DO BEGIN

... some other code to store the data to a Firebird database ...

          fdcUpdateAmmann.CommandText.Clear;
          fdcUpdateAmmann.CommandText.Add(Format('UPDATE dbo.TDexAllocationTicketOut SET ReadDT = GETDATE() WHERE IDInt = %d', [fdmtImportAmmann.FieldByName('IdInt').AsInteger]));
          fdcUpdateAmmann.Execute;

        fdmtImportAmmann.Next;
      END;
      fdmtImportAmmann.Close;
    FINALLY
      fdcAmmann.Close;
    END;

the main changes are, first i open the FDQuery and copy the data to a FDMemTable, after that i close the FDQuery and my While loop are working with the TDMemTable dataset to with the query. So i got no exception and the UPDATE Statements is working well. Strange...

[Brian Evans 83]

Could be a difference in the number of rows in the result set with a larger result set not getting all fetched on open. Various options to fetch them all before processing. The default is usually 50 records at a time.

 

For the FireDac Monitor it looks like not everything has been setup/initialized before it is needed.

 

[weirdo12 16]

6 hours ago, gkobler said:

Same exception 😞

And you are using the MARS=Yes parameter on the connection?

 

Have you tried version 17 of the SQL Server ODBC  driver?

[gkobler 31]

1 minute ago, weirdo12 said:

And you are using the MARS=Yes parameter on the connection?

I tried Yes and No, No diffenrence same exception

 

2 minutes ago, weirdo12 said:

Have you tried version 17 of the SQL Server ODBC  driver?

I had V17 first, then i made some changes with D12 and i got the truble. After that i updated to V19.3.2

[gkobler 31]

54 minutes ago, Brian Evans said:

For the FireDac Monitor it looks like not everything has been setup/initialized before it is needed.

Possible, but in the doc no more info what i should set.

[weirdo12 16]

I'm glad you found a solution. You could have a look at this thread and see if there's anything that might help with the original problem:

 

 

[Lars Fosdal 1732]

We always run with MARS=Yes.

We also follow a different pattern with regards to inserts and updates - using stored procedures to change the contents of the database.

 

Usually the problem is that more than one operation is attempted on the same connection at the same time.

Are you certain you are not crossing variables somewhere? In you example code, there is fdcAmman and fdcUpdateAmman ...

 

[gkobler 31]

59 minutes ago, Lars Fosdal said:

Usually the problem is that more than one operation is attempted on the same connection at the same time.

Are you certain you are not crossing variables somewhere? In you example code, there is fdcAmman and fdcUpdateAmman ...

I only had the FDQuery open and FDCommand was then executed in the While Loop. Nothing else, as shown in the example, I only deleted the Firebird statemens, but this is a different FireDAC connection.

What kind of driver is it that can't even manage an open query with an UPDATE statement at the same time?

[gkobler 31]

1 hour ago, weirdo12 said:

 

I looked at the task and tried some of the things described there, but none of it helped.

[Die Holländer 20]

Did you tried the "SQL Native Client" driver?

 

[Anders Melander 1639]

17 minutes ago, Die Holländer said:

Did you tried the "SQL Native Client" driver?

AFAIK nowadays the ODBC driver is the native driver.

[gkobler 31]

22 minutes ago, Die Holländer said:

Did you tried the "SQL Native Client" driver?

Native driver is obsolete i think

[weirdo12 16]

In the version that works, what happens if you don't call fdqImportData .Close after copying the data to the TFDMemTable?

Edited December 13, 2023 by weirdo12

[gkobler 31]

52 minutes ago, weirdo12 said:

In the version that works, what happens if you don't call fdqImportData .Close after copying the data to the TFDMemTable?

i can't check that, because on the productivity server i can't play and on my developer PC it has no made any problems. So the server on my customer and my MS-SQL Express test enviroment must have some differences.

[Anders Melander 1639]

6 hours ago, gkobler said:

the main changes are, first i open the FDQuery and copy the data to a FDMemTable, after that i close the FDQuery and my While loop are working with the TDMemTable dataset to with the query. So i got no exception and the UPDATE Statements is working well. Strange...

You don't need to copy data from one FireDAC dataset to a TFDMemTable; All FireDAC datasets are mem-tables and can work in offline/briefcase mode.

All you need to do is make sure you fetch all data from the server. Do a FetchAll like it said in the other thread posted.

[Die Holländer 20]

15 hours ago, gkobler said:

Native driver is obsolete i think

This native client 11 driver was for us the only one that was usable for 2012, 2014 and maybe for your 2017 too.

We went to the latest MSSQL sever recently and for that server we needed one of the latest driver and indeed the

Native Client 11 driver  will not work anymore. We use SQL ODBC 18 at the moment.

I had a machine that was not working good either and the trick was to install the MSSQL Server Management Studio

on that machine. The Studio also installs a driver and other dependencies.