Using PI OLEDB's ROUND5 Function With SQL Server Linked Servers: Challenges and Best Practices

How to Use the PI OLEDB ROUND5 Function with SQL Server Linked Servers

Working with OSIsoft PI data via SQL Server’s PI OLEDB provider can be a powerful way to make process data accessible for analytics and reporting. One common requirement is to round or align timestamps, often when retrieving interpolated data from tables such as piinterp. The ROUND5 PI Function is provided for this purpose. However, as many users discover, integrating ROUND5 into linked server queries via SQL Server can be challenging. This post explains how to use ROUND5, why you might see NULL results, and whether timestamp rounding is necessary in your case.

What is the ROUND5 PI Function?

In PI OLEDB, the ROUND function is overloaded. When added to the PI Function catalog in SQL Server via Catalog Manager, the version that takes a DateTime and a Time span is often named ROUND5. Its primary use is to round (or snap) a timestamp to the nearest interval (e.g., nearest hour, minute, etc.).

The Problem: ROUND5 Returns NULLs via Linked Server

It’s common for users to attempt something like this:

SELECT *,
  (SELECT result FROM [YourLinkedServer].[pifunction]..[ROUND5]
     WHERE arg1 = [time] AND arg2 = @interval) AS RoundedTime
FROM [YourLinkedServer].[piarchive]..[piinterp]
WHERE ...

But despite correct syntax and existence of the function, the RoundedTime column returns NULLs. This may be confusing, especially since the similar TIME function works as expected.

Why Does This Happen?

The way SQL Server constructs queries for remote linked servers can differ from local execution. Some PI functions, like TIME, work because their internal logic and expected parameter types match what is sent from SQL Server. Others, like ROUND5, may not correctly interpret the arguments (especially non-scalar subqueries or SQL parameter passing) and end up returning NULL. This issue is particularly apparent when trying to reference dynamic columns or variables in the subquery for each row.

The Effective Workaround: Using OPENQUERY

OPENQUERY allows you to send a query string directly to the linked PI OLEDB server for processing. This can bypass some of SQL Server’s query translation logic and ensure the PI OLEDB engine gets the query as intended.

Example:

SELECT *
FROM OPENQUERY(YourLinkedServer, 
  'SELECT result FROM pifunction..ROUND5 WHERE arg1 = ''2016-01-02 01:00:00'' AND arg2 = ''01:00:00'' ')

This works reliably for fixed parameters.

---

Is Timestamp Rounding Necessary?

If your use case is regular interval timestamps from piinterp, specifying a [timestep] in your query usually returns data already aligned to the interval boundary. That means the [time] column is already rounded as you intended, so using ROUND5 becomes redundant. It’s beneficial to compare results with and without explicit rounding:

-- This usually gives you already-aligned timestamps
declare @timestep nvarchar(20) = '01:00:00';
SELECT [time], [value]
FROM [YourLinkedServer].[piarchive]..[piinterp]
WHERE [tag] = 'MyTag' 
  AND [timestep] = (SELECT result FROM [YourLinkedServer].[pifunction]..[time] WHERE arg1 = @timestep)

Recommendation

• If your data is already at the desired interval, no need to apply ROUND5.
• Use ROUND5 only if you have irregular or high-resolution data and need to snap timestamps to a custom interval explicitly.

Can You Use ROUND5 Without OPENQUERY?

Due to linked server limitations with certain PI functions, there is no fully reliable workaround that avoids OPENQUERY when you must call functions like ROUND5 for each row returned. Either you:

• Use OPENQUERY with hard-coded parameters (best for one-off operations), or
• Pull data without rounding and round timestamps in SQL Server or in your application code.

If you do need fully dynamic rounding per row, consider:

• Importing data into a SQL staging table, then process and round timestamps using T-SQL (DATEADD, DATEDIFF) in SQL Server, or
• Implement similar rounding logic in application code (C#, ETL tool, etc.) after data retrieval.

Summary

• ROUND5 in PI OLEDB is useful for timestamp alignment, but SQL Server Linked Server usage is limited.
• OPENQUERY provides a workaround but isn’t ideal for dynamic queries.
• Often, setting the timestep provides already-aligned times—so rounding isn’t necessary.
• Where needed, prefer post-processing in SQL or application code to achieve timestamp rounding.

Do you have other linked server integration scenarios? Let me know in the comments or contact me—there’s always more to explore in the intersection of PI System, SQL Server, and industrial data analytics!