LabVIEW 2018 64-bit with Microsoft SQL Server Manager

[ace.bot]

Good morning, 

I developed an application for a customer using LabVIEW 2017, Lab SQL, and ODBC to read and write from a Microsoft Access 2007 database. We had to use the 32-bit version of LabVIEW for this to work. We were also writing to one SQL table through ODBC. 

A year later, the customer moved all the databases to a SQL Server, upgraded to LabVIEW 2018, and asked me to migrate all the queries to this database. They also wanted to use the 64-bit LabVIEW since we were no longer using the 32-bit Access databases. 

Installing the new version was fine, updating the ODBC connection on the machine was fine, and updating the queries in LabVIEW went fine. The program was running great in the development setting. 

I then created an executable and an installer, and deployed on another machine. We created the same ODBC connections and tested the read and write functions outside of LabVIEW. When we ran the program, all types of SQL errors, mostly relating to a specific driver. We then installed the exact driver it was looking for, but no improvement. 

So, I would like some help answering a few questions this process raised: 

1) Thumbing through the forums, it seems like ODBC is NOT the best way to connect to a SQL Database. However, that post was from 2009, so maybe things have changed. 

2) I have a hunch this is something with the runtime engine, given that the program works in Dev but not through the exe. 

The next thing we are trying is uninstalling everything from the non-dev machine, installing SSMS FIRST, then the LabVIEW program. 

[aputman · Accepted solution]

You only need a connection string to connect to MSSQL using the SQLOLEDB driver. 

A shortcut for getting a connection string is as follows:

1. Create a text file on your desktop and rename it with a .UDL extension. 
2. Double click the file to open Data Link Properties window.
3. Configure the connection using OLEDB for SQL Server.
4. After successful connection, save the settings and open the .UDL file with a text editor (Notepad, UltraEdit, etc).
5. Connection string is stored in this file
6. Now in LabVIEW, use this connection string on the DB Tools Open Connection VI.  Wire a string constant into the "connection information" terminal.

[aputman]

My method is what is shown in the PDF.  Apologies for the duplicate information.  One of these days, I'll stop making a fool of myself and start by fully reading the posts. 

The problem most likely lies in the decision to use LabVIEW 64bit.  I don't know anything about your application but my assumption is that 64bit LabVIEW is overkill and causes various other problems, like the ones you are experiencing now.  Any driver that you use will need to be 64bit.  If your customer insists on using ODBC, try to use the ODBC application in c:\windows\syswow64\odbcad32.exe.  I think this is the 64bit version of ODBC and may be required to work with 64bit applications.  If your driver is not available in this version of ODBC (but is available in the ODBC app in the Start menu), it's because you only have the 32bit version of the driver installed. 

[JensG69]

---

@aputman wrote:

If your customer insists on using ODBC, try to use the ODBC application in c:\windows\syswow64\odbcad32.exe.  I think this is the 64bit version of ODBC and may be required to work with 64bit applications.

---

Partially wrong! This path gives you access to the 32bit-ODBC-drivers. Usually I would say that a 64bit exe needs the 64bit ODBC drivers, but I do not know enough about this LabSQL-API that you are using.

Regards, Jens

[ace.bot]

This was pretty much what we needed to do. We followed your procedure for steps 1-5. The only difference was that I was using LabSQL, so I wired the connection string into the ADO Connection Open.vi provided with that package. 

We did not need ODBC after we used this method. I am sure I'll be doing this again at some point in my life. 

Thanks for the help!