3

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.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
davidthegrey
  • 1,205
  • 2
  • 14
  • 23
  • Can HANA do multiple commands, maybe try `CALL sp_name; SELECT 1;`. You could also try a Linked Server – Charlieface Feb 23 '21 at 22:03
  • @Charlieface: a stored procedure in Hana can run multiple statements, of course, but I presume this would yeld multiple recordsets. This is what SQL server does. But I don't think OPENQUERY can handle statements returning multiple recordsets. Anyway this is not my case. My SP only returns one recordset. – davidthegrey Feb 24 '21 at 17:34
  • Well it sounds from the error message that it didn't return any recordsets at all, which was why I suggested you use that syntax. `OPENQUERY` just sends an ad-hoc batch, so if you can do such a batch directly then it should work. As you say, I don't think it handles multiple recordsets, but that was not what I was trying to do. – Charlieface Feb 24 '21 at 17:38
  • @Charlieface I tried your syntax, I get: [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "SELECT": line 1 col 95 (at pos 95) – davidthegrey Feb 24 '21 at 17:47
  • 1
    If you have a single resultset then you can use function with tabular result instead of procedure that is `SELECT`able by default like `select * from myfunc(p1, p2)` – astentx Feb 24 '21 at 21:57
  • Thank you@astentx. This worked perfectly. – davidthegrey Feb 25 '21 at 07:31

1 Answers1

2

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

csy
  • 21
  • 2