Linked Server vs SAP HANA stuck on executing

Question

Hello,

I need some help with a very strange problem.

I have 2 sql servers identical (same version, patch, windows, firewall rules, provider settings, odbc settings, etc) connecting via Linked Server (same configurations) with SAP HANA.

When running this query1 on server1 stucked on executing:

SELECT * FROM OPENQUERY([HANASERVER],
'SELECT * FROM "TEST"."WAREHOUSE"')

After 15min i receive this message:

*Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "HANASERVER" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM "TEST"."WAREHOUSE"" against OLE DB provider "MSDASQL" for linked server "HANASERVER".*

When running the same query1 on server2 runs in 2 seconds.

---

When running a query2 on different table on server1 runs in seconds:

SELECT * FROM OPENQUERY([HANASERVER],
'SELECT * FROM "TEST"."PRODUCTS"')

---

When running the above query2 on server1 then query1 starts working but only for executions within ~ 10 minutes...

Any ideas?

Answer 1

Hi,

The OLE DB provider "MSDASQL" for linked server "HANASERVER" reported an error. The provider did not give any information about the error.

Unfortunately usually when you get this error, it does not provide any useful information except the fact that the error come from the external source. For more information, you should monitor the errors in the source data - in your case in the SAP HANA

https://help.sap.com/doc/e344ef1295b6433e88fe084c0768e1cd/2.0.04/en-US/SAP_HANA_Troubleshooting_and_Performance_Analysis_Guide_en.pdf

Notice that I said "usually" since in your case you have a simple issue in your query, and fixing this should be first step

SELECT FROM OPENQUERY([HANASERVER],'SELECT FROM "TEST"."WAREHOUSE"')

I have no idea if this is a laziness which present only a part of the query for the sake of the discussion (in this case it is bad way to discuss issue) or the exact query which you use - in this case please notice that you should add the information which you want to select (the columns). for example SELECT XXXX FROM OPENQUERY([HANASERVER],'SELECT XXXX FROM "TEST"."WAREHOUSE"')

Dont forget that SAP HANA (high-performance analytic appliance) is a column-oriented database so you need to query the columns just like SQL Server. You should not execute SELECT FROM Table_name but SELECT Column_Name From Table_name

connecting via Linked Server

If you are using linked server and the configuration was good then you can use the four-parts-name in order to query the data instead of using OPENQUERY. Check this very short step by step tutorial: https://www.cdata.com/kb/tech/saphana-odbc-linked-server.rst

SELECT * FROM [linked server name].[CData SAPHANA Sys].[SAPHANA].[Buckets]

Answer 2

I want also to give one more tip.

If i use the query with top1 it returns results instantly:

SELECT FROM OPENQUERY([HANASERVER],
'SELECT TOP 1 * FROM "TEST"."WAREHOUSE"')

but if i try for example top10 stucks again:

SELECT FROM OPENQUERY([HANASERVER],
'SELECT TOP 10 * FROM "TEST"."WAREHOUSE"')

Answer 3

As others have said, running a query this way it is nearly impossible to debug or determine the root cause from the SQL Server side. The HANA server is generating an error, which is not returned to SQL Server.

The first thing to do is to look at the HANA server log and see if there is any information.

Additionally, my guess would be this is a blocking issue of some kind on the HANA server side.

Answer 4

In addition to the other posts, the reasons for the differences between the server1 and server2 could be due to differences in how the linked server is set up. For instance, they may be using different proxy logins for Hana and Hana may have different resource pools for this logins. (I'm just making things up, I don't know anything about Hana.)

By the way, when you add code, use the button with the ones and zeroes on it, to avoid that part of the code is interpreted as Markdown.

Answer 5

Still no reply from dep about the issue on SAP database side. So I ll accept the answer.