Pass-through mechanism

You can use a pass-through mechanism to query data in a linked server. A pass-through mechanism wraps a query in a function so that the query itself is sent to PI SQL Client OLEDB and the result is returned. OPENQUERY is a pass-through mechanism that executes the specified pass-through query on the specified linked server. It can be referenced in the FROM clause of a query as if it were a table name.

OPENQUERY(LINKED_SERVER, 'query')

where:

• Linked_Server is the name used to identify the linked server, which was specified during configuration.

• query is the query string to be executed in the linked server. The maximum length of the string is 8 KB.

• OPENQUERY cannot be used to execute extended stored procedures on a linked server. To execute an extended stored procedure on a linked server, use a four-part name. For more information, see References to linked server objects.

• OPENQUERY does not accept variables for its arguments.

Examples of pass-through mechanism

The following example is a simple query that returns all information about every element from the database specified during configuration.

SELECT * FROM OPENQUERY (LINKED_SERVER, 'SELECT * FROM [Master].[Element].[Element]')

The following example is a more complex query that returns attribute values of a primary referenced element at event frame end time.

SELECT * FROM OPENQUERY (LINKED_SERVER, 'SELECT ef2.Name EF_Name,
ef2.PrimaryReferencedElement E_Name, ea.Name EA_Name, v.Value
FROM
(
SELECT TOP 1 ID
FROM [Master].[EventFrame].[EventFrame]
WHERE IsRoot
ORDER BY StartTime ASC
) ef1
INNER JOIN [Master].[EventFrame].[EventFrame] ef2 ON ef2.ParentID = ef1.ID
INNER JOIN [Master].[Element].[Attribute] ea
ON ea.ElementID = ef2.PrimaryReferencedElementID
CROSS APPLY [Master].[Element].[GetSampledValue](ea.ID, ef2.endtime) v
WHERE ef2.Name LIKE ''%A''
-- we just want the ''xxxx_A'' child Event Frames')