Upgrade - Error (check ID 39)

[former_member213205]

HI all,

Im trying to upgrade a test environment from SAP BO 882 pl 04 to SAP B0 882 pl 06.

After the pre-upgrade check, i get a failed on checkID 39 with description - Change tracking is on, switch change tracking off in "B1 analytic on IMCE 1.0"

It also mentions note 1663948, which isnt released.

Any idea's on how perform this upgrade?

[former_member213205 · Accepted solution]

the B1i analytic Admin Console doesnt ring a bell... any more detailled help?

[Former Member · Accepted solution]

Hi,

Did you get a solution to this issue?

What is BI analytic and where is it turned on/off?

I have the same issue upgrading from 882PL4 to later patches i.e PL5/6/7

Regards

George

[former_member186095 · Accepted solution]

Hi George,

If you read carefully the note, you will get the point.

You see that change tracking feature in the SQL server 2008 will be turned on if you install B1 Analytic on IMCE 1.0 in B1 landscape. The note said that you must detach SAP B1 db from B1 Analytic Admin Console

So, you can detach it by turning the change tracking off i.e. set to False value in the db properties. See this following screen capture here:

you may check this link to know more about change tracking:

http://www.sql-server-performance.com/2009/change-tracking-2008/

Good luck

Rgds,

JM

[Former Member · Accepted solution]

Thank you Jimmy.

My issue has been sorted and DB upgraded

Regards

George

[former_member213205 · Accepted solution]

My apologies for the late respons.

Checking out that value didnt work for me, i had to find all tables who also had the change tracking value to true... turned out to be about 8 tables. I turned them all off, AND on the sql server, re-ran the upgrade wizard, and put them back to change tracking.

Regards

Frederik

[Former Member · Accepted solution]

Hello!

How can I see which tables are affected?

Thanks!

[Nicolas_Flores · Accepted solution]

You could try this query

USE UR_DATABASE]

GO

SELECT s.name as Schema_name, t.name AS Table_name, tr.*

FROM sys.change_tracking_tables tr

INNER JOIN sys.tables t on t.object_id = tr.object_id

INNER JOIN sys.schemas s on s.schema_id = t.schema_id

[Former Member]

But how can I see which tables are affected? SAP has too many tables and it's really hard to check on it one by one.

[Former Member]

Hi Leigh,

I recommend you open an incident to SAP Business One Product Support, we can help you with this matter.

Regards,

Paul Finneran

SAP Business One Product Support

[Former Member]

Hi Paul,

I already find the solution. This is due to the add-on installed in SAP B1. At first I tried to change the database tracking into false in SQL but failed to do so because I need to check first which tables are affected. I executed the query below and the upgrade was successful.

ALTER TABLE SPP1 DISABLE CHANGE_TRACKING

ALTER TABLE SPP2 DISABLE CHANGE_TRACKING

ALTER TABLE OSRN DISABLE CHANGE_TRACKING

ALTER TABLE OALI DISABLE CHANGE_TRACKING

ALTER TABLE OSCN DISABLE CHANGE_TRACKING

ALTER TABLE OBTN DISABLE CHANGE_TRACKING

ALTER TABLE OSPG DISABLE CHANGE_TRACKING

ALTER TABLE ITM1 DISABLE CHANGE_TRACKING

ALTER TABLE OCRD DISABLE CHANGE_TRACKING

ALTER TABLE ORTT DISABLE CHANGE_TRACKING

ALTER TABLE OITM DISABLE CHANGE_TRACKING

ALTER TABLE OSPP DISABLE CHANGE_TRACKING

ALTER TABLE OPLN DISABLE CHANGE_TRACKING

ALTER DATABASE Pagadian

SET CHANGE_TRACKING = OFF

Regards,

Ashley

[jorgerios-aramayoalbrisi]

Ashley:

how can I see which tables are affected?

Icheck the tables you say and execute the query but continue with the error.

Thx and Regards,

[ramesh_kumar]

Include the following table also

