How do I get round error could not read working days information from table TFACS

[stephenl1]

I have a stored procedure that uses Add_workdays and Workdays_between. When I run this stored procedure from within SAP HANA Studio it runs without a hitch, but when I try and run it through ODBC driver for a Crystal Reports report that I wish to develop, I get the following error

"Database Connector Error: 'S1000:[SAP AG][LIBODBCHDB32 DLL]pHDBODBC32] General error;2048 column store error: "_SYS_BIC"."aklc/ZSP_PNC001_PROCESSING_SUMMARY": line 7 col 1 {at pos 237): column store error: [70000000] could not read working days information from table TFACS "R001813"."TFACS" - invalid table name: Could not find table/view TFACS in schema R001813: line 1 col 134 (at pos 133) at /sapmnt/Id7272/a/HDB/Jenkins_prod/workspace/HANA_FA_CO_LIN64 GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/catalog/catalog.cc:598 5 [Database Vendor Code: 2048 ]".

The gist of the error seems to be that when executing the stored procedure through the ODBC it thinks it needs to be looking in my personal catalogue not the default catalogue I identified when activating the stored procedure. I am unsure why it should do this. How do I change this so it correctly looks in the correct catalogue, which in my case is SAPAD1, and the table TFACS does exist in this catalogue?

Thanks

Stephen

[pfefferf · Accepted solution]

Both functions WORKDAYS_BETWEEN and ADD_WORKDAYS have an optional parameter to specify the source schema. With that you can define in your procedure (when you call the functions) from which schema table TFACS should be used.

[stephenl1 · Accepted solution]

Thanks I was missing specifying the default schema. Interesting that it needs that; I come from a Microsoft SQL server background and we would never specify a default database like this, as it would mean having to change the code between development and production versions. Luckily the schema is the same in HANA across development and production so I can get away with specifying this. Do you know why it has been done this way? I am curious to know why this works when running it in SAP HANA studio without the default schema specified, but only works from Crystal Reports with the default specified?

[pfefferf]

I assume that the usage of table TFACS in your procedure is unqualified (no schema used in procedure to identify the table). If that is true, and you defined also not default schema for the procedure the current schema of the session is used, which is the schema of the user. For your HANA studio execution I think you used something like SET SCHEMA or you were logged on with the technical SAPAD1 user?

If all your systems uses the same SAPAD1 schema the easiest (but not recommend solution) could be to just add the schema name to the procedure to identify the table name within the schema. But I would recommend to either use synonyms or a default schema with schema mapping to identify the table in case of different schema name (e.g. for DEV, QA, Prod systems) or to be able to be more flexible in case the schema name is the same on all systems.

Regards,
Florian

[stephenl1]

Thanks, I don't qualify table TFACS in my procedure at all. My procedure simply uses the in-built SQL Script function WORKDAYS_BETWEEN as follows.

Select workdays_between('Z3', a.Technical_descision_date, current_date) as Work_days_since_TD_Date

from table...

It is the built in function WORKDAYS_BETWEEN that references the table TFACS, and I have no control of the code behind this procedure.

When I run the procedure in HANA Studio it runs without a problem. But when I use this same procedure via Crystal Reports, that is when it brings up the error.

When I created the procedure in SAP HANA Studio the first thing brought up was a screen headed New SQL Procedure, in which I identified SAPAD1 as the default schema. I have checked the properties panel for the procedure and it is still showing SAPAD1 as the default schema.

When I log into SAP HANA Studio, I log in as myself. The catalogue view under SAP HANA Modeler, shows the following

AD1@HD1 (R001813) CRM Devleopment --- I am user R001813

> Catalog

> Public Synonyms

> R001813

> SAPAD1

So SAP HANA can see both catalogs but of course I run my procedure in HANA Studio from an SQL session opened in SAPAD1. The problem is that Crystal Reports is trying to run it over R001813. I can see no settings in the ODBC driver to tell it to point at SAPAD1. I have used Microsoft ODBC drivers in the past with Crystal Reports, and the config for Microsoft ODBC allow you to specify the database, but HANA's ODBC driver does not give you any option for selecting the catalog.

Further to above, if I drop the line from the procedure that use the built in function workdays_between, then the procedure runs fine in Crystal Reports, selecting data from all other tables that are also in SAPAD1 without issue. The problem appears to thus be with the workdays_between function. The same problem also occurs with the add_workdays function.

The version of HANA I am using is 7.4 and SAP HANA Studio Version: 2.0.19

[stephenl1]

To get round the schema problem I was encountering when reading the stored procedure via Crystal Reports, it became necessary to specify the schema when using WORKDAYS_BETWEEN and ADD_WORKDAYS. I found that my schema between dev., test, QA and production was different, so hard coding the schema name into the function parameters was not acceptable. To overcome the problem I used the following code. This places the schema into a variable and uses that variable as the function parameter.

Declare V_Schema varchar(20);
select physical_schema into V_Schema from "_SYS_BI"."M_SCHEMA_MAPPING" where authoring_schema = 'SAP_CRM';
select add_workdays('Z3',a.Technical_descision_date,2,V_Schema) as TD_plus_2_Days
, workdays_between('Z3', a.Technical_descision_date, current_date,V_Schema) as Work_days_since_TD_Date
from table;