Q: Who is affected?
A: People running HANA on following patches SAP 9.1 PL10-13 + SAP 9.2 PL00-07 (MSSQL is not affected). This also affects databases converted from MSSQL to SAP HANA.
Q: What is this bug and how does it manifest itself?
A: The problem can manifest itself in many different ways but are normally happening during startup of the add-on after an upgrade. It presents itself as an unexpected error in older add-on version and mention one of the following issues:
- Some SQL sentence could not execute with no explanation why
- That a transaction could not be committed
- That a record could not be added
- That a new table or field could not be added
The result is that the Boyum add-on can't be started.
Q: How do I fix the issue?
A: You will need to go to HANA Studio and update the SAP Stored procedures ("SBO_SP_TransactionNotification" and "SBO_SP_PostTransactionNotice") from NVARCHAR(20) to NVARCHAR(30) [See comment below on how to do that] + check that any customization inside the stored procedures can also handle the new length (see below). You can also read the official SAP not on the issue here: https://launchpad.support.sap.com/#/notes/2228417
Q: I've have already fixed the NVARCHAR(20) to NVARCHAR(30) but I still have the issue. What to do?
A: In such a case you most likely have customization the Stored Procedures done by consultants or other add-ons. A very common issue is that other add-ons (Beas/TaskCenter/Enprise etc.) inside SAP's SPs call their own Stored Procedure. If such a stored procedure has not been updated it most likely only accept NVARCHAR(20) and the SAP's SP try to call with more than 20 they fail resulting in the error.
What you need to do in such a situation is you need to contact the people in charge of the modifications made and have them fix it (refer to this post or the SAP note listed below if they do not know what you are talking about). Boyum can't help with such modifications! Here is a sample of what you can write to them
***
Hi <company that made the Stored procedure>
For Customer <customername> running HANA I've noticed that you have modifications in Stored Procedure "SBO_SP_TransactionNotification". These modifications are caused an issue with add-on B1 Usability Package (another add-on created by Boyum IT Solutions) that the customer is using.
I've been told by the Boyum support that the issue is most likely do to the call of your Stored Procedures in "SBO_SP_TransactionNotification". They suspect that your stored procedure can max handle the old length of NVARCHAR(20) for variable object_type and not the new NVARCHAR(30) that HANA uses.
Can you give me information on how to check/fix that you support object_type = NVARCHAR(30)
***
Q: Why are you more affected an not others?
A: It is related to how long add-ons User-defined Table codes are. If they are higher than 16 the issue occurs. We have many tables that are affected by this compared to others and that is the main reason but all add-ons with long table-names are affected.
Q: What is done to fix this?
A: SAP knows of the issue and has planned to fix this in SAP 9.2 PL06. Please know however that the fix is only for new databases so all above is still relevant if you upgrade. The official SAP Note on the matter is here: https://launchpad.support.sap.com/#/notes/2228417
From our side, we have made it so B1UP 2016.11 HF1 have code in it that try to auto fix for people with unmodified Stored Procedures but ultimately only SAP and the Add-on providers that use the SP's can fix this.
Do you have more info?
Please see this video on how to troubleshoot Stored Procedure issues in general.
Comments
3 comments
FYI: With SAP Business One 9.1 PL10 SAP now solves this issue for new databases by making ObjectType 30 chars default.
Existing databases still need to apply above option 2 to resolve the issue.
As a service note it also mean that if you work with other Addons/SSPs that use the StoredProcedures with custom code you should expect that they need to update their custom SQL to ObjectType = 30 instead of 20...
It does not affect B1UP further as we do not add custom code to SBO_SP_TransactionNotification.
Warning: Only use on databases that does not have any modifications!
Script that modifies the procedures to work correctly:
DROP PROCEDURE SBO_SP_PostTransactionNotice;
-- B1 DEPENDS: BEFORE:PT:PROCESS_START
CREATE PROCEDURE SBO_SP_PostTransactionNotice
(
in object_type nvarchar(30), -- SBO Object Type
in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255)
)
LANGUAGE SQLSCRIPT
AS
-- Return values
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed
begin
error := 0;
error_message := N'Ok';
--------------------------------------------------------------------------------------------------------------------------------
-- ADD YOUR CODE HERE
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select :error, :error_message FROM dummy;
end;
DROP PROCEDURE SBO_SP_TransactionNotification;
-- B1 DEPENDS: BEFORE:PT:PROCESS_START
CREATE PROCEDURE SBO_SP_TransactionNotification
(
in object_type nvarchar(30), -- SBO Object Type
in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255)
)
LANGUAGE SQLSCRIPT
AS
-- Return values
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed
begin
error := 0;
error_message := N'Ok';
--------------------------------------------------------------------------------------------------------------------------------
-- ADD YOUR CODE HERE
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select :error, :error_message FROM dummy;
end;
FYI Regarding known issues with this SAP Change:
- beas add-on aleready correct this in latest version
- Orbis TaskCenter fixes this from version 2.2
Please sign in to leave a comment.