Import or link SQL server tables

Whether you are importing from or linking to Microsoft SQL Server tables, the process is essentially the same. The following instructions describe how to link to an existing PI AF table using the PI System Explorer.

1. To browse to the target PI AF table, display the Library pane, expand the Tables node, and click the desired table.

   Table properties display in the right pane.

2. Click Link.

   The Table Link window opens.

3. Click the Connection down arrow, then click Build.

   The Data Link Properties window opens.

4. On the Provider tab, select SQL Server Native Client 11.0.

5. On the Connection tab, configure the SQL Server instance that contains the database to which you want to connect.

6. Configure authentication:

   • For Integrated security, select the Use Windows NT Integrated Security option. See Authentication for linked tables for more information.

   • For SQL Server security, select the Use a Specific user name and password option. Then, enter the SQL Server Login name in the User Name field. Click to unselect the Blank password checkbox and specify that a password be required. Enter a password in the Password field.

     Note: Ensure that support for external PI AF tables for non-impersonated users has been enabled with the afdiag /DTImp command.

7. Click the Select the database down arrow and choose the database that contains the table to which you want to connect.

8. To verify that connection settings work, click Test Connection. If the settings are correct, PI System Explorer displays a success message.

   Note: Test Connection verifies that the account with which the PI System Explorer is running has access to the specified database. However, if you choose Windows NT Integrated Security in the Table definition and choose the No additional security context option for table connection security, the account associated with the PI AF Server Application Service is used to connect when a user displays the data by viewing the Table tab.

9. Click OK.

10. If prompted, enter a password and click OK.

    You are returned to the Table Link window.

11. In the Query field, specify the SQL query that returns the desired data and click OK.

    If the connection string requires a user ID and password, select the Supply password option. To change the password used connect to the SQL table, click the Change Password button and enter the new SQL login password. This replaces the value previously entered in the Password field.

12. To display the data retrieved by the query, view the Table tab.

13. On the toolbar, click Check In to check in and save the table connection.