Web Intelligence – Unable to insert comment
November 30, 2016 by: David LaiThe ability to add commentary to web intelligence reports is a powerful new feature released in SAP BusinessObjects 4.2.
However, it does not work straight out of the box.
Here are some steps to get comments to work.
Step 1
Create a 32 bit version of the audit odbc connection on the SAP BusinessObjects server. Then restart the Server Intelligence Agent (SIA) from the Central Configuration Manager.
This should solve the issue 90% of the time.
Step 2
Separate the commentary service from the APS and put it in it’s own APS.
Then restart the commentary APS. There is a small chance this may solve the issue if Step 1 did not work.
Step 3
This was a bizarre find, as I had to look through the APS.Commentary_trace log.
I found the following database insert error.
2016 11 30 11:14:44.337|CMNTRY 009 – Database Layer: Failed to execute query with message:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column ‘CommentID’, table ‘boaudit.dbo.COMMENTARY_MASTER’; column does not allow nulls. INSERT fails.
The weird thing is that, I didn’t get that error in a working installation of SAP BusinessObjects. So I took the drop-create table script from a working audit database and replaced the COMMENTARY_MASTER table in the bad audit database.
Below is the script you can use if you are on MS SQL SERVER. Same concept though if your audit database is on DB2, Oracle, etc.
/****** Object: Table [dbo].[COMMENTARY_MASTER] Script Date: 11/30/2016 1:44:46 PM ******/
DROP TABLE [dbo].[COMMENTARY_MASTER]
GO
/****** Object: Table [dbo].[COMMENTARY_MASTER] Script Date: 11/30/2016 1:44:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COMMENTARY_MASTER](
[CommentID] [int] IDENTITY(1,1) NOT NULL,
[DocId] [varchar](250) NOT NULL,
[CommentText] [varchar](2000) NOT NULL,
[UserCuid] [varchar](100) NOT NULL,
[UserName] [varchar](255) NULL,
[LastModifiedUserCuid] [varchar](100) NULL,
[LastModifiedUserName] [varchar](255) NULL,
[CreationTimeStamp] [bigint] NOT NULL,
[LastModifiedTimestamp] [bigint] NOT NULL,
[LogicalReportId] [varchar](100) NULL,
[ContextCommon] [varchar](2000) NULL,
[AppName] [varchar](100) NULL,
[AppContext] [varchar](2000) NULL,
[AppDatapath] [varchar](2000) NULL,
[OptionKey1] [varchar](2000) NULL,
[OptionKey2] [varchar](2000) NULL,
[OptionKey3] [varchar](2000) NULL,
[OptionKey4] [varchar](2000) NULL,
[Hidden] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CommentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The issue was that the bad version did not have CommentID as an Identity column.
[CommentID] [int] IDENTITY(1,1) NOT NULL,
Hi,
Thanks for this, helped me solve our issue – with some tweaking:
We have our audit database in Oracle 12c and have upgraded from BO 4.1 (SP6) to BO 4.2 (SP3). We ran into the same interface error message (“Unable to insert comment (Error: INF)”) – but when looking in the APS.Commentary_trace log we got another error entry stating:
“[…] CMNTRY 009 – Database Layer: Failed to execute query with message:ORA-02289: sequence does not exist”
the solution to this was to run below create-statement in the Audit database:
CREATE SEQUENCE commentID_sequence;
After that commentary works.
Hi David,
I am getting the same error while inserting a comment. I have got a separate schema created for commentry service on Oracle database and a new APS has been created but none of this resolves the issue.
Please advise.
Regards
Navjot