ALTER TABLE CRD1 DISABLE CHANGE_TRACKING

ALTER TABLE ITM9 DISABLE CHANGE_TRACKING

[jorgerios-aramayoalbrisi]

Thank you Ramesh Kumar!

That tables have the issue!

Them I could change DB > properties > Change Tracking > False

and update to the patch

Thakn you again!!!

[wale_adegbola]

I've tried all this and still didnt work still gave me this error message.

Find Attach Error Message.

[Former Member]

Hi Adewale,

Please open a support incident to SAP, we will look into this issue for you

Regards,

Paul Finneran (mod)

SAP Business One Support

[Nicolas_Flores]

USE [UR_DATABASE]

GO

SELECT s.name as Schema_name, t.name AS Table_name, tr.*

FROM sys.change_tracking_tables tr

INNER JOIN sys.tables t on t.object_id = tr.object_id

INNER JOIN sys.schemas s on s.schema_id = t.schema_id

[former_member211398]

Hi Adewale,

Maybe you have solved this issue, if not, this is how I solved:

1. In the database affected run this query:

select sys.schemas.name as Schema_name, sys.tables.name as Table_name from sys.change_tracking_tables

join sys.tables on sys.tables.object_id = sys.change_tracking_tables.object_id

join sys.schemas on sys.schemas.schema_id = sys.tables.schema_id

It shows you what tables have tracking enabled.

2. For each table with tracking enable run this query:

ALTER TABLE Table_Name DISABLE CHANGE_TRACKING

3. Rigth clic in the database with tracking enable, choose Properties>Change Tracking; once there you have to change the value of Change Tracking to False, and click on OK

4. Close SQL Management Studio and run the upgrade wizard again.

Hope this could help to others.

Regards.

[marcel_schomaker]

Here a script i use for DBs which are used in Analytics

USE <DATABASE>

EXEC sp_msforeachtable "ALTER TABLE ? DISABLE CHANGE_TRACKING"

   , @whereand=" and exists (

   select null

   from sys.change_tracking_tables ctt

   where ctt.object_id = o.id

)

"

go

declare @sql varchar(100)

set @sql='alter database '+quotename(db_name())+' SET CHANGE_TRACKING = OFF';

exec(@sql)

[former_member184718]

Hi Frederik,

Note 1663948 has been released.

1663948 - Install “B1 Analytic on IMCE 1.0” in B1 landscape, then try to upgrade any B1 company database in the landscape.

Symptom

The replication service of “B1 Analytic on IMCE 1.0” uses the Change Tracking mechanism in SQL Server 2008. So if “B1 Analytic on IMCE 1.0” is installed in B1 landscape, the Change Tracking on B1 company databases will be turned on, which may lead the failure of the B1 upgrade.

Reproducing the issue

Install “B1 Analytic on IMCE 1.0” in B1 landscape, then the Change Tracking on B1 company databases will be turned on.

Cause

The technical root cause is if the Change Tracking is on with one table, then any operation to change table structure will fail.

Solution

The company database should be detached in B1 Analytic Admin Console before running company upgrade.

Hope this helps.

Hari

[former_member213205]

the B1i analytic Admin Console doesnt ring a bell... any more detailled help?

[Former Member]

Hi Frederike,

Note 1663948 has been released and you should be able to access it now.

Regards,

Paul Finneran

SAP Business One Product Support

[Former Member]

Dear Mr. Frederik Lootens,

As per the issue description, we would request you to kindly log a support message with component SBO-IMCE-COM, for further investigation of the issue.

Thanks.

Regards,

Abhinav Banerjee

SAP Business One Product Support

[Johan_H]

Hi Frederik,

There do not seem to be any notes about this error (except the one you mentioned, which is not released).

Considering that the precheck did not alert about anything your best option is probably contacting SAP support.

Alternatively you could try to upgrade to another patch level (you may have to wait then of course).

Regards,

Johan

[former_member213205]

Its the pre check that failed with the error.

I tried with pl 05 and pl 06 and both give the same error.