Microsoft ODBC and SQL Datalink Providers won't allow my LabView program to execute nested transactions with a SQL database

[Rich_ODBC]

I have a LabView 7.1 program that we are developing to be used with a testing station that will be taking configurations from LabView GUIs that we created and sending transactions to a database that holds all of the data acquisition and configuration parameters of the testing station as well as the device under test (DUT). We are using the LabView Database Connectivity Toolkit to send the transactions to the database.

I have the entire program working with an MS Access database right now using a universal data link (UDL) connection to the database. Everything works fine with the UDL datalink connection. My LabView program was designed and built using the Jet 4.0 provider which allows nested transactions. The program was designed with nested transactions to allows a modular type of program in which troubleshooting and re

-coding can be done very easily. We have all of our stored procedures and queries built in individual vi's and call them when we need them throughout each layer of the program.

However, I need to use a SQL database for the database upon integration of my software to our test system. I am having a problem trying to connect my LabView program to a SQL Server database using a data source name (DSN) connection to the Microsoft ODBC datalink provider or SQL datalink provider. When I try to connect my LabView program using any of the Microsoft Providers that I

can use to connect to a SQL Server database, the SQL provider or the ODBC provider, I am getting an error when I go to run the program stating that the SQL and ODBC providers do not allow nested transactions. Here is the specific error:

An error occurred. Following are the details:

Error Number: -2147168237

ErrorSource: Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi<ERR>Exception occurred in Microsoft OLE DB Provider for ODBC Drivers, Cannot start more transactions on this session.. in Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi

ErrorType: LABVIEW

Error Message: Exception occurred in Microsoft OLE DB Provider for ODBC Drivers, Cannot start more transactions on this session.. in Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi

Press OK to continue

An error occurred. Following are the details:

Error Number: -2147168237

ErrorSource: Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi<ERR>Exception occurred in Microsoft OLE DB Provider for SQL Servers, Cannot start more transactions on this session.. in Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi

ErrorType: LABVIEW

Error Message: Exception occurred in Microsoft OLE DB Provider for ODBC Drivers, Cannot start more transactions on this session.. in Conn Transaction.vi->Connections - List Connections.vi->GSU System Configuration.vi->ITD Program Main Panel.vi

Press OK to continue

I researched the Microsoft website and found that the Microsoft ODBC and SQL providers were designed not to allow nested database transactions, even if the end source (in my case SQL) supports them. Also, that the Microsoft Jet Provider datalink tool, which allows the nested transactions, was designed to only accept Microsoft Access databases only. See attached links:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306649

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q177138

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316872&

Does anyone know of a tool, function, or patch that can be used to allow nested transactions in the Microsoft SQL or ODBC datalink providers? So far, the only provider that we can use that allows the nested transactions is the Jet provider. Any advice on how we can get the SQL or ODBC provider to allow nested transactions would be greatly appreciated!

[mikeporter]

Can't say I have tried this because I have never found nested transactions very helpful. However, it appears that the solution (as pointed-out in the help files you referenced) is to use ADO, not ODBC or OLE DB, to connect to the database. To quote the reference http://support.microsoft.com/default.aspx?scid=kb;en-us;Q177138:

"Neither Open Database Connectivity (ODBC), nor Microsoft OLE DB Provider, supports Nested Transactions. ActiveX Data Objects (ADO) supports the feature, but only if the underlying provider exposes it. "

Mike...

[mikeporter]

Oh yes, one more thing that could be a contributing factor. The Database Connectivity Toolkit adds a lot of unnecessary complication to nearly everything. In the end it can be hard at times figuring out exactly what is going to the database, or how it's being sent.

Recommendation: lose the toolkit and just learn how to use ADO directly. It's not nearly as hard as the toolkit makes it look...

Mike...