AnsweredAssumed Answered

Parameterize  GetSampleValues  Stored Procedure for PI SQL Client Linked Server

Question asked by GregMcFarlane on Jan 7, 2020
Latest reply on Jan 8, 2020 by GregMcFarlane

I am developing SSRS reports that use PI data. 

 

I am trying cobble together something that allows me to ask parameterized questions (as opposed to writing explicit functions for every attribute I am asked to plot)

 

For my proof of concept using the first Attribute , i follow this procedure 

1. use dataModeler in SQL Commander Lite (SCL) to create a table valued function that calls GetSampledValues

2. on my linked server,  write an openQuery so I can pass in  elementId, dates, timestep

 

this works. 

 

My challenge is that I will be asked to write a lot of these.  

If there are NO alternatives, thats fine, Ill just get busy... but it seems like I am setting myself up for a maintenance nightmare. 

 

I have tried creating a table valued function (TVF) that takes  TemplateName, and AttributeName as parameters. This does not seem to work. 

 

I can take the guts out of the TVF create script and run that in SCL. That works.  I can not take that same query and port it to the LINKED SERVER (LS).  It chokes on the  LESS THAN sign.

 

 

Ideally, I would like to have something on my Linked Server that looks like this

 

--- these would all be parms i pass in to my stored procedure

Declare @templateName varchar(100), 

@ElementName varchar(100), 

@ParentName varchar(100),

@StartDate datetime, 

@EndDate datetime, 

@TimeStep varchar(100)

 

 

SELECT e.Name, e.id, e.PrimaryPath, s.*
FROM
(
   SELECT e.ID, e.Name, e.Template, e.PrimaryPath
   FROM [Master].[Element].[Element] e
   where e.[Name] = @ElementName
   and e.PrimaryPath like ''%'' + @ParentName + "%''
) e
CROSS APPLY [Master].[Element].[tvf_Gregs_GetSampledValues]
(
e.ID, --Element ID
@startTime, --Start Time
@endTime --End Time
@TimeStep --Time Step
) s
WHERE e.Template = @TemplateName
'

Outcomes