CODEWITHSUNDEEP
sqlsql-serverstored-procedureshana
davidthegrey
davidthegrey

Reputation: 1475

Call HANA stored procedure from SQL Server

I have defined a stored procedure in SAP HANA, with 3 IN parameters, that returns a recordset. It works perfectly from the HANA studio when I call it with the CALL sp_name (par1, par2, par3) syntax.

Now I need to call it from a Microsoft SQL Server (I am on 2016). I have a remote linked server connection to the HANA server, which works ok when querying tables and views with both the HANADB..DBNAME.TABLENAME and the OPENQUERY(HANADB, 'SELECT ...') syntax.

I tried hard, but I couldn't find a way to call remote procedures. The 'CALL sp_name (p, p, p)' syntax returns an "object has no columns or current user has no authorizations" error.

I also tried to create the stored procedure in HANA with the LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ViewName. Calling the view from within HANA is now a pain, since I have to use the PLACEHOLDER."$$parametername$$"=>'value' syntax, but it works. If I do that from SQL Server, I get an error

[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;328 invalid name of function or procedure: viewname

Is there a way to call a stored procedure in HANA from SQL server?

More information: If I run the statement in SQL server without including parameters: SELECT * FROM OPENQUERY(hanadb, 'CALL MYDBNAME."MYSPNAME" ()');

I get this error:

[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1281 wrong number or types of parameters in call: DATAIN is not bound: line 1 col 6 (at pos 5

If I include the parameters, I get the error:

Msg 7357, Level 16, State 2, Line 1 Could not process object "CALL MYDBNAME."MYSPNAME"('2021-01-01T00:00:00.000Z', '2021-02-01T00:00:00.000Z', 'TOLUENE')". The OLE DB provider "MSDASQL" for linked server "hanadb" indicates that the object has no columns or that the current user does not have the necessary permissions.

In the second case the error is generated by SQL server, while in the first by Hana. I don't know if there is a way to work around this.

Upvotes: 3

Views: 5224

Answers (1)

csy
csy

Reputation: 21

please try like this. EXEC ('call schema_name.sp_name (''p1 value'',''p2 value'')') AT hana_linked_server_name

Upvotes: 2

Related Questions