Exercise: Using a Microsoft SQL Server Linked Server

Document created by kduffy on Sep 25, 2018Last modified by kduffy on Feb 14, 2019
Version 19Show Document
  • View in full screen mode

Objective: The goal of this exercise is to practice converting PI SQL queries into Microsoft SQL Server linked server queries.

 

Approach:

The first step in writing a query, whether intended for native execution or for something such as a linked server, should be to create the PI SQL query that runs in PI SQL Commander to give the intended output. This allows you to easily use the resources provided to build the query as well as ensure that it works against the PI System before moving to a third party application. Then the final step is to port over the query with necessary syntax changes.

 

The first step has already been covered previously in the course, so this exercise will focus on the second part. You will be provided with a set of PI OLEDB Provider and PI OLEDB Enterprise queries that you'll need to change the syntax for so that it's able to be executed against a SQL Server linked server.

 

Note:

  • For PI OLEDB Enterprise queries, the AF Server linked server is named AFSRV01Linked
  • For PI OLEDB Provider queries, the PI Data Archive linked server is named PISRV01Linked

 

Using the following resources:

As well as these additional resources:

 

Note: All of the following queries can be made without using OPENQUERY (Transact-SQL), however this construct can be used in all cases as desired and the solution guide will show both approaches.

 

Problem Statement:

1. Execute the Predefined Query for the pipoint..pipoint2 table with a trimmed down SELECT clause

SELECT tag, descriptor, compressing, creator, pointsource, zero, span

FROM [pipoint]..[pipoint2]

WHERE pointsource = 'R'

 

2. Execute the Predefined Query for the piarchive..piinterp2 table with slight changes to the time-related WHERE clauses

SELECT *

FROM [piarchive]..[piinterp2]

WHERE tag = 'sinusoid'

AND time BETWEEN 'y' AND 't'

 

 

3. Execute the transpose snapshot function for Boilers, and return just the boilers that are in the Extruding Process\Equipment branch for each site

SELECT eh.Path + eh.Name Element, ts.*

FROM [NuGreen].[Asset].[ElementHierarchy] eh

CROSS APPLY [NuGreen].DataT.[TransposeSnapshot_Boiler]

(

eh.ElementID

) ts

WHERE eh.Path like N'\NuGreen\%\Extruding Process\Equipment\'

OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

 

4. Execute the Predefined Query for the Boiler's transpose snapshot function

SELECT eh.Path + eh.Name Element, ts.*

FROM [NuGreen].[Asset].[ElementTemplate] et

INNER JOIN [NuGreen].[Asset].[Element] e

ON et.ID = e.ElementTemplateID

INNER JOIN [NuGreen].[Asset].[ElementHierarchy] eh

ON e.ID = eh.ElementID

CROSS APPLY [NuGreen].DataT.[TransposeSnapshot_Boiler]

(

eh.ElementID

) ts

WHERE et.Name = N'Boiler'

OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

Have questions? Ask the community

When you are ready, compare your answers with the Solution: Using a Microsoft SQL Server Linked Server

Attachments

    Outcomes