I am facing this issue in one of our applications which uses Node.js and connects to and MS SQL server on a Windows machine. The issue is that there are multiple API calls on a page and each of them run certain queries. However, one query is common among all the API calls and this is a raw query and not parameterized at all. There are no inputs or variables in this query. For the some instances of the query I only get 10 records out of a total (lets say 136) but on other occasions I get all 136 records. Further more, if in the browser console I do replay XHR on the request that got 10 records it is able to fetch the correct number of records.
I have tried switching different versions of msnodesqlv8 as well as switching different versions of ODBC Drivers specified in the connectionstring but the issue remains the same. One thing to note is the issue not happening only with one query, it happens randomly with other queries as well and the same behavior is seen. I did see the issue to resolve if I switch to Tedious instead of msnodesqlv8 which lead me to believe that it might be an issue with msnodesqlv8.
A bit of a background, we cannot use Tedious on the server because we need Windows Authentication to access the servers with a service account. NTLM on Tedious still requires username and password which we cannot provide. The requirement is for the connection to be made using the user running the process which is service account in my case.
I have tried to look for any known issues with msnodesqlv8 but it does not seem like it is a known issue. If so, is there any solution? Or is diagnosis incorrect and it is something else causing this issue?