MSSQL Live Driver Information
Source Properties
When setting up the MSSQL data source, you'll be asked to specify…
| Option | Use |
|---|---|
| Host | The host name or IP address of your MySQL server |
| Port | The port that the MySQL server runs on (see Finding the Port Number below) |
| Database | The name of the MySQL database to use |
| Username | The username (with read-only access) to use to connect |
| Password | The password that goes with the above username* |
| Table | The name of the table to query |
| Lookup Field | The field that gives you the 'name' of the value - used to connect a single ARDI property with a single row in your database |
| Date Stamp | The name of the date column in your database. |
| Value | The name of the column that contains the value you are looking for |
| Filter | Optional - An additional filter to add to the SQL query |
| Date Format | The date format that the Date Stamp column is in. |
| Date Distribution | If the history is recorded in a synchronised fashion. |
| Timezone | The timezone that the dates are stored in. Normally these are your local timezone, or UTC |
* Note that this password is visible to all administrative users, which is why you should ensure that the user account is read only and can not make changes to the underlying database.
Link Properties
These are the options that appear when you link a property to a text data source…
| Option | Use |
|---|---|
| Lookup | The value of the lookup column |
How It Works
As an example, let's look at a simple database layout…
| Date | Name | Value |
|---|---|---|
| 2016-01-01 12:15 | ALPHA_TEMP | 24.04 |
| 2016-01-01 12:15 | BETA_PRESS | 2891 |
| 2016-01-01 12:15 | BETA_TEMP | 16.29 |
| 2016-01-01 11:15 | ALPHA_TEMP | 29.04 |
| 2016-01-01 11:15 | BETA_PRESS | 2231 |
| 2016-01-01 11:15 | BETA_TEMP | 20.29 |
If we wanted to connect to the latest values in this table, we would set the columns of our data source so that…
- Lookup Field = Name
- Value = Value
- Date = Date
Then, if we had an asset called “Beta”, when we linked the “Pressure” property to data, we would set…
- Lookup = BETA_PRESS
The driver checks for the Lookup Field that matches the Lookup of the data link and returns the value in the Value field.
So in the case above, the driver would check the first three rows, and set the pressure of the Beta asset to 2891.
Finding the Port Number
You will often find several instances of Microsoft SQL Server running on a single machine. Each of these instances have a unique name, usually added onto their host-name.
For example, one instance might be \\SQLSVR\\RECORDINGDB
While ARDI can use your host-name (ie, SQLSVR in the example above), it doesn't use the instance name to connect. You'll need to find out what port number to use.
This is best done using SQL Server Configuration Manager, which can be downloaded free-of-charge. Search using your preferred search engine for 'SQL Server Configuration Manager <version>', where 'version' is the edition of SQL server you have (2012, 2016 etc.).
Follow the steps below…
1. Under SQL Server Network Configuration, locate the instance you want to connect to and double-click on 'TCP/IP' on the list to the right.
2. Make sure 'Enabled' is set to 'True' to allow TCP connections to your database.
3. Under the 'IP Addresses' tab, scroll to the bottom and under IP All, check or choose the port number that this instance uses.
Hit 'OK' when you're done, and your sever should now be accessible to ARDI.