Solution: Using a Microsoft SQL Server Linked Server

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

This document contains the answers to the Exercise: Using a Microsoft SQL Server Linked Server.

Note: These answer assume that linked servers, named "PISRV01LINKED" and "AFSRV01LINKED", are functional on the snapshot transpose function was created for NuGreen's Boiler template.

 

Each question takes the conversion of PI SQL -> Microsoft SQL Server syntax one step further.

 

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

 

This is the simplest case of converting a PI SQL query into one that's compatible with a linked server. The name of the table needs to be preceded with the linked server name:

SELECT tag, descriptor, compressing, creator, pointsource, zero, span
FROM [PISRV01LINKED].[pipoint]..[pipoint2]
WHERE pointsource = 'R'

 

Alternatively, simply wrapping the query with an openquery statement will also work since the SQL Server will not attempt to parse the query itself (with single quotes used as escape characters for existing single quotes in the query):

SELECT * FROM OPENQUERY([PISRV01LINKED],'
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

This is the next step in converting queries: convert PI specific syntax. In this case, the timestamps used are referring to 't' and 'y', which are only understood by the PI System. These need to be converted to something that the SQL Server can understand, such as GETDATE() or DATEADD(), or a manually entered timestamp. Since this is not a course covering t-SQL, we will avoid things such as GETDATE and DATEADD, and instead demonstrate the timestamps themselves:

SELECT *
FROM [PISRV01LINKED].[piarchive]..[piinterp2]
WHERE tag = 'sinusoid'
AND [time] BETWEEN '2018-10-03' AND '2018-10-04'

 

 

Alternatively, simply wrapping the query with an openquery statement will also work since the SQL Server will not attempt to parse the query itself (with single quotes used as escape characters for existing single quotes in the query):

SELECT * FROM OPENQUERY([PISRV01LINKED],'
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

 

This next question begins to get a little more complicated. There are types of database objects, such as table-valued functions, that the SQL server will not allow to be included in the query for a linked server. The preferred way to query a transpose function is a table-valued function, but if this were to be executed against the SQL server's linked server, you'll see this:

Remote table-valued function calls are not allowed

 

 

If you recall from the transpose function creation wizard, you were presented with the option to create a function table as well as the table-valued function:

 

This option, among other use cases, is particularly useful when the SQL client does not allow table-valued functions. Once created, you can execute its Predefined Query to see how to use the function table, then edit the query slightly to get the output that was requested for this portion of the exercise

 

Now, when the function table is presented to the SQL client, it presents itself as a normal table, and the fact that it's a function table is only known to the query engine. This will work around the limitation, and all that's needed to resolve the query is to add the linked server name in front of each database object:

 

SELECT eh.Path + eh.Name Element, ts.*
FROM [AFSRV01LINKED].[NuGreen].[Asset].[ElementHierarchy] eh
INNER JOIN [AFSRV01LINKED].[NuGreen].DataT.[ft_TransposeSnapshot_Boiler] ts
ON eh.ElementID = ts.ElementID
WHERE eh.Path like N'\NuGreen\%\Extruding Process\Equipment\'

 

 

Alternatively, simply wrapping the query with an openquery statement will also work since the SQL Server will not attempt to parse the query itself (with single quotes used as escape characters for existing single quotes in the query):

SELECT * FROM OPENQUERY([AFSRV01LINKED],'
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

 

This query may seen to have been out of order with question 3 because it's the actual predefined query with no changes, but putting question 3 before this one allowed for the simpler one to occur first. If you were to apply the same principle in question 3 to this query, it would result in:

SELECT eh.Path + eh.Name Element, ts.*
FROM [AFSRV01LINKED].[NuGreen].[Asset].[ElementTemplate] et
INNER JOIN [AFSRV01LINKED].[NuGreen].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [AFSRV01LINKED].[NuGreen].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
INNER JOIN [AFSRV01LINKED].[NuGreen].DataT.[ft_TransposeSnapshot_Boiler] ts
ON eh.ElementID = ts.ElementID
WHERE et.Name = N'Boiler'

 

This query would produce an error because the SQL server is not asking for the correct amount of data from the function table due its execution plan. The PI OLEDB Enterprise is therefore rejecting the query by saying the element ID's are not restricted enough.

OLE DB provider "PIOLEDBENT" for linked server "AFSRV01LINKED" returned message "[NuGreen.DataT.ft_TransposeSnapshot_Boiler Tbl1007 function table] Neither 'ElementID' argument column nor any value column is restricted properly.

 

Possible reasons:

- Invalid WHERE condition (use 'ElementID = ...' or restrict 'Process Feedrate', 'Feedrate Tag', 'Fuel' etc.)

- Join order (reorder tables in FROM clause and use 'OPTION (FORCE ORDER)' query hint)".

To understand how this happened, it's important to understand that this query is being optimized by the SQL server's query optimizer. It normally uses table statistics to know which tables to join in which order, but since the PI OLEDB Enterprise tables are completely unknown to the SQL server's query optimizer, it cannot do a good job.

 

To remove the SQL server's optimizer from the equation, you can either add "Option (FORCE ORDER)" or change the INNER JOIN's to INNER REMOTE JOIN's. Both of these will instruct the SQL server to not try to optimize anything, but to execute the query as is.

 

Force order answer:

SELECT eh.Path + eh.Name Element, ts.*
FROM [AFSRV01LINKED].[NuGreen].[Asset].[ElementTemplate] et
INNER JOIN [AFSRV01LINKED].[NuGreen].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [AFSRV01LINKED].[NuGreen].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
INNER JOIN [AFSRV01LINKED].[NuGreen].DataT.[ft_TransposeSnapshot_Boiler] ts
ON eh.ElementID = ts.ElementID
WHERE et.Name = N'Boiler'
OPTION (FORCE ORDER)

 

Inner Remote Join answer:

SELECT eh.Path + eh.Name Element, ts.*
FROM [AFSRV01LINKED].[NuGreen].[Asset].[ElementTemplate] et
INNER REMOTE JOIN [AFSRV01LINKED].[NuGreen].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER REMOTE JOIN [AFSRV01LINKED].[NuGreen].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
INNER REMOTE JOIN [AFSRV01LINKED].[NuGreen].DataT.[ft_TransposeSnapshot_Boiler] ts
ON eh.ElementID = ts.ElementID
WHERE et.Name = N'Boiler'

 

 

Alternatively, simply wrapping the query with an openquery statement will also work since the SQL Server will not attempt to parse the query itself (with single quotes used as escape characters for existing single quotes in the query):

SELECT * FROM OPENQUERY([AFSRV01LINKED],'
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)')

Attachments

    Outcomes