Get erros when trying to run some queries through linked server.

Discussion created by rchandler on Oct 23, 2012
Latest reply on Oct 24, 2012 by spilon

Some queries can be run on the PI SQL server commander. But when I tried to run them through SSMS which has a linked server to the PI.


I got the error message like this:


OLE DB provider "PIOLEDBENT" for linked server "targetsafserver" returned message "[Targets.Data.Snapshot Tbl1005] Neither 'ElementAttributeID' nor 'ElementTemplateAttributeID' column is restricted. Such a query is considered expensive.




Possible solutions:


- Add a join with 'ElementAttribute' or 'vElementAttribute' or 'ElementTemplateAttribute' table or, if the join is already present, reorder tables in the FROM clause and use the 'OPTION (FORCE ORDER)' query hint.


- Add 'OPTION (ALLOW EXPENSIVE)' query hint.".


Msg 7320, Level 16, State 2, Line 1


Cannot execute the query "SELECT "Col1027","Col1025","Tbl1005"."Time" "Col1020","Tbl1005"."Value" "Col1021" FROM "Targets"."Data"."Snapshot" "Tbl1005",(SELECT "Tbl1001"."Name" "Col1027","Tbl1001"."ElementID" "Col1028","Tbl1003"."ID" "Col1024","Tbl1003"."Name" "Col1025","Tbl1003"."ElementID" "Col1022" FROM "Targets"."Asset"."ElementAttribute" "Tbl1003","Targets"."Asset"."ElementHierarchy" "Tbl1001" WHERE "Tbl1001"."ElementID"="Tbl1003"."ElementID") Qry1029 WHERE "Tbl1005"."ElementAttributeID"="Col1024" ORDER BY "Col1027" ASC,"Col1020" ASC,"Col1025" ASC" against OLE DB provider "PIOLEDBENT" for linked server "targetsafserver".


We tried to add  'OPTION (ALLOW EXPENSIVE)', we get some syntax error near option.


Can any one give some suggestions? Anything related to linked server configurations?


Thanks in advance.