======3.2. HANA ======
===== 3.2.1. Violation of primary key constraint =====

**Issue** \\
When performing some operation in Produmex, you get the following error message:

''<color red>Could not execute 'INSERT INTO PMX_xxxx (...)\\ 
[301]: unique constraint violated: Table(PMX_xxxx)</color>''

**Solution** \\
When the system cannot insert new records into PMX tables, it most usually means that the key sequences are inconsistent, and need to be rebuilt.

This typically occurs after importing a database, or importing data with the Produmex import tool. The reason is that the sequences are reinitialized by the import process, so the next insert would try to insert key 1 again, leading to primary key issues.

In these cases the procedure below needs to be executed, in order to rebuild the Produmex sequences from the actual data found in the tables.

//Note: in  Produmex version 5.1 and older, the procedure doesn't exist and you need to create it before executing it. You can create the procedure with the following code://

<code sql>CREATE PROCEDURE "PMX_SP_RebuildSequences" ()
LANGUAGE SQLSCRIPT AS
	v_seqName NVARCHAR(256);
	v_resetByQuery NCLOB;
	v_restartWith BIGINT;
BEGIN
	DECLARE CURSOR cur FOR
		SELECT SEQUENCE_NAME, RESET_BY_QUERY FROM sys.sequences WHERE SCHEMA_NAME = current_schema AND SEQUENCE_NAME LIKE 'PMX_%' AND LENGTH(RESET_BY_QUERY) > 0;
	OPEN cur;
	FETCH cur INTO v_seqName, v_resetByQuery;
	WHILE v_seqName IS NOT NULL DO
		DELETE FROM TMP_TN_CharListTable;
		EXEC 'INSERT INTO TMP_TN_CharListTable ("idx") ' || v_resetByQuery;
		SELECT TOP 1 "idx" INTO v_restartWith FROM TMP_TN_CharListTable;

		EXEC 'ALTER SEQUENCE "' || current_schema || '"."' || v_seqName || '" RESTART WITH ' || v_restartWith || ' RESET BY ' || v_resetByQuery;

		FETCH cur INTO v_seqName, v_resetByQuery;
	END WHILE;
END;
</code>

After having created it, or if it was already existing, you have to execute the procedure the following way:
<code sql>CALL "PMX_SP_RebuildSequences" ()</code>

===== 3.2.2. Inconsistent calculation model =====

**Issue** \\
Sometimes an SQL query can fail with an error message similar to this:

''<color red>[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] General error;2048 column store error: search table error:  [34011] Inconsistent calculation model;CalculationNode(TABLE_EW_PRODUCTION:hana01:30003:COL_0x7f0926c872000x7f09260a5e701) -> attributes -> attribute (COL38):Datatype string(1) of COL38 does not match to datatype fixedstring(1) in datasource</color>''

**Solution** \\
This error is usually not an SQL syntax error, but an error on the HANA server. If the error persists, restarting the HANA server should solve the issue in most of the cases.


===== 3.2.3. Invalidated procedure =====

**Issue** \\
It can happen that a procedure needs to be recompiled after having been modified. When this happens, you'll get an error message like the following:

''<color red>Could not execute 'CREATE PROCEDURE SBO_SP_TransactionNotification ( in object_type nvarchar(20), -- SBO Object Type ...' in 14 ms 515 µs . 
SAP DBTech JDBC: [430] (at 961): invalidated procedure: PMX_SP_TransactionNotification: line 32 col 7 (at pos 961)</color>''

**Solution** \\
To solve this problem, you have to recompile the invalidated procedure. This can be done as follows:
<code sql>ALTER PROCEDURE "<yourSchema>"."PMX_SP_TransactionNotification" RECOMPILE</code>
or
<code sql>ALTER PROCEDURE "<yourSchema>"."SBO_SP_TRANSACTIONNOTIFICATION" RECOMPILE</code>

Note that if the ''SBO_SP_TRANSACTIONNOTIFICATION'' procedure is the invalidated procedure, it's impossible to book transactions or modify master data in SAP B1: strange red error messages come every time.

If this doesn't help, you can also try to re-create completely the stored procedure, with the ''DROP PROCEDURE'' and ''CREATE PROCEDURE'' commands.

===== 3.2.4. Crystal report cannot connect to HANA server =====

On MSSQL, the connection data will be replaced dynamically when printing, therefore it is not necessary to save the report with the actual connection data.

On HANA, the connection data will not be replaced dynamically therefore it is necessary to save the report with the actual connection to the right database and schema. 
Use the “ODBC (RDO)” connection type, with the following connection string:
<code>DRIVER={HDBODBC32};SERVERNODE=yourserver:30015;DATABASE=yourdatabase</code>

===== 3.2.5. Cannot display view definition in HANA Studio=====

**Issue** \\
In some old versions of HANA Studio, you might get the following error message when trying to open the definition of a view:

''<color red>Invalid table name: Could not find table/view M_VIEW_CACHE in schema PUBLIC</color>''

**Solution** \\
First try to upgrade your HANA Studio installation to the latest available version.

If that's not an option, you can get the view definitions with the following query:
<code sql>SELECT CAST(DEFINITION AS NVARCHAR(4000)) FROM PUBLIC.VIEWS WHERE SCHEMA_NAME = 'YOUR_SCHEMA' and VIEW_NAME = 'YOUR_VIEW'</code>

===== 3.2.6. Cannot create pick list proposal due to special character in address=====

**Issue** \\
The creation of a pick list proposal fails when some special Unicode characters are present in the business partner's address.

For example, the Canadian flag can be represented by [[https://emojipedia.org/flag-for-canada/|these Unicode characters]], which causes this issue:

''<color red>ERROR [42000] [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near "27 Kings College Circle Canada ????\\
Toronto ON M5S 1A1\\
CANADA": line 1 col 542 (at pos 542)'</color>''

**Solution** \\

Remove the special characters from the business partner's address.

===== 3.2.7. SYSTEM user is locked =====

**Issue** \\

With default HANA server settings, the SYSTEM user will be automatically locked after a number of failed login attempts.

When this happens, HANA Studio will also fail to connect to the HANA server with the SYSTEM user. The error message is\\
''<color red>Invalid user name or password</color> <color blue>__Change...__</color>''

{{:support:supportguidev2:dbissues:systemuserlocked.png?nolink|}}

**Solution** \\

It's possible to disable the automatic locking of the SYSTEM user by changing the //password_lock_for_system_user// parameter to false.

{{:support:supportguidev2:dbissues:passwordlockforsystemuser.png?nolink|}}

Instructions to reset the SYSTEM user's password are described in [[https://launchpad.support.sap.com/#/notes/2274157|SAP Note 2274157]]