Solution: PI SQL Client (OLEDB) and RTQP Configuration

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Jul 1, 2019
Version 7Show Document
  • View in full screen mode

This document contains the answers to the Exercise: PI SQL Client (OLEDB) and RTQP Configuration . The screenshots in this document are of either PI SQL Commander's Data Link Properties dialogue box or the PI SQL Client Admin Guide .

 

Note: Each of these connection strings use the [Location] parameter to store the [AF Server name] and the [Data Source] parameter to store the [AF Database] name, but it is sufficient to leave [Location] blank and specify the [AFServer\AFDatabase] in the [Data Source] parameter.

PI OLEDB Provider and PI OLEDB Enterprise do not use the [Location] parameter since the connection is for the entire server, but with PI SQL Client connections being AF database specific, the optional usage of the [Location] parameter to specify where the [Data Source] is located was introduced.

 

For each of these connections, when right clicking on [PI SQL Client] in the [OLEDB Data Sources] section of PI SQL Commander's [Object Explorer], the PI SQL Client Data Link Properties menu's third tab shows the current value for each of the OLEDB parameters. This can be very helpful in confirming that the correct OLEDB parameter name and value are being added to the connection string.

 

1. Connecting to an AF Database named "AFDB1" on an AF Server named "AFSRV01" using Windows explicit login. The Windows domain username is "OledbUsers" and password is "5q1i5|=v|\|".

answer: Provider=PISQLClient.1;Data Source=AFDB1;Password="5q1i5|=v|\|";User ID="OledbUsers";Location=AFSRV01

From the PI SQL Client Admin Guide:

 

From PI SQL Commander:

 

2. Connecting to an AF Database named "AFDB2" on an AF Server named "AFSRV02" passing the user's context via SSPI, setting the timezone to "(UTC+04:30) Kabul".

answer: Provider=PISQLClient.1;Data Source=AFDB2;Integrated Security=SSPI;Location=AFSRV02;Time Zone=(UTC+04:30) Kabul

 

From PI SQL Client Admin Guide:

 

From PI SQL Commander:

3. Connecting to an AF Database named "AFDB3" on an AF Server named "AFSRV03" passing the user's context via SSPI, setting the query context to query from October 1, 2018 as opposed to the current time.

answer: Provider=PISQLClient.1;Data Source=AFDB3;Integrated Security=SSPI;Location=AFSRV03;Query Date=2018-10-01

 

From PI SQL Client Admin Guide:

 

From PI SQL Commander:

 

4. Connecting to an AF Database named "AFDB4" on an AF Server named "AFSRV04" passing the user's context via SSPI, disallowing connections to be made with NetTcp.

answer: Provider=PISQLClient.1;Data Source=AFDB4;Integrated Security=SSPI;Location=AFSRV04;Protocol Order=Https/Soap:5464

 

From PI SQL Client Admin Guide:

 

From PI SQL Commander:

 

5. Connecting to an AF Database named "AFDB5" on an AF Server named "AFSRV05" passing the user's context via SSPI, setting the timeout for connections to be 60 seconds and the timeout for query execution to be 900 seconds.

answer: Provider=PISQLClient.1;Data Source=AFDB5;Integrated Security=SSPI;Location=AFSRV05;Command Timeout=900; Connect Timeout=60

 

From PI SQL Client Admin Guide:

 

From PI SQL Commander:

1 person found this helpful

Attachments

    Outcomes