Problem
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.
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.
SELECT * FROM OPENQUERY(LS, 'SELECT a.col1, b.col2
FROM object1 a
INNER JOIN object2 b
ON a.id=b.id') tbl