Why would an SQL query not return data from only one field in table

[dacad]

Running LV 2013 and connecting to tables on an SQL 2005 server. Hoping to upgrade but big company IT makes it a major ordeal.

My system starts out by running a query for a single column in all rows to generate a list of part numbers.  No problem there. I then select a single part number and another query runs to select and return all the other information for that part number which is spread among multiple tables.

All works fine except for getting the data from the primary table.  One field in particular returns empty even though there is data in the table. I went through the design of the table with a fine tooth comb, even deleting the field and recreating it to make sure there was not some obscure setting I had inadvertently changed.  No good.

I rearranged the fields and things got interesting.  When I moved it higher up, all the fields that were not  below it returned no data until it got to the field that was originally below the one in question.  Placing it at the end allowed all the other fields to appear but it still came in blank.  To verify what was being returned, I probed the variant array coming straight out of the query vi form the db connectivity toolkit. The array showed where the field should have been but no data was present.

I have an MS Access front end that can run queries on the linked tables all day and all the data is returned. 

Looking on the SQL server side, I started looking at the field types.  I have several varchar fields, no problems there. A couple float fields, smallint fields, bit fields and datetime fields all with no problems.  The field in question started out as an ntext field. I have two of them in the table.  On looking this up online, I read where at some point in the future, the ntext field was going to be discontinued and I should use the varchar(MAX) field in its place. So I changed both fields to varchar(MAX).  Still didn't work.

For ref, the equivalent Access fields are:  varchar = text,  float = number - double precision,  smallint = integer,  bit = yes/no,  datetime = yes, date & time, and ntext or varchar(MAX) = the memo type field.

Reaching for straws, I tried multiple changes and when I changed one of the varchar(MAX) fields to varchar(2000), (Defines the maximum number of character spaces in the field).  Behold,  it eliminated the problem.

So.....  I have resolved the issue but I really want to understand if this a limitation? bug? known condition? or what. 

It appears that the LV database connectivity toolkit cannot read from more than one ntext or varchar(MAX) field in a given table.

I could read multiple memo fields from Access with no issues and I can read all the SQL fields queried in an Access front end with the same query so it leaves me to believe that there is an issue from LV in reading the SQL tables.

Anyone had any similar experience or knowledge?  While I have solved the immediate issue I want to understand what the root cause is for the problem so that I know what, if any limitations I have to work with going forward.

[dkfire]

This is just what I think:

The problem you are getting is linked to the size of the data.

In the Database Connectivity Tool Kit, LabVIEW has to link the data type from ADO, the sub-system used to connect and talk to the database, and a LabVIEW data type.

I had a problem with inserting large XML data into a SQL2008 server. (In newer SQL server there is a XML data type.)

I needed to change the mapping in one of the tool kit function to be able to insert the large data. This might be some releated to your problem.

[dacad]

Hmmmm.  The data in the fields currently is pretty small, less than 200 characters.  Thing is, it never popped an error, it just didn't return the contents of the field. But it did return the contents of the next field which was the same data type, ntext to start then varchar(MAX) atter I changed them.

varchar(MAX) allows up to around 2Gb of space for the field.  It's just not clear why it would not read the data from the first field but it would from the second field. After I changed the first field to varchar(2000) it read the field in just fine. Going from 2Gb to 2Kb does make a difference but it still read in the second one. That's what has me stumped.

[dkfire]

And you are sure you don't get a warning (Not an error, only a warning) in the Errorcluster ?

[dacad]

No Error, No Warning.  I replicated the condition this morning.

My table appears as shown below.  The field that is Data Type  varchar(2000) was originally varchar(MAX) like the one below it.  When both are (MAX) the first field returns no characters at all.  When I change the first one to (2000) then it returns the data accordingly.

I am using pretty uncomplicated code as shown below also.  I am looking at the indicator straight out of the databse call so this info is sinply not even getting to LV.  I have run in highlight mode and no errors, no warnings. It simply returns no data for that field.  I tried feeding the table names into the Select Data and no change. I tried different rows (part numbers in my case) and no change.  I deleted the field in the table and re-created it from scratch and repopulated it and no change.

Since this didn't affect the data that came into my MS Access front end, I can only think at this point there is something in the LV database vi's that is causing it. While I have a workaround that will work fine for me on this project, there may be a time when I need multiple (MAX) fields and I want to eventually figure out what might be causing it.

[Yamaeda]

If you run the query on the sql server directly i think you'll get a warning/error. I've had similar problems, and the error was something like "result can be bigger than 8192 bytes", which is some limit. That's probably why it can only handle 1 varchar(max), as 2 would be too big.

/Y

[Sam_Sharp]

I think it's a limitation of the ODBC driver for MSSQL rather than a fault with LabVIEW. Which ODBC driver are you using? 

The following google search seems to agree with me that certain versions of the MSSQL ODBC driver don't support varchar(max) fields properly (probably because it allocates a buffer or something to that effect): https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#safe=off&q=odbc%20drive...

The solution seems to be to use the SQL Native Client driver instead.

[dacad]

I did run the query in the SQL management system.  No errors when they were both set to (MAX).   The OBDC Driver is a consideration I had not taken into account.  I looked and these are the drivers on my system currently.

Since I configured my initial DSN configuration using Access, I don't know which driver it used.  I can go back and create two alternate DSN configurations selecting the two client drivers respectively and try both and see what happens.

While I give this a try and report back, the fact that I am using the same DSN config for LV as I am for my Access front end, seems like if it was the driver, it would affect both.

Should be able to try it this afternoon.