database sql query crashes labview

[ChrisReed]

When we changed from MS SQL Server 2000 to 2008 we also changed our connection string,

FROM:

Provider=sqloledb; Data Source=MC6603\SQLEXPRESS; Network Library=DBMSSOCN; Initial Catalog=ansur_dummy; User Id=xx; Password=xx;

TO:

Driver={SQL Server Native Client 10.0};Server=MC6603\SQLEXPRESS;Database=ansur_dummy;Uid=xx;Pwd=xx;

Mainly because sqloledb has been deprecated for some time now and SQL Server Native Client 10.0 is recommended for new development - fair enough.

However when we ran an SQL query eg.

SELECT *

FROM templates

WHERE test_organisation in ('Common', 'RPH') and status = 'Approved'

to return a column of data that was formatted as either VARCHAR(max) or XML it completely crashed LabVIEW 2010 on two separate computers - but any other column type eg. VARCHAR(50) returned fine. 

The VI that caused LabVIEW to crash was "DB Tools Fetch Recordset Data.vi" which outputs a 2D variant array.

However the sqloledb connection string worked fine, but obviously we don't want to go back to that string.

Is the Connectivity Toolkit not compatible with these "exotic" format types or something?

[Ever_Song]

HI Chris,

I checked some of our documentation, and it looks like the Database Connectivity Toolkit (at least for LabVIEW 2010; checking in 2011 SP1) does not support VARCHAR(max). A suggested workaround was to use VARCHAR(8000) instead. Also, if you have an error log that you could post that would be helpful to see if we have any fixes in later versions of LabVIEW.

[ChrisReed]

Hi Bill,

Unfortunately we can't use VARCHAR(8000) because we are storing files that are probably going to be longer than 8,000 characters.

I looked in my LabVIEW Data\lvfailurelog\ directory, but there was no error *.txt file present - is it stored somewhere else?  Perhaps because LabVIEW completely crashed it didn't have a chance to save one?

What I don't understand is that if we use the Provider=sqloledb instead of the Driver={SQL Server Native Client 10.0} connection string then we can read VARCHAR(max) and XML datatypes!!

Chris

[Ever_Song]

Hi Chris,

The LabVIEW crash file is found in LabVIEW’s default directory; though, this could have been changed. This KnowledgeBase talks about the location more. Though, like you said, it could have failed without an error log for some reason.

Not sure why one connection string would allow for VARCHAR(max)/XML and one would not; though, I did find this post that discusses a similar issue. I am hoping to reproduce the error on my end. Could you post a portion of your code (or a screenshot) that reproduces the error so I can make sure we are on the same page?

[ChrisReed]

Hi Bill,

Yes that link shows they were having the exact same problem (back in 2009) that we are seeing now, except they were using SQL 2005 and we are using SQL 2008, because we need to use the XML Datatype. 

I have attached a VI (in LV2010) that shows a simple query to a table in our database that has data stored in varchar(max) format.

If we use either of the Provider Connection Strings (ie. sqloledb or SQLNCLI10) it works fine.

If we use the Driver Connection String (ie. SQL Server Native Client 10.0) then it crashes LabVIEW immediately after executing the "DB Tools Fetch RecordSet Data.vi"

Chris