We are using FireDac to connect to SQL Server. We have several queries which run without problems in general. But we have got the problem that they get the status Suspended in the activity-monitor from Microsoft SQL Server Management Studio if the record count of those queries is bigger than 50.
The queries with status Suspended do not hurt as long as the data they show is not edited or something like that. But if you want to change data that a suspended query is using, then the complete table is blocked.
For example we have a table with different tasks and another table with different notes for each task. Furthermore the tasks are connected to different users. Additional we have a overview which shows all the tasks for one user.
If the query for this overview contains more than 50 records (even if there are just shown 10 entries by a grid) then it will get the suspended-status. If the same user wants to add a new comment to one of these tasks by adding a note, in that moment he stores the new note the server blocks the complete task table.
We found the solution that the user can scroll through the complete overview of the tasks and then add a new comment but why is that necessary? Does anyone have an idea?
Once the users as scrolled to all his tasks and was at the end of the grid, the suspended status from the query disappears and the query has finished.
Is there anybody who has some experience with that kind of problem?
I do not think that it is a SQL Server problem as the same SQL statement does not generate a suspended query if we run it in Microsoft SQL Server Management Studio.
Regards Maggi