Sage 100

Has anyone run across this error when trying to install an update?: Error prepare failed with the following error: create Table failed for MAS_System..CM_Entity\23000:[Microsoft] [SQL Server Native Client 11.0][SQL Server] The create unique index statement terminated because a duplicate key was found for the object name dbo.CM_Entity and the index name 'KPRIMARY_CM_Entity. The duplicate key value is (AP_Vendor, D,).

------------------------------
Kelley Johnson
Kelley Johnson Consulting
------------------------------

I had almost this same message for a premium to premium migration. It ended up that change-tracking was turned on for that table in the SQL database, once we turned that off the error went away. Not sure if this will help you but it is at least something to check!

------------------------------
Kate Krueger
Business Applications Manager
Eide Bailly, Inc.
Denver CO
970-692-5113
------------------------------

Thank you. I will give that a try.

------------------------------
Kelley Johnson
Kelley Johnson Consulting
------------------------------

Original Message:
Sent: 01-21-2021 10:21
From: Kate Krueger
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

I had almost this same message for a premium to premium migration. It ended up that change-tracking was turned on for that table in the SQL database, once we turned that off the error went away. Not sure if this will help you but it is at least something to check!

------------------------------
Kate Krueger
Business Applications Manager
Eide Bailly, Inc.
Denver CO
970-692-5113
------------------------------

Kelly - I ran into EXACTLY your error yesterday installing PUs for 6.10 Premium. In my case Change Tracking was not only turned on in 3 MAS_xxx databases but also for specific tables within them (select * from sys.change_tracking_tables) and that was harder to remove thru SQL Mgmt Studio UI. So I googled it and schlepped this T-SQL together to remove Change Tracking at both the table and db level.

I didn't have time to pretty it up so it would run for all MAS_xxx db's in one shot. Also it's not set to run conditionally, meaning it will complain if Change Tracking is already off but just ignore that error and manually change the 2 MAS_XXX references below to the next company code. After doing this I could click Convert in Company Maintenance without error. 

-- Change database name here and below. Run for every MAS_ database.
USE MAS_XXX
GO

DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.' + t.name + ' DISABLE CHANGE_TRACKING '
FROM sys.change_tracking_tables ct
JOIN sys.tables t
ON ct.object_id= t.object_id
JOIN sys.schemas s
ON t.schema_id= s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;

-- Change database name here
ALTER DATABASE MAS_XXX SET CHANGE_TRACKING = OFF

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------

Original Message:
Sent: 01-21-2021 11:07
From: Kelley Johnson
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

Thank you. I will give that a try.

------------------------------
Kelley Johnson
Kelley Johnson Consulting
------------------------------


Original Message:
Sent: 01-21-2021 10:21
From: Kate Krueger
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

I had almost this same message for a premium to premium migration. It ended up that change-tracking was turned on for that table in the SQL database, once we turned that off the error went away. Not sure if this will help you but it is at least something to check!

------------------------------
Kate Krueger
Business Applications Manager
Eide Bailly, Inc.
Denver CO
970-692-5113

Hi Alnoor

Thank you for your detailed response. In my case, change tracking was not turned on. It turns out there were duplicate records in CM_Entity and CM_UDF. Once those were removed, I was able to complete my update to PU 10 and install 1099 utilities.  This is a new account for me and first time I did any work on this install. It appears after some digging there are issues with custom office that I will have to continue to work on. And I thought this was going to be a simple quick update.

------------------------------
Kelley Johnson
Kelley Johnson Consulting
------------------------------

Original Message:
Sent: 01-22-2021 14:04
From: Alnoor Cassim
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

Kelly - I ran into EXACTLY your error yesterday installing PUs for 6.10 Premium. In my case Change Tracking was not only turned on in 3 MAS_xxx databases but also for specific tables within them (select * from sys.change_tracking_tables) and that was harder to remove thru SQL Mgmt Studio UI. So I googled it and schlepped this T-SQL together to remove Change Tracking at both the table and db level.

I didn't have time to pretty it up so it would run for all MAS_xxx db's in one shot. Also it's not set to run conditionally, meaning it will complain if Change Tracking is already off but just ignore that error and manually change the 2 MAS_XXX references below to the next company code. After doing this I could click Convert in Company Maintenance without error.

-- Change database name here and below. Run for every MAS_ database.
USE MAS_XXX
GO

DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.' + t.name + ' DISABLE CHANGE_TRACKING '
FROM sys.change_tracking_tables ct
JOIN sys.tables t
ON ct.object_id= t.object_id
JOIN sys.schemas s
ON t.schema_id= s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;

-- Change database name here
ALTER DATABASE MAS_XXX SET CHANGE_TRACKING = OFF

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------


Original Message:
Sent: 01-21-2021 11:07
From: Kelley Johnson
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

Thank you. I will give that a try.

------------------------------
Kelley Johnson
Kelley Johnson Consulting


Original Message:
Sent: 01-21-2021 10:21
From: Kate Krueger
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

I had almost this same message for a premium to premium migration. It ended up that change-tracking was turned on for that table in the SQL database, once we turned that off the error went away. Not sure if this will help you but it is at least something to check!

------------------------------
Kate Krueger
Business Applications Manager
Eide Bailly, Inc.
Denver CO
970-692-5113

Original Message:
Sent: 1/22/2021 2:05:00 PM
From: Alnoor Cassim
Subject: RE: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

Kelly - I ran into EXACTLY your error yesterday installing PUs for 6.10 Premium. In my case Change Tracking was not only turned on in 3 MAS_xxx databases but also for specific tables within them (select * from sys.change_tracking_tables) and that was harder to remove thru SQL Mgmt Studio UI. So I googled it and schlepped this T-SQL together to remove Change Tracking at both the table and db level.

I didn't have time to pretty it up so it would run for all MAS_xxx db's in one shot. Also it's not set to run conditionally, meaning it will complain if Change Tracking is already off but just ignore that error and manually change the 2 MAS_XXX references below to the next company code. After doing this I could click Convert in Company Maintenance without error. 

-- Change database name here and below. Run for every MAS_ database.
USE MAS_XXX
GO

DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.' + t.name + ' DISABLE CHANGE_TRACKING '
FROM sys.change_tracking_tables ct
JOIN sys.tables t
ON ct.object_id= t.object_id
JOIN sys.schemas s
ON t.schema_id= s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;

-- Change database name here
ALTER DATABASE MAS_XXX SET CHANGE_TRACKING = OFF

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------


Original Message:
Sent: 01-21-2021 11:07
From: Kelley Johnson
Subject: Error Updating Sage100C Premium 6.00.1.0 to 6.00.10.0

Thank you. I will give that a try.

------------------------------
Kelley Johnson
Kelley Johnson Consulting
------------------------------