Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Challenge building an SSRS dataset from OpenQuery

I'm trying to convert a large join to an OpenQuery in order to build a subsequent SSRS report. Here is my non-OpenQuery join.

Select T.*, P.*, A.*, U.NAME, U.ID as USER_ID
FROM [SALESFORCE]...[TASK] AS T
INNER JOIN [SALESFORCE]...eo2__ecology_project__c AS P on t.whatid = p.id
INNER JOIN [SALESFORCE]...eo2__eosite__c as S on s.id = .eo2__site_new__c
INNER JOIN [SALESFORCE]...account as A on S.eo2__account__c = a.id
INNER JOIN [SALESFORCE]...[USER] as U on left(T.OWNERID,15) = eft(U.ID,15)
where T.fes_task_type__c = 'Energy Assessment'

When I try to convert to and OpenQuery, I get this far,
Select * FROM OPENQUERY
(SALESFORCE, 'SELECT TASK.ID, eo2__ecology_project__c.ID, ACCOUNT.ID, USER.NAME, USER.id
FROM TASK
INNER JOIN eo2__ecology_project__c on TASK.whatid = eo2__ecology_project__c.id
INNER JOIN eo2__eosite__c on eo2__eosite__c.id = eo2__ecology_project__c.eo2__site_new__c
INNER JOIN ACCOUNT on eo2__eosite__c.eo2__account__c = ACCOUNT.id
INNER JOIN USER on left(TASK.OWNERID,15) = left(USER.ID,15)
WHERE (TASK.FES_TASK_TYPE__C = "Energy Assessment")
')

and then receive the following error

OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 2032 : Relationship eo2__ecology_project__c is not valid.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".

SAVE ME BILL!
1 person has
this problem
+1
Reply