Installation and Technical Issues

[jpendleton]

I'm getting this error when activating the Inventory Management Module.

Violation of Primary Key constraint PK_tciImportTarget_5DD47778.  Cannot insert duplicate key in object 'tciImportTarget'

Tried deleting the row in tciImportTarget but we do have a couple of companies with IM activated, probably before certain upgrades so it can't be removed.  Any idea where else to look.  What does 5DD47778 refer to?

[Wei]

During the company activation, there are log files written in your user data folder (same folder as the MAS 500 Application.Config file),  It should give you some clues for the error. Or you can use SQL Profiler to find what key values casue the violation.

You can use sp_help tciImportTarget to find out PK_tciImportTarget_5DD47778, it is a clustered, unique, primary key for this table, the column is ImportTargetKey.

[jpendleton]

Thanks for the ideas.  I'm still stuck, but did figure out how to run a trace.  It's trying to do this.

INSERT INTO acuity_app..tciImportTarget (ImportTargetKey, ImportTargetID, Description) VALUES (0, 'Beginning Bal', 'IM Beginning Balance Import'

But there isn't a preexisting ImportTargetKey of 0.  Although I can't manually insert one into my test database, without getting the same Primary Key error. 

Oh well, maybe Monday will bring an answer.

[Wei · Accepted solution]

For the most of MAS 500 tables, when inserting 0 to the table, the insert trigger has the code to get a new surrogate key then use that key to insert to the table;

If you run this T-SQL:

sp_helptext tI_tciImportTarget

You'll find the trigger to code to handle the 0 surrogate key:

if (@newKey = 0)

begin

exec spGetNextSurrogateKey 'tciImportTarget', @newKey output

update tciImportTarget set ImportTargetKey = @newKey

where ImportTargetKey = 0

end

In your case, please look at the trigger code and make sure the code is there and check the surrogate key table and make the next key is the right one (the max(ImportTargetKey) + 1).

[jpendleton]

Thank you.  The code was there, I had seen it in the trace, now I understand the system a little better.  There were two keys that had not been incremented.