I am trying to query AF with PI Oledb. I can get the query to work inside sql commander and vba; however, I wish to set it up in a stored procudure to recalculate an AF equation at evenly spaced times. Then I wish to total the results of the query inside sql and send the result to PI via oledb. So, I set up a linked server in sql server mangement called AFLINK. Then I tried my query for AF inside sql server mangement, and I get the following error:

Msg 4122, Level 16, State 1, Line 9
Remote table-valued function calls are not allowed.



My query is as follows:
SELECT eh.Name Element, ea.Name Attribute, i.Time, i.Value
FROM AFLINK.PIAFDB.Asset.ElementHierarchy eh
INNER JOIN AFLINK.PIAFDB.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
CROSS APPLY AFLINK.PIAFDB.Data.InterpolateRange (ea.ID, N't', N'*', N'1h') i
WHERE eh.Path = N'\'
AND eh.Name = N'Digestion'
OPTION (FORCE ORDER)


Thanks,
Kevin

OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System.
We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ  or contact OSIsoft vCampus for more details.

Rhys:

I tired what you suggested, and I still got an error; however, I did get it to work by simply creating a view with sql commander and hitting that view.



Now, I have another issue:
When I run my stored procedure from sql mangement studio, it works great; however, when I set it up as a job with sql server agent, it fails. I get the folowing error:

Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.


Any help would be greatly appreciated.

Thanks,
Kevin