Why does the recordset returned by this query have a status of "closed"?

Question

I'm running the following SQL query from LabVIEW, a graphical programming language, using the built in capabilities it has for database connectivity:

DECLARE @currentID int
SET @currentID = (SELECT MIN(ExperimentID) FROM Jobs_t WHERE JobStatus = 'ToRun');
UPDATE [dbo].[Jobs_t]
SET [JobStatus] = 'Pending'
WHERE ExperimentID = @currentID;
SELECT @currentID AS result

This is the analogous code to main() is a C-like language. The first block, which has the "Connection Information" wire going into it, opens a .udl file and creates an ADO.NET _Connection reference, which is later used to invoke methods for the query.

This is the inside of the second block, the one with "EXE" and the pink wire going into it. The boxes with the gray border operate much like "switch" statements. The wire going into the "?" terminal on these boxes determines which case gets executed. The yellow boxes with white rectangels dropping down are invoke nodes and property nodes; they accept a reference to an object and allow you to invoke methods and read/write properties of that object. You can see the _Recordset object here as well.

Here's the next block to be executed, the one whose icon reads "FETCH ALL". We see that the first thing to execute on the far left grabs some properties of the recordset, and returns them in a "struct" (the pink wire that goes into the box that reads "state"). This is where the code fails. The recordset opened in the previous VI (virtual instrument) has a status of "closed", and the purple variant (seen under "Read all the data available") comes back empty.

The rest of the code is fairly irrelevant, as it's just converting the received variant into usable data, and freeing the recordset reference opened previously.

My question is, why would the status from the query of the recordset be "closed"? I realize that recordsets are "closed" when the query returns no rows, but executing that query in SSMS returns rows. Also, executing the LabVIEW code does the UPDATE in the query, so I know that's not broken either.

Any guidance on this issue would be greatly appreciated. Feel free to ask questions about things in LabVIEW; I have no trouble giving you as much information as you need to make a proper assessment.

Answer 1

I posted this on the LabVIEW subreddit, and it turns out the database connectivity VIs aren't very good with batch queries. I switched over to using methods and properties of the OdbcConnection, OdbcCommand, and OdbcDatareader classes to achieve what I wanted. Here's the code if you're interested: