Linked Server: Joins in OpenQuery fail

  • 7 August 2023
  • 0 replies
  • 58 views

Userlevel 7
Badge

Using OpenQuery throws the “Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "id" is a duplicate.” error. 

This error occurs with every JOIN. The tests I made: 

  • SELECT * FROM OPENQUERY ([GOOGLESHEETS], 'SELECT * FROM Food_FoodSales as a, [Employee Sample Data_Data] as b WHERE a.Region=b.Region') 

  • SELECT * FROM OPENQUERY ([GOOGLESHEETS], 'SELECT a.id, b.id FROM Food_FoodSales a LEFT OUTER JOIN [Employee Sample Data_Data] b ON a.Region=b.Region') 

  • SELECT * FROM OPENQUERY ([GOOGLESHEETS], 'SELECT a.id, b.id FROM Food_FoodSales a INNER JOIN [Employee Sample Data_Data] b ON a.Region=b.Region') 

 

 

To fix this issue, you can simply use the standard SQL statement 

SELECT *  

FROM [GOOGLESHEETS].[CData GoogleSheets Sys].[GoogleSheets].[Food_FoodSales] a,  

[GOOGLESHEETS].[CData GoogleSheets Sys].[GoogleSheets].[Employee Sample Data_Data] b  

WHERE [a].[Region]=[b].[Region] 


This topic has been closed for comments