Error: The data is invalid - Communication link failure (OPENQUERY with SQL Server Linked Server)

Problem:

If you are calling ZappySys Data Gateway feature in SQL server then there will be a time when you start facing odd errors such as below. 

OLE DB provider "SQLNCLI11" for linked server "Zs_Csv" returned message "Deferred prepare could not be completed.".
OLE DB provider "SQLNCLI11" for linked server "Zs_Csv" returned message "Communication link failure".
Msg 13, Level 16, State 1, Line 0

Session Provider: The data is invalid.

Possible Cause:

There are few reasons for such error but below are two main reasons

• If query length is more than 2000 Characters long such as below then you might get such error
  

  SELECT * FROM OPENQUERY(LS, '--some really long text more than 2000 chars--')

• If query contains multiple OPENQUERY statements for JOIN / UNION like below then also it might fail (Gateway doesnt support parallel queries on a single connection - MARS compatibility issue).
  

  SELECT a.id, b.name 
  FROM OPENQUERY(LS, 'select * from tbl1') a 
  JOIN OPENQUERY(LS, 'select * from tbl2') b 
  ON a.id=b.id

Possible Fix:

To fix above error you can try few things such as reduce your query length. This error occurs when you exceed Query more than 2000 characters.

There are few ways you can reduce query length.

• If your query has long SQL (more than 2000 chars ) then reduce SQL length using different techniques
  • e.g. use SELECT * FROM MyTable rather than SELECT col1,col2... FROM MyTable
  • Use Meta Option in WITH clause if you must use column name. (e.g. SELECT * FROM MyTable WITH(META='c:\meta.txt') this way you can define column in Meta file rather than SELECT query. Check this article
  • Conside using EXECT (....) AT [Linked_Server_name] option rather than OPENQUERY so you can use very long SQL (See next section on EXEC..AT usecase)
  • Consider using Virtual Table / Stored Proc to wrap long SQL so your call is very small like below (where usp_GetOrdersByYear is custom proc created on ZappySys Driver UI)

    SELECT * FROM OPENQUERY(LS, 'EXEC usp_GetOrdersByYear 2021') 

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

  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

Performance Tips

We discussed some Pros and Cons of OPENQUERY vs EXEC (...) AT in previous section. One obvious advantage of EXEC (....) AT is it reduces number of requests to driver (It sends pass through query). With EXEC you cannot load data dynamically like SELECT INTO tmp FROM OPENQUERY. Table must exist before hand if you use EXEC.

INSERT INTO tmpMWS_Report
EXEC('select s3.* .................. ') AT [AMAZON-MWS-CSV]

Advantage of this method is your Query speed will increase because system calls API only once when you call EXEC AT. In OPENROWSET it needs to call above query twice (Once to obtain metadata and once to get data).

 

OPENQUERY vs EXEC AT (Handling Larger SQL Text)

So far we have seen examples of using OPENQUERY. It allows us to send pass-through query at remote server. The biggest limitation of OPENQUERY is it doesnt allow you to use variables inside SQL so often we have to use unpleasant looking dynamic SQL (Lots of tick, tick ....  and escape  hell). Well there is good news. With SQL 2005 and later you can use EXEC( your_sql ) AT your_linked_server syntax .

Disadvantage of EXEC AT is you cannot do SELECT INTO like OPENQUERY. Also you cannot perform JOIN like below in EXEC AT

SELECT a.* FROM OPENQUERY(ls_json,'select * from $') a 
JOIN  OPENQUERY(ls_json,'select * from $') b ON a.id=b.id

However you can always do INSERT INTO MyTable EXEC(...) AT LINKEDSRV. So table must exists when you do that way.

Here is how to use it. To use EXEC AT you must turn on RPC OUT option. Notice how we used variable in SQL to make it dynamic. This is much cleaner than previous approach we saw.

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ls_Json', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'localhost,5000', @provstr=N'Network Library=DBMSSOCN;', @catalog=N'JsonApi'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ls_Json',@useself=N'False',@locallogin=NULL,@rmtuser=N'tdsuser',@rmtpassword='########'
GO

EXEC sp_serveroption 'ls_Json', 'rpc out', true;  
go

declare @tbl varchar(100)='$'
EXEC('select * from ' + @tbl ) AT ls_Json

Here is the difference between OPENQUERY vs EXEC approaches.

SQL Server OPENQUERY vs EXEC for Linked Server - Handling Larger SQL Text (More than 8000 chars)

If you decide to use EXEC instead of OPENQUERY then make sure below setting is ON else you will get an error.

RPC OUT setting for EXEC AT statement in SQL Server Linked Server

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to [e-mail]

https://zappysys.com/support/