======3.1. Database Upgrade ======

===== 3.1.1. Unable to end SQL transaction (HANA) =====

**Issue** \\
On HANA, sometimes you can get the following error message while executing the Produmex database upgrade:


''Error: TargetInvocationException: Exception has been thrown by the target of an invocation. --->
ProdumexError 41028: SBO error when updating BO with key 'OITM.PMX_UMMT' of type 'Produmex.Foundation.Data.Sbo.BusinessObjects.SboUserFieldMD' in SBO with error code '-1116' and description 'Unable to end SQL transaction'''

**Solution** \\
In this case, you have to:
  * disable the stored procedure ''PMX_SP_TransactionNotification'' by removing the corresponding code from the ''SBO_SP_TRANSACTIONNOTIFICATION'' procedure
  * execute the Produmex database upgrade again
  * re-enable the ''PMX_SP_TransactionNotification'' procedure

===== 3.1.2. Cannot use duplicate view name  =====
**Issue** \\
In some cases you can get the following error message while executing the Produmex database upgrade:

''transaction rolled back by an internal error: cannot use duplicate view name: PMX_SSCC_SHIPPING_LABEL''

Most of the time this error occurs when the view mentioned in the error message exists in both the company database and the Produmex database. This cannot happen for PMX views, as the Produmex database upgrade will automatically create synonyms for views between the 2 databases, and this doesn't work if the synonym to be created already exists as a view.

**Solution** \\
Remove the unneeded view. If unsure, and if it's not a custom view, you can remove the view in both databases, as the Produmex database upgrade will re-install the view where it's needed.



===== 3.1.3. Unable to update DB Tools (SQL) =====

**Issue** \\

{{ :implementation:sqlcollation.png?400 |}}

''Error: ComException Cannot resolve the collation conflict between "SQL_Latin__General_CP850_CI_AS" and "SQL_Latin_General_CP1_CI_AS" in the equal to operation.''

**Solution** \\
On database level, the collation should be **SQL_Latin1_General_CP850_CI_AS** 

{{ :implementation:sqlcollationsolution.png?400 |}}

<code>
--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'MyDatabase';
GO
</code>

When installing PDMX on a database with collation SQL_Latin1_General_CP1_CI_AS The installer will first add stuff to this DB like UDF, tables etc, and afterwards we get an error saying that the collation is not correct.
The problem is that at this time, it’s too late to change the collation to SQL_Latin1_General_CP850_CI_AS because when we change it on database level, the UDF’s aren’t changed and they stay on CP1.
 
The solution is to drop the database and restore it back with the basic SAP DB (without PDMX fields), change the collation and redo the PDMX installation.

===== 3.1.3. Error updating UDF during DB upgrade=====
**Issue** \\
A UDF cannot be updated with the Produmex database upgrade tool.

**Solution** \\
  * try to update the same UDF with the SAP B1 client. 
  * Disable the call to our SP from within the SBO_SP_TransactionNotification.
  * Stop running the Produmex add-on loader
  * Recompile PMX_SP_TransactionNotification

===== 3.1.4. Error updating UDF during DB upgrade=====

**Issue** \\
When upgrading Produmex WMS to a newest version the following error may come up:\\ ''Error: SqlException: Error: The new name 'IX_PMX_OSEL:TypeCode' is already in use as a INDEX name and would cause a duplicate that is not permitted.''

**Solution** \\
  * Delete the following database index in your system: **Tables->dbo.PMX_OSEL->Indexes->IX_PMX_OSEL:TypeCode (Non-Unique, Non-Clustered)**
  * Then run the database upgrade process again

{{ :implementation:general:deleting_database_index.png?nolink |}}