SQL Fetch Fails to Retrieve Recordset

[orange68]

I wrote some simple code in an attempt to query a MS SQL database and return a recordset. The query is actually a call to a stored procedure with the serial number of a top level assembly. The recordset returned should be a list of the sub-assembly part numbers in that top level assembly and their serial numbers. When I run the attached VI I do not get any data in the recordset although I have confirmed using Wireshark that the query was successfully executed and the expected subassembly data is indeed returned from the SQL server. Is there something wrong in my VI? Note that I coded the same sequence in Teststand using the built in database step types and I get the same result - Wireshark shows the successful query response data but Teststand can't seem to "find" it. Thanks for any help!

[Mark_Yedinak]

Are you sure you are not getting the data? You need to convert the data from the variant data to strings. You may have data there that is showing up as unprintable characters in the variant data. We use the following to convert the data to strings.

[orange68]

Thanks for the reply. I tried your suggestion but the data data found result is false and no records display in the recordset out indicator. I am not sure - maybe this is a firewall issue. I can see the data with Wireshark so I know it is being returned but maybe the firewall is blocking it from the Labview app. In any case I do not get any errors from Labview - everything runs as expected except that no data shows up. If I run the same query from the same physical machine using SQL Server Management Studio the data does show up in that app. Unfortunately I do not have control over the firewall on this machine to turn it off and try it.

[orange68]

FYI, I was able to run this on a machine with the firewall disabled. That also did not work - same behavior. 

[Mark_Yedinak]

That is very odd. I have not encountered that issue. We have been using the DB Toolkit extensively and have not seen this behavior.

[orange68 · Accepted solution]

FYI the solution to my problem was adding "SET NOCOUNT ON" and "SET ANSI_WARNINGS OFF" to the stored procedure script as mentioned here - https://stackoverflow.com/questions/16529032/operation-is-not-allowed-when-the-object-is-closed-when...

Oddly enough, my implementation in LabView did not produce this error but both TestStand and LabWindows did which is what led me to find the solution.