Error Creating Company in Dynamics GP – Violation of Primary Key Constraint PKSY60100

That is a mouthful to say the lease.  So one of the projects I’m working on is migrating our Dynamics GP 2010 database off the existing SQL 2008 R2 server and moving it to a fresh new SQL 2008 R2 server.  So I copied over all the backups and restored them.  I thought everything was golden as I was able to go get inside GP and work without issues.

Then I needed to create a new company to test some things out.  I went through the process of creating the company using GP Utilities and everything was going awesome until the end of the creation during the final stages of loading the stored procedures.  I get the error below.

error

Well I click on OK and think everything will be all right and I let the process finish.  Then when I launched GP the new company was not in the list of companies I could choose from.  So I do what every IT person does I deleted everything and ran through the entire process again.  Well as luck  would have the same results where produced.  I’m not going to lie I ran through it for a 3rd time after I restarted the SQL Server thinking that would clear it up.  WRONG!

HAAALP me Google!

Turns out Microsoft knows of this error as indicated by KB871699.  In reading about the error the problem boils down to what SQL user account is set as the OWNER of the Dynamics database.  I completely forgot to check this when I migrated over the databases to the new SQL server.  When I created the blank Dynamics databases I was logged in as SA so by default that user is the database owner and not the DYNSA account which is required by GP Utilities to create new companies.

So as Microsoft suggested I ran sp_helpdb and validated that yes SA was the owner and not DYYNSA.  To correct my mistake I ran the following query as suggested by Microsoft against both the Dynamics and my existing company database.

USE Dynamics; GO --sp_changedbowner 'DYNSA'; GO USE YOURCOMPANYDB; GO --sp_changedbowner 'DYNSA'

 

image

Once I changed the ownership of the database life was golden and I have a nice new company in Dynamics GP.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *