I am working on a SSRS report to display sampled values(hourly) given Element and Attribute name. Linked server is setup. Tried to create a SQL stored procedure to accept user input: element, attribute, starttime and endtime:
ALTER PROCEDURE [dbo].[TSDetails]
-- Add the parameters for the stored procedure here
@Element varchar(500)='Tank1',
@Attribute varchar(500)='Temperature',
@StartTime varchar(100)='2024-03-01 13:00:00.000',
@EndTime varchar(100)='2024-03-10 13:00:00.000'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @SQL nvarchar(max)
declare @TempTable TABLE(Element nvarchar(500), Attribute nvarchar(500),Value nvarchar(500), Timestamp datetime, Error nvarchar(max))
set @SQL=('select a.Element, a.Name Attribute, sv.TimeStamp, sv.Value, sv.Error From [LINKEDSERVER_AF].Master.Element.Attribute a CROSS APPLY [LINKEDSERVER_AF].Master.Element.GetSampledValues(a.ID, ''@StartTime'',''@EndTime'',''1h'') sv
WHERE a.Element = ''@Element'' AND a.Name = ''@Attribute''')
-- Insert statements for procedure here
insert into @TempTable
exec(@SQL)
END
GO
This query results in an error:
Remote table-valued function calls are not allowed.
Is there a different way to retrieve sampled values using RTQP engine? User may enter any date range and must be able to retrieve values.
