title: SQL Server to SalesForce Linked Server Nightmare
body: <p>I am getting inconsistent results from joining Linked Servers. It's something that should be relatively simple... but has taken me hours to get this figured out. I am using SQL Server 2014 and the CData ODBC Driver to Join to SalesForce. I'm not doing anything fancy just trying to perform standard CRUD operations but again and again it seems that when ever I filter these linked server tables that sometimes results do not produce.</p>
  
  <p>My current and main issue right now is I am having difficulty JOINING two Linked tables to two Local tables. If I remove one of the Linked tables from the join results are produced. But whenever I add two linked tables to the joins it produces and empty record set. </p>
  
  <p>And yes all the related identifiers exist so it really is an issue with the Linked Server. Here are the three variations that I've tried:</p>
  
  <pre><code>SELECT * FROM Offer_Interest oi
  INNER JOIN Offer o ON oi.Offer_ID_SQL = o.Offer_ID_SQL
  INNER JOIN OPENQUERY([TR-SF-PROD], 'SELECT Id, OFFER_ID_SQL__C FROM Offer__c') osf ON o.Offer_ID_SQL =osf.OFFER_ID_SQL__C
  INNER JOIN Interest i ON oi.Interest_ID_SQL = i.Interest_ID_SQL
  INNER JOIN OPENQUERY([TR-SF-PROD], 'SELECT INTEREST_ID_SQL__C, Id FROM Interest__c') isf ON i.Interest_ID_SQL =isf.Interest_ID_SQL__c
  WHERE o.PrimaryContact_ID_SQL = 2803
  </code></pre>
  
  <p>I've also tried without OPENQUERY:</p>
  
  <pre><code>SELECT * FROM FROM Offer_Interest oi
  INNER JOIN Offer o ON oi.Offer_ID_SQL = o.Offer_ID_SQL
  INNER JOIN [TR-SF-PROD].[CDataSalesforce].[Salesforce].[Offer__c] osf ON o.Offer_ID_SQL =osf.OFFER_ID_SQL__C
  INNER JOIN Interest i ON oi.Interest_ID_SQL = i.Interest_ID_SQL
  INNER JOIN [TR-SF-PROD].[CDataSalesforce].[Salesforce].[Interest__c] isf ON i.Interest_ID_SQL =isf.Interest_ID_SQL__c
  WHERE o.PrimaryContact_ID_SQL = 2803
  </code></pre>
  
  <p>And Lastly I've also created Synonyms to the Linked Server tables. All of these work using the same filter or WHERE CLAUSE if I run them seperately although the linked server tables seem buggy if I filter them without OPENQUERY. </p>
  
  <p>This is my first experience Linking a Server to SQL Server so anyone with experience in this or what the issue may be would be greatly appreciated!</p>