Database Errors after switching to MS SQL Express Database

[ErikTheRed]

Hello, I'm having a fair bit of difficulty tracking down these errors that I'm getting. I'm not sure exactly what to provide to get the best help here, so I'll post the errors and start prepping some code snippets if they are needed.

I recently migrated from an Access database to an SQL database, my code has a number of read/writes on the same connection (i.e. I open the connection at the beginning of the software with multiple parallel loops, and utilize that connection, sometimes in parallel loops, without closing it until the end). I'm running into errors with a few of the simple recordset and properties functions as follows:

-1/18/2012 4:13:10 PM------------------1,-2147217906,NI_Database_API.lvlib:Rec Move To Next Record ®.vi:2->Tag_Data to Cluster.vi:1->Device Table DB - Return Router EUIs and Positions.vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi<ERR>ADO Error: 0x80040E0EException occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft]

Invalid bookmark value in NI_Database_API.lvlib:Rec Move To Next Record ®.vi:2->Tag_Data to Cluster.vi:1->Device Table DB - Return Router EUIs and Positions.vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi

-1/18/2012 4:15:43 PM------------------1,-2147352567,NI_Database_API.lvlib:Rec Get Recordset Properties ®.vi:2->NI_Database_API.lvlib:DB Tools List Columns.vi:2->Device Table DB - Return Device Data for EUI.vi->LE & Device Table DB - Log Location Calculation (SQL).vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi<ERR>ADO Error: 0x80020009Property Node (arg 2) in NI_Database_API.lvlib:Rec Get Recordset Properties ®.vi:2->NI_Database_API.lvlib:DB Tools List Columns.vi:2->Device Table DB - Return Device Data for EUI.vi->LE & Device Table DB - Log Location Calculation (SQL).vi->LE - Package v4 for Learning Factory (no RYG) (SQL).vi->BuzNet TopLevel 4.2.3.vi

These errors occur at random intervals, though it seems that the first one ([color=#000000]-2147217906) happens much more frequently. I've been swatting these two bugs (i.e. suppressing and logging) and ultimately was faced with a third error:[/color]

[color=#000000]1,1,"NI_Database_API.lvlib:Rec Delete.vi"<ERR>Object 0x000000 is not valid.[/color]

[color=#000000]--that ultimately killed the program.[/color]

I think it might be something with synchronous selects/writes but it's very hard to tell given the data I have...

[color=#000000]Can anyone help me with a next step?[/color]

[color=#000000]Thanks![/color]

[color=#000000]Erik[/color]

[asbo]

Did you look up what your ADO exceptions are referring to?

ADO Error 0x80040E0E refers to an invalid bookmark. Despite having used ADO before, I have no idea what this is referring to.

ADO Error 0x80020009 refers to an EOF, of sorts. It essentially means your query returned no results or you reached the end of your record set.

[Yair]

Cross-posted here - http://forums.ni.com/t5/LabVIEW/Database-Errors-after-switching-to-MS-SQL-Express-Database/m-p/1844547/message-uid/1844547#U1844547

I'm with asbo - search for the exceptions online. Also, you could try wrapping your DB actions in non-reentrant VIs, so that they don't execute in parallel, but that's just a broad statement.

[ErikTheRed]

I had previously tried googling the error codes to no avail. However, when I was generally googling around, I found a post where someone was complaining about LV using only a few recordset identifiers per connection. It turns out that recordsets were overwriting each-other when multiple parallel loops were executing queries. I opened a new connection to the database for each loop I had running, and I have not seen the error return.

TLDR: Don't perform multiple actions on a database with the same connection at once. Multiple connections to the database will allow concurrent transactions.

[asbo]

I wonder if you're truly guaranteed concurrent transactions, e.g. simultaneous operations on the same field. I don't know if it's relevant for your use case, but it might be worth the peace of mind to check it out. Thanks for posting back with your solution.