Jan4Written by:Drew Salem
Tuesday, January 04, 2011 
There are a few show-stopping limitations and/or bugs to the PHD OLEDB provider (at the time of writing). The tables provided by the provider are:
PHD_DATA
PHD_LINK_LIST
PHD_PARENT_TAG
PHD_RDI_LIST
PHD_REDUCTIONS
PHD_TAG
PHD_TAG_BROWSE
PHD_WRITE_DATA
Anomaly One: Standard SQL cannot be used on all tables consistently
Some tables such as PHD_TAG can be queried successfully using a standard SELECT query against a linked server such as:
SELECT *
FROM your_linkedserver_name.standardapi.phd.phd_tag
This will return all your tags with relevant attributes such as their interpolation method, whether they have been manually inputted and their description.
However if you run a similar query against PHD_DATA (which holds the tag data such as the value, confidence, timestamps etc):
SELECT *
FROM your_linkedserver_name.standardapi.phd.phd_data
You get nothing.
If you specify a tag number and timestamp:
SELECT *
FROM your_linkedserver_name.standardapi.phd.phd_data
WHERE tagname = '301GXXXXI004.PV'
AND start_timestamp = 'Now-30'
AND end_timestamp = 'Now'
You still get no results.
If you run a query in the following format (which is normally valid):
EXEC ('SELECT * FROM phd_data') AT your_linkedserver_name
No results. But if you use the previous format with a tag number and timestamp, you DO get results back:
EXEC ('SELECT * FROM phd_data WHERE tagname = "301XXXXX2I004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000') AT your_linkedserver_name
That last query returns all columns for tag 301G00342I004.PV for the past 30 days with an hourly value. Use double quotes in your query to specify strings, otherwise you get an error.
Anomaly Two: Simple SQL Joins cannot be made on all tables
A basic requirement would be to combine data from two tables to return overall information on a tag. For example, the value of the tag is stored in phd_data. The tag's description is stored in phd_tag.
Running the following returns an error message:
SELECT dt.tagname,
dt.tagno,
tg.description
dt.timestamp,
dt.sample_interval,
dt.confidence,
dt.VALUE
FROM phd_data dt
INNER JOIN phd_tag tg
ON tg.tagno = dt.tagno
WHERE dt.tagname = '301XXXXI004.PV'
AND dt.start_timestamp = 'Now-30'
AND dt.raw_data = 0
AND dt.end_timestamp = 'Now'
AND dt.sample_interval = 3600000
Msg 208, Level 16, State 1, Line 1
Invalid object name 'PHD_DATA'.
I reluctantly tried this:
EXEC ('SELECT dt.tagname, dt.tagno, dt.timestamp, dt.sample_interval, dt.confidence,
dt.VALUE FROM phd_data dt INNER JOIN phd_tag tg ON tg.tagno = dt.tagno
WHERE dt.tagname = "301GM003FI004.PV" AND dt.start_timestamp = "Now-30" AND dt.raw_data = 0
AND dt.end_timestamp = "Now" AND dt.sample_interval = 3600000') AT your_linkedserver_name
However, this returns:
"PHDOleDB" for linked server "myphdlink" returned message "Execute Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "PHD Server Initialisation Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "ParseSQLString Failed".
OLE DB provider "PHDOleDB" for linked server "myphdlink" returned message "Bad Column Ordinal".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'your_linked_server'
So what's the solution? I've no idea. If you find one, let me know. In the meantime, I have put a call in and am awaiting a response from Honeywell.
Anomaly Three: Query results cannot be stored in temporary or memory tables.
I figured if results are being returned to a screen, then they can be stored in temp tables. If the data is in temp tables, then they can be joined.
SELECT *
INTO #temp_tag
FROM your_linkedserver_name.standardapi.phd.phd_tag
SELECT *
FROM #temp_tag
DROP TABLE #temp_tag
This works. Now we need to put PHD_DATA into a temp table. For the reasons mentioned above in anomaly one, neither of the following work:
SELECT *
INTO #temp_data
FROM your_linkedserver_name.standardapi.phd.phd_data
WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30"
AND end_timestamp = "Now"
SELECT *
FROM #temp_data
DROP TABLE #temp_data
or
SELECT *
INTO #temp_data
FROM your_linkedserver_name.standardapi.phd.phd_data
WHERE tagname = '301GM003FI004.PV'
AND start_timestamp = 'Now-30'
AND end_timestamp = 'Now'
SELECT *
FROM #temp_data
DROP TABLE #temp_data
or
INTO #TEMP_DATA
EXEC ('SELECT * FROM phd_data WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND end_timestamp = "Now"') at you_linkedserver_name
SELECT * FROM #TEMP_DATA
DROP TABLE #TEMP_DATA
Using Dynamic SQL doesn't even work for PHD_TAG
DECLARE @SQL NVARCHAR (4000)
SET @SQL =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'
CREATE TABLE #tagno
(
tagno INT
)
INSERT INTO #tagno
EXEC Sp_executesql @sql
SELECT *
FROM #tagno
DROP TABLE #tagno
or
DECLARE @SQL2 NVARCHAR (4000)
SET @SQL2 =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'
CREATE TABLE #tagno
(
tagno INT
)
INSERT INTO #tagno
EXEC (@sql2)
SELECT *
FROM #tagno
DROP TABLE #tagno
The error returned for the last two is:
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "PHDOleDB" for linked server "myphdlink" does not support the required transaction interface.
Finally, I thought I'd try a memory table:
DECLARE @SQL3 NVARCHAR (4000)
SET @SQL3 =
'EXEC (''SELECT TAGNO FROM phd_tag WHERE tagname = "301GM003FI004.PV"
AND start_timestamp = "Now-30" AND raw_data = 0 AND end_timestamp = "Now"
AND sample_interval = 3600000'') at your_linkedserver_name'
DECLARE @MemoryTable TABLE (
tagno INT)
INSERT @MemoryTable
EXEC Sp_executesql @SQL3
SELECT *
FROM @MemoryTable
Anomaly Four: Using SQL Server's Import and Export Wizard to pull data into a relational database table...
...doesn't work. It looks like it does right up until you execute the package. When previewing the data within the Wizard, it is using it's own process to view the data. However, when executing the package, a seperate process is created and thus it fails as this isn't supported by the provider. When creating a linked server to a PHD server, there is a "Allow Inprocess" checkbox for the provider offered by SQL Server that allows you to have the query executed using the same process that SQL Server is using. This option however is not available in the Import Export Wizard. You could probably get round this in SSIS, but as I wanted to enable the business by allowing users to create their own reports, I didn't bother going down that route.
Conclusion?
Well, compared to standard providers, it's a bit flakey. That said, it's fairly impressive what they've done when you think about it. If you want to get fancy with your reporting using standard techniques without the worry of running into unexpected and painful obstacles and obtuse workarounds, you may want to use PHDTOREL instead (though there are draw backs to this too).