How to link SQL server to Labview with Database Toolkit

[holyna]

I'm a newbie of database but I have to use MS SQL server (2008) to hold my data. I'm fully equipped with all Labview functions, just don't know how to use it properly. I have full development suite, including database toolkit. My current labview version is 8.6.1. On the hardware side, I have cFP2220 and many modules of differnet kind.

Here's some useful link I found from the web:

1. I got the database creation procedure (pdf file) to establish the udl link.

http://decibel.ni.com/content/docs/DOC-4602 

It gives step-by-step procedure of getting connection to MS Access, and I'm able to record data to MS Access with the database toolkit. However, I couldn't figure out how to establish a udl or connection string to SQL server. In other words, I hope someone can explain in details what's the procedure to setup such a link.

2. I learned from the forum that there's a LabSQL, but it needs some knowledge of SQL statement. I would walk away from it as I'm not familar with SQL. 

3. The VI I used to record data to DB is very similiar to "Create Database Table.vi" found from the example.

To make it short, I think I just need someone who has experience with SQL server connection with Labview to show me the steps to create a link to SQL.

Many thanks, 

[David_L]

Hello,

Have you looked at the NI Example Finder under "Toolkits and Modules >> Database Connectivity?"  There are a few examples there.  Also check out this community example

[smercurio_fc]

---

holyna wrote:

However, I couldn't figure out how to establish a udl or connection string to SQL server. In other words, I hope someone can explain in details what's the procedure to setup such a link.

---

Creating a connection to SQL Server follows the same steps, but you have to use a different driver. When you create the UDL file you would need to select the SQL Server driver in the "Provider" tab. The "Connection" tab will then change to fit a connection to SQL Server. In there you would need to select server, provide whatever authentication is required based on how SQL Server authentication was set up, and select the database.

---

2. I learned from the forum that there's a LabSQL, but it needs some knowledge of SQL statement. I would walk away from it as I'm not familar with SQL.

---

If you have the Database Toolkit then you don't need LabSQL. You will, however, still need some knowledge of SQL since you have to create SELECT statements. You can look over the SQL tutorial found here: http://www.w3schools.com/SQl/default.asp.

[holyna]

Thanks David and smercurio. But I still have two concerns.

1. I saw the link David referred to before. Since I don't really need to fetch any data, or select any data, I guess I don't need to use the Select * statement? All I need to do at this point is to record the data to the SQL server.

2. Smercurio's instruction gets to the point where I've been looking for. But still I have some difficulties to setup the link. I tried two ways at Step 4. One is to follow the same procedure to select the "Microsoft Access Driver (*.mdb)" and finish until I creat such a udl link and assign the "provider, database etc" info as you said. The second way is to select "SQL server" when I created new database source. With the first way, I need to specify a name for MS Access database where the data be recorded, is this true? With the 2nd way, I put my PC's name as the server name, but I have no option to create my database name (it only gives the option to choose from "master, model, msdb, reportserver..."). There I just use default (I guess all my data will be recorded to the defaulted master database? if so, where is it?...). Then the test says successful, please see the reported manu.

Now, with both methods, I created the udl file and selected the "Microsoft OLE DB Provider for SQL Server" in the provider section, and ENTERED the server name as it CANNOT automatically recognize the server. On the 3rd option, I selected "master" as the database (there are other default options, but I guess this is the best one to select? although I'd like to have my own database name). And test connections was successful.

I thought everything should be fine, but when I ran the vi I got the following error "Error 1 occurred at "NI_Database_API.lvlib:Conn getDataToModify.vi:1"".

I totally got lost from this point and I'm not sure what's the problem...  😞

[smercurio_fc · Accepted solution]

You cannot create a database in SQL Server using the ODBC control panel or a UDL. You have to create a database using the SQL Server tools. (Actually, you can use SQL statements to create a database, which is all that really happens using the SQL Server tools. However, I'm not sure if the Database Toolkit has the VIs required to send those kinds of SQL statements.). Once you've created the database you can create tables using the SQL Server tools, or you can use the Database Toolkit VIs. 

Are you trying to run SQL Server on your computer? SQL Server is typically run on a server. Are you trying to use SQL Server Express?

Assuming you have SQL Server running, and a database created, then these are the steps required to create a UDL to connect to it:

1. In Windows Explorer create a new text file, and rename the text file so that it has a .udl extension (like that PDF said).
2. Double-click on this UDL file.
3. Change to the "Provider" tab.
4. Select "Microsoft OLE DB Provider for SQL Server".
5. Click the "Next >>" button. This changes you to the "Connection" tab.
6. From the dropdown menu select your SQL Server. If it's not listed, try clicking the "Refresh" button. If this does not populate the list, then you will need to enter it manually.
7. In the "#2" section specify the authentication method and the credentials, if required, in order to log into SQL Server.
8. In the "#3" section specify the database that will be used, which is the one you previously created using the SQL Server tools. DO NOT SELECT THE MASTER DATABASE. If you do, you'll completely screw up your installation of SQL Server.
9. Click "Test Connection" to verify that you can connect to SQL Server.

[holyna]

quick reply before I start trying your suggestions.

I have Microsoft SQL Server 2008 installed on my PC, which I will use as a local server for DAQ. I think it's not an Express version since I didn't see any "express" mentioned. I found two pieces of tools, one is "SQL Server Business Intelligence Development Studio" and the other is "SQL Server Management Studio". I guess the latter one is the tool to create a SQL server database? If so, I used it to created a database. But it seems to me I can't specify the path, so I just leave it in the default place.

Regarding Labview Database Toolkit, there are couple of functions you can write SQL statement, and the most common one I saw is "DB Tools Execute Query.vi". Please, if you have time, have a look at the toolkit manual at the following link, http://www.ni.com/pdf/manuals/371525a.pdf

from the page 6-8. It talks about some SQL statement.

I will try to do your suggestions and let you know if I can make it running. I have a feeling I'm getting very close.

Thanks, 

[holyna]

I followed all the steps but it still does not work. I think I may have screwed up the SQL installation since I tried couple of times linking to "master" database before you mentioned not to do so. I'm trying to uninstall and re-install it and see if it can help.

[holyna]

Problem solved! Many thanks, smercurio_fc! However, I do have a new question.

This problem essentially caused the error I kept encounterring. My vi was fine except the part where to specify the table name. As long as I use a constant string to specify the table name, the vi works and record data to my SQL server. But, since I will have more than 200 columns in the table and my sampling rate is 1s, I decided to us a subvi to create table by date. In other words, when the cpu reachs a new date, a new table should be created automatically according to that date. This function works fine in MS Access database. However, it gives error in SQL Server. Attached please see the DB vi including the function to create table by date. Any idea what happened?

Should I open a new thread or just keep posting under this one?

[holyna]

sorry, forgot to attach the vi diagram.

[smercurio_fc]

You should not be using spaces in table names, which it appears that you are doing. While you can, the problem is that in your select statements you need to enclose the table name with [ ], e.g., [My table name].

Besides, why would you create a new table for each date? And why would you have 200 columns? What is your database schema?