AnsweredAssumed Answered


Question asked by jrheeders on Feb 20, 2019
Latest reply on Feb 20, 2019 by Dan Fishman



I want to write Historian data into MSSQL within hour buckets that calculates the average value.


If I use this code it work perfect:

Select Tag,Avg(Cast(Value as float)) as Value  FRom FTHLINK.piarchive..picomp2


tag = 'Plant.Kao0:RSLinx Enterprise:Kao0.ANALOG_INPUTS117..ANA_IN_EU'

and time >= '2016-01-01 00:00:00' and

time < '2016-01-01 01:00:00'

Group By Tag


But if I use the Openquery which is recommended I am getting an error the whole time complaining about the float conversion:


Declare @strQuery Nvarchar(Max)

Declare @TagName Varchar(250)

Declare @StartTime Varchar(250)

Declare @EndTime Varchar(250)


Set @TagNAme = 'Plant.Kao0:RSLinx Enterprise:Kao0.ANALOG_INPUTS117..ANA_IN_EU'

Set @StartTime = '2016-01-01 00:00:00'

Set @EndTime = '2016-01-01 01:00:00'

Set @strQuery = 'Select * From OPENQUERY (FTHLINK, ''SELECT Tag, '''''+@StartTime+''''' as TimeStamp, Avg(Cast(Value as Float)) as Value FROM piarchive..picomp2 WHERE tag = '''''+ @TagName + ''''' AND time >= '''''+@StartTime+''''' AND time < ''''' +@EndTime+ ''''' Group by Tag'' )'

EXEC sp_executesql @strQuery


Any help would be appreciated.