When using JOIN in Linked Server OPENQUERY you may get Communication Link Failure error


If you use Linked Server and try to join OPENQUERY then you may get the below error in some cases.

OLE DB provider "SQLNCLI11" for linked server "LS_INFOR_LINKEDSERVER" returned message "Communication link failure".

Possible Cause

Joining OpenQuery with Linked Table can be a very expensive operation and unlike normal table join, it can be very slow especially when calling Drivers like INFOR. What we would suggest is to try any of the following methods to see it helps.

Check the Known Issues here

If your query uses JOIN / UNION with multiple OPENQUERY in same SQL then use multiple Linked servers (one for each OPENQUERY clause) as below.

Error: Cannot use OPENQUERY with JOIN / UNION

When you perform JOIN / UNION ALL on the same Linked Server it may fail to process sometimes because Datagateway doesn’t support parallel query requests on the same connection.

You can try any of the following workarounds

Method1 (Use multiple linked servers)

Use multiple linked servers to the same data source see it solves the issue.

SELECT a.id, b.name FROM 
OPENQUERY(LS_1, 'select * from tbl1') a JOIN
OPENQUERY(LS_2, 'select * from tbl2') b
on a.id=b.id

Method2 (Use temp tables)

select * into #tmp1 from OPENQUERY(LS_1, 'select * from tbl1') tbl;

select * into #tmp2 from OPENQUERY(LS_2, 'select * from tbl1') tbl;

select a.id,b.name from #tmp1 a join #tmp2 b on a.id=b.id

Method3 (Use native join )

Try to join using its native SQL if the underlying driver supports it.

For example INFOR Datalake SQL has JOIN support so try that first.

FROM object1 a
INNER JOIN object2 b
ON a.id=b.id') tbl