cancel
Showing results for 
Search instead for 
Did you mean: 

HANA SDA to ESP window

richard_gu
Explorer
0 Kudos

Dear All,

I am trying to use HANA SDA to connect to ESP server, but always get error "SAP DBTech JDBC: [476]: invalid remote object name: can't get SQL Server Info: line 0 col 0 (at pos 0)";

I checked my ESP server and I saw the login success which came from the HANA.

I am using something similar to below connection string:

CREATE REMOTE SOURCE TEST_SDA_TO_ESP ADAPTER "odbc"

CONFIGURATION 'Servername=XXXXXXX;Port=19011;Driver=libesp_psqlodbca_lib.so;Database=gujing_cluster_workspace/test_sdk;SSLmode=disable;'

WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=I053866;password=XXXXXX';


Please help, thank you!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Richard and Ashok,

I was also facing very similar errors as yours, but I finally could make it to work!

As Dilip Sarmah suggested, I tried with HANA SP9 and Smart Data Streaming, but, initially I got same issues. This time I decided to look deeper...

Here I list the steps I followed to successful configure it in HANA SP9 + Smart Data Streaming:

     1- Install HANA SP9 + HANA Smart Data Streaming

     2- Download and install the latest version of unixODBC (http://www.unixodbc.org/download.html)

          tar -xvf unixODBC-2.3.2.tar.gz

          cd unixODBC-2.3.2

          ./configure --prefix=/usr/local/unixODBC

          make

          make install

     3- Create links to unixODBC libs below:

          cd /usr/local/unixODBC/lib

          ln -s libodbccr.so.2.0.0 libodbccr.so.1

          ln -s libodbcinst.so.2.0.0 libodbcinst.so.1

          ln -s libodbc.so.2.0.0 libodbc.so.1

     4- Add unixODBC lib to streaming library path:

          vi /usr/sap/BBB/HDB00/streaming/STREAMING.sh (and add two lines below)

               LD_LIBRARY_PATH="/usr/local/unixODBC/lib":$LD_LIBRARY_PATH

               export LD_LIBRARY_PATH

     5- Add Smart Data Streaming host to your no_proxy environment variable (ESP odbc client have some issues with proxy settings, see: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/7079eef1-53b6-3010-98ae-90e172a82...)

     vi /etc/profile.d/proxy_settings.sh
          export no_proxy="sap.corp,localhost,127.0.0.1,moo-repo,169.254.169.254,repo,mo-b1f55fc59,mo-e0ef78976"

     6- Create .odbc.ini file to check if the ODBC connection is OK (before creating Smart Data Access)

     su - bbbadm  (your hana instance user)

     vi .odbc.ini (with contents below)

[esp1]

Driver=/data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/libstreamingpsqlodbca_lib.so

Database=default/teste_esp

Servername=mo-e0ef78976

Port=30016

Username=SYSTEM

Password=XXXXX

SSLmode=enable

     7- Test your odbc connection:

          /usr/local/unixODBC/bin/isql -v esp1

          You must see isql prompt:

          +---------------------------------------+

          | Connected!                            |

          |                                       |

          | sql-statement                         |

          | help [tablename]                      |

          | quit                                  |

          |                                       |

          +---------------------------------------+

          SQL>

     8- Create SDA connection in your hana studio:

CREATE REMOTE SOURCE esp1 ADAPTER "odbc" CONFIGURATION FILE 'property_esp.ini'

CONFIGURATION

       'Driver=/data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/libstreamingpsqlodbca_lib.so;Database=default/teste_esp;Servername=mo-e0ef78976;Port=30016;Username=SYSTEM;Password=XXXXXX;SSLmode=enable' WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=SYSTEM;password=XXXXX'

CREATE VIRTUAL TABLE summary AT esp1."default"."teste_esp"."summary"

select * from summary

Troubleshooting:

Here are the list of issues I found out and how I fixed them. If you are facing errors, I suggest you checking your ODBC connection using isql before trying SDA commands.

a- ODBC libs

If you receive error message below (file not found), check if you created links to unixODBC librarys:

Error:

[01000][unixODBC][Driver Manager]Can't open lib '/data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/libstreamingpsqlodbca_lib.so' : file not found

[ISQL]ERROR: Could not SQLConnect

Solution:

cd /usr/local/unixODBC/lib

ln -s libodbccr.so.2.0.0 libodbccr.so.1

ln -s libodbcinst.so.2.0.0 libodbcinst.so.1

ln -s libodbc.so.2.0.0 libodbc.so.1

b- Library path

If you receive error message below (file not found), check if you have unixODBC lib folder and ESP odbc folder in your LD_LIBRARY_PATH

Error:

[01000][unixODBC][Driver Manager]Can't open lib '/data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/libstreamingpsqlodbca_lib.so' : file not found

[ISQL]ERROR: Could not SQLConnect

Solution:

export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/

If you continue to receive file not found, check which one is missing in your LD_LIBRARY_PATH using command below:

ldd /data1/hana/shared/BBB/streaming/STREAMING-1_0/odbc/libstreamingpsqlodbca_lib.so

c- Proxy

ESP odbc client have some issues with proxy settings, see: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/7079eef1-53b6-3010-98ae-90e172a82...

Error:

[08001][unixODBC]can't get SQL Server Info

[ISQL]ERROR: Could not SQLConnect

And the error below in strace:

recvfrom(3, "HTTP/1.1 403 Forbidden\r\nCache-Co"..., 16384, 0, NULL, NULL) = 5392

Solution:

export no_proxy=$no_proxy,mo-e0ef78976

d- Debug / strace

isql seems to always show the same error message, doesn’t matter what issue is happening:

[08001][unixODBC]can't get SQL Server Info

[ISQL]ERROR: Could not SQLConnect

I couldn’t find any log files. The way I found out to get a hint about the issue is to use strace command:

strace /usr/local/unixODBC/bin/isql -v esp1

It was with strace that I found out the proxy issue.

 

Richard and Ashok,

I think this tips can help you. If nothing works, try to send me your strace output from isql command.

richard_gu
Explorer
0 Kudos

Dear Braccialli,

Many thanks for such a detailed explain, I will try follow your suggestion and using SP09.

Any result, I will let you know.

Thanks!

Answers (4)

Answers (4)

former_member70923
Discoverer
0 Kudos

Hi,

i'm facing a similar issue with the same error message.

I tried connecting to a ESP project (running on remote Linux machine) using MS Excel as described in the utilities guide. Same error message with ODBC drivers shipped with ESP SP08 PL1 & PL2. The network connectivity is fine as i'm able to connect to the project using ESP Studio from my laptop (Win 7 64-bit).

Parameters I used during DSN creation:

- Workspace/Project

- Server

- port

- SSL (set to disabled as ESP was installed without SSL)

- username (Native OS user on server, used in Studio)

- password

Please let me know if any solution is identified for this.

Regds,

Ashok

0 Kudos

Hi Richard,

Give me a week or so - I will provide you the answer on this. I have not used this for a long time so need to check my old notes.

For the time being, I would prefer you to move to SP09. HANA SP09 have Smart Data Streaming (SDS) which is closely integrated with HANA. It's life cycle is managed by HANA cluster, works with HANA users, can use DU, has monitoring views, integrated with HANA studio, SDS HANA adapter and CCL HANA reference table. I would suggest you to move SDS since this is where we ESP dev team will be putting more afford.

Thanks,

Dilip

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

This one might be obvious, but does TEST_SDA_TO_ESP match the exact name of the window in the ESP project? Within an ESP project, window names are case sensitive. Does this name match the case of the name in the ESP project?

richard_gu
Explorer
0 Kudos

Hi Robert, Thanks for the reply;

the "TEST_SDA_TO_ESP" is the remote source name; The project name is "gujing_cluster_workspace" and the window name is "test_sdk"; the window name is specified in the connection string as "Database=gujing_cluster_workspace/test_sdk"; in ESP the project name and the window name here are all in lowercase.

Do you mean the remote source name should be same as the window name?

Or do you mean the connection string "Database=gujing_cluster_workspace/test_sdk" here is finally converted in uppercase so that can not be found in ESP?

RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

There is a sample of creating a remote source using ESP on page 585 of the SP08 SAP HANA Adminstration guide that I'm looking at. According to the example, you need to specify the project name, workspace name and "user" in that order.

Databasepath : <ESP Project Name>/<ESP Workspace Name>/user

The order in the docs seems a bit strange, but can you try changing your database parameter to be:

Database=test_sdk/gujing_cluster_workspace/user

It then appears that you don't specify the window name until you run the CREATE VIRTUAL TABLE statement.

richard_gu
Explorer
0 Kudos

Hi Robert, In fact I tried this at very beginning but meeting similar error.

In addition, I am very disappointed at this HANA document as some obvious mistakes there:

If page 568, you have:

<ESP PROJECT>/<ESP WORKSPACE>/user

but in page 569, in the example of connection string, we have:

Database=ws1/proj2/user

Anyway, none of them works.

Former Member
0 Kudos

Hello Richard,

The Knowledge Management Team is starting an initiative where we monitor this forum in order to identify areas of improvement for the product documentation. I read your feedback about the HANA Administration Guide. We will investigate how to make this topic more accurate, and more helpful.

Former Member
0 Kudos

Hi,

I really am not familiair with HANA SDA and can't help you with that.

Just wanted to point out one thing that doesn't make any sense.

Apparently you are trying to setup a connection through the ODBC interface.

However you are getting back an error from the JDBC interface talking about Microsoft SQL Server; which is a completely different interface and completely different database and that just doesn't add up. Furthermore ESP doesn't have a Database parameter.
Are by chance using a completely different connection string that doesn't have any resemblance at all with what you provided here?

What is the REMOTE SOURCE designed to use for: ODBC or JDBC?

Are you able to connect with a different datatbase through ODBC?

Maybe you should specify an ODBC DSN parameter and check outside Hana if you can connect with that DSN if it connects successfully.


richard_gu
Explorer
0 Kudos

Hi Ben,

Thanks for the reply. This JDBC error is thrown from HANA studio and HANA studio connect to HANA server using JDBC. for HANA Server to connect to ESP only ODBC is supported. I think here the JDBC indicate nothing but an error happened in HANA studio. The real server side error is "invalid remote object name: can't get SQL Server Info: line 0 col 0 (at pos 0)".

I could use ODBC DSN, but it doesn't work either.

Anyway, thanks for your reply.

Maybe I should ask the question in HANA forum.