Error using Database addons with SQL query.

[ww.brown]

I get a LabVIEW error when I provide the following SQL query to the NI database VI: ..vi.lib\addons\database\Connection.llb\Conn Execute.vi. 

declare @SR_ID table ( script_result_id uniqueidentifier )

insert into @SR_ID
select script_result_id from SCRIPT_RESULT
where script_result.software_name = '%s'

SELECT distinct STEP_RESULT.STEP_TYPE
FROM STEP_RESULT INNER JOIN @SR_ID s ON
STEP_RESULT.SCRIPT_RESULT_ID = s.script_result_id

The error is:

Error -2146824584 occurred at NI_Database_API.lvlib:Rec Destroy - Connection.vi->Database_Query Database with flattened string reply.vi->ADUTS-RPT Query DB.vi->ADUTS-RPT Get Step Names.vi->ADUTS-RPT Top.vi

Possible Reason(s): Exception occured in ADODB.Recordset: Operation is not allowed when the object is closed.

The query works fine when given directly to my SQL server.

Any ideas on this problem would be most appreciated.

[Tim_W.]

Hi wwwbrown,

What version of LabVIEW and the Database Connectivity Toolkit are you using? It appears that the behavior you're describing was a bug that got fixed in the 2009 release (http://digital.ni.com/public.nsf/allkb/B323B3FAE93E3329862575EF00621907)

Tim W.

Applications Engineering

National Instruments

http://www.ni.com/support 

[ww.brown]

Thanks for your reply.  I am using LabVIEW 8.6.  Actually, the error occurs after the Conn Execute call when I attempt to access the ADODB._Recordset using the Rec getData.VI and the _Recordset.GetStrings property.  The property node returns the error shown.  Is this the use case that was fixed in LV 2009?

[Tim_W.]

It looks like the bug is related to ADODB objects. I found a related KnowledgeBase article that was written about the same issue, but never published for some reason. Here's what it has to say about your error:

"This error occurs when a query is run on the database that does not return any recordset data, such as a delete command.  In this case, when you encounter the DB Tools Fetch Recordset Data VI, the state of the recordset is "closed" and produces the error.

To avoid the error, you can check for the state of the recordset and make sure that it isn't closed. To do this, use the DB Tools Get Properties VI and check the state from the properties cluster.  An additional, or alternate, check would be to check for the beginning of file and end of file properties.  If both BOF and EOF are both true, there is no data in the recordset.  

If you open the example program, Database Fetching, you can see that they are using both of these checks.

In versions of the toolset prior to version 1.0.2, these checks were built in.  This causes code without the checks to return this error if the toolset is updated from a previous version."

Tim W.

Applications Engineering

National Instruments

http://www.ni.com/support 

[ww.brown]

I run this same query using MS SQL Server 2005 and it returns in 13 seconds with 2 dozen entries.

I believe the problem is in the "declare" done prior to the main query (SELECT).  I have tried splitting this compound query into 2 separate calls to Conn Execute.vi with no luck.

Perhaps one of your SQL guru's might have an opinion.

Thanks.