Troubleshooting PI OLEDB: Joining SQL Tables with PIInterp2 vs PISnapshot

When working with OSIsoft PI OLEDB, it's common to enrich PI data with metadata or tag lists stored in SQL Server tables. Such integrations are vital for building efficient reports and dashboards—especially when you deal with hundreds or thousands of tags. However, sometimes behavior between tables like PIInterp2 (interpolated data) and PISnapshot (latest value) can differ in ways that stump even experienced engineers. In this article, I'll walk through a practical example of this, the underlying limitations, and how to work around them.

---

The Scenario: Joining SQL Tag Lists to PI Data

Suppose you have a SQL Server table (TagTable) holding tag names—for example, 1,400 different PI tag names. You want to retrieve both the current (snapshot) value and the interpolated archive value from one day ago for each tag. This means two queries against PI OLEDB:

1. Join with PISnapshot: Grabs the current value for each tag.
2. Join with PIInterp2: Grabs an interpolated value from a precise timestamp (like exactly 24 hours ago).

Your SQL might look like this:

-- Works for snapshot
SELECT s.tag, s.value
FROM [LINKED_PISERVER].[piarchive]..[pisnapshot] s
WHERE s.Tag IN (SELECT Tag FROM dbo.TagTable)

-- Fails with PIInterp2
SELECT i.tag, i.value
FROM [LINKED_PISERVER].[piarchive]..[piinterp2] i
WHERE i.Tag IN (SELECT Tag FROM dbo.TagTable)
  AND i.time = CAST(DATEADD(DAY, -1, GETDATE()) AS VARCHAR(MAX))

The first query works as expected. The second returns no results or fails unexpectedly.

The Problem: Query Complexity and Limits

If you try running these queries through a SQL Server linked server using OPENQUERY, you may soon hit a wall: the notorious 8,000-character limit on query length. With thousands of tags, dynamically building SQL strings to pass through OPENQUERY quickly becomes unmanageable.

Interestingly, the same approach against PISnapshot works, but not with PIInterp2. Why?

Investigating the Root Cause

Community discussions and OSIsoft tech support suggest several possible root causes:

• Linked Server and OLEDB provider behavior: PIInterp2 is more complex than PISnapshot, especially when resolving precise timestamps for interpolation. Some query planners are more restrictive about correlated subqueries or cannot efficiently push predicates (the IN) through.
• SDK Issues: There have been past PI SDK issues regarding millisecond rounding. However, upgrades did not resolve the core query difference in this scenario.
• Time Zone or Timestamp Formatting: If SQL Server and PI Server use different time zones, or if the timestamp format doesn't match exactly, PIInterp2 may return no rows. Using GETDATE() from SQL Server may not align to what PI expects, especially if high-precision timestamps are required.
• Query Length and OPENQUERY Limitations: Perhaps most critically, the 8,000-character limit for queries sent via OPENQUERY truncates large lists of tags, causing incomplete or empty results.

Workaround: Using sp_executesql Instead of OPENQUERY

A successful workaround is to ditch OPENQUERY and use sp_executesql to run the dynamic SQL. Unlike OPENQUERY, sp_executesql allows query text up to 2GB. This enables you to construct a large IN clause for all tags in your TagTable without truncation:

DECLARE @CSVString NVARCHAR(MAX), @Query NVARCHAR(MAX)
-- Construct CSV list of tag names from TagTable
SELECT @CSVString = Stuff((SELECT N''', ''' + [TagName] FROM dbo.TagTable FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)'), 1, 3, N'') + N''''

SET @Query = 'SELECT i.tag, i.value ' +
    'FROM [LINKED_PISERVER].[piarchive]..[piinterp2] i ' +
    'WHERE i.tag IN (' + @CSVString + ') ' +
    'AND i.time = CAST(DATEADD(DAY, -1, GETDATE()) AS VARCHAR(Max))'

EXECUTE sp_executesql @Query

This approach can handle very large lists of tags and complex filtering logic.

---

Other Troubleshooting Tips

• Check Provider Limitations: Different PI OLEDB versions and PI SDK versions may handle subqueries or joins differently.
• Server Permissions and Configuration: Ensure SQL Server and PI Server are properly linked and time zones match. Some linked server provider options may influence result retrieval.
• Performance Concerns: With large tag lists, consider batching your queries or using PI AF/Analytics to stage results in advance if performance is lacking.
• Use PI SQL Commander: Testing your queries in PI SQL Commander can help isolate syntax and provider-specific issues.

---

Conclusion

Mismatches between expected and actual behavior when joining SQL tables to PI OLEDB tables like PIInterp2 can be frustrating. The culprit is often less about the PI OLEDB schema itself and more about SQL Server and provider-specific limitations—most commonly, the query length ceiling of OPENQUERY. Using sp_executesql or batching your queries can resolve the problem and unlock scalable historical data reporting for all your tags.

---

References & Further Reading:

• OSIsoft PI OLEDB Provider Documentation
• SQL Server Linked Server Limitations

If you've faced similar issues or have additional tips, let us know in the comments!