SQL Fetch Recordset Data provides incomplete results

[Willy2a]

I just tried it. The same query works correctly in SQL Server.

[Brian_Beal]

Can you post a VI that has the 2D array with the data in it?  Just copy the array to the front panel of a new VI, set the values as default, and save it.  I have 8.5, so it would have to be that or earlier.  I want to say I've seen this problem before, but it has been many years.

[Brian_Beal]

Are the fields for all columns set up the same in SQL?

[Willy2a]

All fields are defined as text in the SQL database. Attached is the VI that I am using for debugging the issue. For some reason I can't get the variant array to "remember" the default valves. I even made a constant from the results but that didn't work either.

[Brian_Beal]

You need to copy and paste the variant indicator(with data in it) to a new VI, change the indicator to a control, then set the values as default(right click on the edge of the array). Save the new VI and post it.

[Willy2a]

That is what I did, but for some reason it didn't work. Attached is another version that converts the variant array to a string array. I was able to get the string array to default to the values I read from SQL.

[Brian_Beal]

I can't open your Variant Array.vi because I only have 8.5, but I was able to open the other one.  I never pass the error out cluster of the variant conversion vi because it returns errors that aren't really errors.  I don't remember all of the details, but many years ago I learned that I couldn't wire that without screwing everything up downstream.  Try to leave that unwired and see what happens. 

[SteveBale · Accepted solution]

Hi all,

Just quickly read through this post and noticed you have set some of your table columns to data type text. I have had this same issue, if you change your columns to nchar(1000) your data will be returned correctly. It appears because text columns are dynamic (I think!), LabVIEW does not allocate enough/any memory to store data returned.

As said I have had this before atleast twice on different systems now. I assume the same behaviour will occur using data type blob etc.

Thanks,

Steve

[Willy2a]

Aha! That was it. I changed all the fields in SQL to nchar(100) and everything seems to work now - for some reason changing one field to nchar(1000) had no effect, but changing them all to nchar(100) fixed it. Thank you all.

Message Edited by Willy2a on 12-22-2009 08:40 AM