Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you create a company by using Microsoft Dynamics GP Utilities: "Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'"

Error message when you create a company by using Microsoft Dynamics GP Utilities: "Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'"

View products that this article applies to.

Symptoms

When you create a company by using Microsoft Dynamics GP Utilities, you may receive the following error message:
Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'.
Additionally, you may receive the following error message in the Dexsql.log file:
/*

BEGIN
DECLARE @stored_proc_name char(34)
DECLARE @retstat int
DECLARE @param2 int
set nocount on
SELECT @stored_proc_name = 'Database_name.dbo.smGrantAccessForDboAndSa'
EXEC @retstat = @stored_proc_name Number, @param2 OUT SELECT @retstat, @param2 set nocount on
END

/* [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'.*/

/* [Microsoft][SQL Native Client][SQL Server]The statement has been terminated.*/

↑ Back to the top

Cause

This problem occurs because the database owner of the DYNAMICS database is not DYNSA. Additionally, the database owner may be the same as the current user. The smGrantAccessForDboAndSa stored procedure inserts two records into the SY_User_Company_Access_REL (SY60100) table. One record refers to the current user, and the other record refers to the database owner. Typically, the two users are sa and DYNSA. If the database owner for the DYNAMICS database is the same as the current user, the stored procedure tries to insert the same user two times. The second Insert statement generates the error message about the primary key violation.

↑ Back to the top

Resolution

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.
  1. Start the Support Administrator Console, Microsoft SQL Query Analyzer, or SQL Server Management Studio. To do this, use one of the following methods depending on the program that you are using.

    Method 1: For SQL Server 2000

    If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.

    Method 2: For SQL Server 2005

    If you are using SQL Server 2005, start SQL Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

    Method 3: For SQL Server 2008 or 2008 R2

    If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008 or 2008 R2, and then click SQL Server Management Studio.

    Method 4: For SQL Server 2012

    If you are using SQL Server 2012, start SQL Management Studio. to do this, click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio.
  2. Verify that the DYNSA user is the owner of the correct databases. To have us verify that the DYNSA user is the owner of the correct databases for you, go to the "Fix it for me" section. To fix this problem yourself, go to the "Let me fix it myself" section.

    Fix it for me

    To verify that the DYNSA user is the owner of the correct databases automatically, click the Fix this problem button or link. Click Run in the File Download dialog box, and then follow the steps in the Fix it wizard.

    Note this wizard may be in English only; however, the automatic fix also works for other language versions of Windows.

    Note if you are not on the computer that has the problem, save the Fix it solution to a flash drive or a CD and then run it on the computer that has the problem.

    Let me fix it myself

    To verify that the DYNSA user is the owner of the correct databases yourself, follow these steps:
    1. Run the following statement and make sure that DYNSA is listed as the owner for the DYNAMICS database and all Microsoft Dynamics GP companies.
      sp_helpdb
    2. If DYNSA is not listed as the owner of the Microsoft Dynamics GP databases, run the following statement against the databases that have to change the owner to DYNSA.
      sp_changedbowner 'DYNSA'
  3. To delete the company from Microsoft Dynamics GP or from Microsoft Great Plains, follow these steps:
    1. Run the following statement.
      SELECT * FROM MASTER..SYSDATABASES
    2. If the database is listed in the results, delete the database by running the following statement. 
      DROP DATABASE XXX
      Note Replace XXX with the database name.
  4. To delete the company reference from the tables, download and run the statement that is available at the following link:
    https://mbs2.microsoft.com/fileexchange/?fileID=d57c52af-2432-486d-9072-e1a1d60563bf
  5. Restart Microsoft Dynamics GP Utilities, and then try to create the company again.

↑ Back to the top

More Information

For more information about how to create a Dexsql.log file for Microsoft Dynamics GP, click the following article number to view the article in the Microsoft Knowledge Base:

850996 How to create a Dexsql.log file for Microsoft Dynamics GP

↑ Back to the top

Keywords: kberrmsg, kbtshoot, kbprb, kbexpertiseinter, kbmbspartner, kbmbsmigrate, kbfixme, kbmsifixme, kb

↑ Back to the top

Article Info
Article ID : 871699
Revision : 1
Created on : 1/7/2017
Published on : 10/26/2013
Exists online : False
Views : 274