Dudley McFadden

Linked Server Datetimes or times

Discussion created by Dudley McFadden on Jun 24, 2013
Latest reply on Jun 25, 2013 by Marcos Vainer Loeff

Hello, I have used PI OLEDB through  SQL Server 2005 for a few years using Linked Server. The main reason is that I have tables in my SQL Server database consisting of lists of PI tags.  I use the INNER REMOTE JOIN feature to get data from piinterp, piavg, and event pisnapshot. 

 

Now, running on SQL Server 2008, most of my old queries still work.  But there are some data type issues which seem perplexing.  I already resolved some issues by using Unicode strings (NVARCHARs).  And, I realized that the SQL Server 2008 data type TIME(2) is what I need to pass as the timestep field in the WHERE clause to the aggregate tables in PI.  That data type wasn't implemented in SQL Server 2005.  But today I can't seem to figure out specifying time ranges.

 

Regarding dates and times, for example, using the PI OLEDB Tester, this query:

 
select time, value, pctgood from piavg
where timestep='10m' and time between '24-JUN-2013 10:00' and '24-JUN-2013 16:00'
and tag = 'sinusoid'

 retrieves a rowset of 10-minute average tuples.  But from SQL Server Management Studio, with the linked server being named PISERVER,

 
SELECT time, value, pctgood
FROM  PISERVER.piarchive..piavg pm where pm.tag ='sinusoid'
and timestep=N'00:10' AND pm.time BETWEEN N'24-JUN-2013 10:00' and N'24-JUN-2013 16:00'

 this returns no rows.  Moreover, after changing the earlier date from 24 to 22, thus:

 
SELECT time, value, pctgood
FROM  PISERVER.piarchive..piavg pm where pm.tag ='sinusoid'
and timestep=N'00:10' AND pm.time BETWEEN N'22-JUN-2013 10:00' and N'24-JUN-2013 16:00' 

 I receive an error message presumably from the SQL Server server: 

 

Msg 8114, Level 16, State 11, Line 17
Error converting data type DBTYPE_DBTIME to time.

 

So, maybe I need a CONVERT or CAST statement or something?

 

Should I use the pifunction table feature instead?  My administrator would need to create the RELDATE and DATE functions, correct?

Outcomes