none
SQL Server 2014 Snapshot agent duplicating constraints RRS feed

  • Question

  • Hello,

    We are upgrading our server from SQL Server 2008 R2 Std. Ed.  to SQL Server 2014 Std. Ed.

    Our server works with Merge and Transactional publications.

    Our current problem happens when trying to generate the first snapshot using the snapshot agent. Since this is the first time, the snapshot will create all the system tables related with replication and the error happens when creating the table MSmerge_conflict_<publication>_<article>  because now the snapshot agent is trying to create the constraints from the <article> (a table in our case)  with the same name, so it brings the conflict of having duplicated constraints names and because of that the snapshot agent fails.  This didn't happen on 2008 because the snapshot agent didn't try to create the constraints before.

    This is the error that we see on the Snapshot Agent Status: There is already an object named"NewAccountPermission" in the database. Could not create constraint or index. See previous errors.

    The exact T-sql statement that is failing is shown on the replication monitor:
    Error messages:
    Message: There is already an object named 'NewAccountPermission' in the database.
    Could not create constraint or index. See previous errors. 
    Command Text: CREATE TABLE [dbo].[MSmerge_conflict_Roster_AccountPermissions](
    [AccountPermissionID] [int] NOT NULL CONSTRAINT [NewAccountPermission]  DEFAULT ([dbo].[getNewAccountPermissionID]()),
    [PermissionName] [varchar](255) NULL,
    [Description] [varchar](255) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL


    Digging a bit further I searched and compared the snapshot files that creates this table. Here it can be seen that the snapshot files only differ on the creation or not of the constraint.

    Here is an extract o f the 2014 file called AccountPermissions_3.cft

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[MSmerge_conflict_Roster_AccountPermissions](
    [AccountPermissionID] [int] NOT NULL CONSTRAINT [NewAccountPermission]  DEFAULT ([dbo].[getNewAccountPermissionID]()),
    [PermissionName] [varchar](255) NULL,
    [Description] [varchar](255) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL
    )

    Here is the 2008 version of the same file:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[MSmerge_conflict_Roster_AccountPermissions](
    [AccountPermissionID] [int] NOT NULL,
    [PermissionName] [varchar](255) NULL,
    [Description] [varchar](255) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL
    )


    Any ideas on what is going on? Do I have to configure some properties on the publication now to disallow creating the constraints on the msmerge_conflict table?

    Thank you,



    Alejandro Rivera

    Monday, November 17, 2014 8:29 AM

Answers

  • Hi Tom,

    I installed the cumulative update 4 for SQL Server 2014, but it didn't work. We are starting to believe that this is actually a bug from 2014 and the way that it scripts the default constraints on tables. Because the default constraint is now in the "CREATE TABLE" statement instead of using an "ALTER TABLE", the snapshot agent tries to create a duplicate constraint on the merge conflict system table since it uses the same schema as the "CREATE TABLE" statement.

    My solution for now is to drop all the default constraints on the tables, run the snapshot agent and then add all the constraints again. This is not a desirable solution of course, but is all I have so far.



    Tuesday, November 25, 2014 7:05 PM

All replies

  • I am confused by a few things. 1) is this a user table or a system table? 2) is there a schema involved? It looks like the schema is called Roster, but it is hard to say.

    Can you post the schema of the problem base table here, ie Roster.AccountPermissions or Roster_AccountPermissions?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, November 17, 2014 2:40 PM
    Moderator
  • Hi Hilary,

    1) There is an user table called dbo.AccountPermissions. The problem happens when the snapshot agent tries to create MSmerge_conflicts_Roster_AccountPermissions (system table). The database is called Roster.

    2) We use the default dbo schema. Here is the create script for that table (just in case):

    USE [Roster]
    GO

    /****** Object:  Table [dbo].[AccountPermissions]    Script Date: 11/17/2014 7:48:27 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[AccountPermissions](
    [AccountPermissionID] [int] NOT NULL CONSTRAINT [NewAccountPermission]  DEFAULT ([dbo].[getNewAccountPermissionID]()),
    [PermissionName] [varchar](255) NULL,
    [Description] [varchar](255) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [MSmerge_df_rowguid_13B9434FF11A44ACB63F5D2927C053FD]  DEFAULT (newsequentialid()),
     CONSTRAINT [pk_accountpermissions] PRIMARY KEY CLUSTERED 
    (
    [AccountPermissionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    I'd put a screenshot too, but sadly the forum doesn't allow me until it verifies my account.

    Let me know if you any more info.

    Thank you,

    Monday, November 17, 2014 5:01 PM
  • Can I see what  getNewAccountPermissionID looks like?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, November 17, 2014 8:52 PM
    Moderator
  • Please post the results of SELECT @@VERSION on both servers.

    Monday, November 17, 2014 9:10 PM
  • Hi Hilary,

    Sorry we work on a NGO in rural Kenya so there is a bit of a time difference.

    Here it is:

    USE [Roster]
    GO

    /****** Object:  UserDefinedFunction [dbo].[getNewAccountPermissionID]    Script Date: 11/18/2014 9:27:11 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[getNewAccountPermissionID]
    (

    )
    RETURNS int
    AS
    BEGIN
       -- Declare the return variable here
       DECLARE @Result int

       -- Add the T-SQL statements to compute the return value here
       SELECT @Result = isNull(MAX(AccountPermissionID),0)+ 1 FROM dbo.AccountPermissions

       -- Return the result of the function
       RETURN @Result

    END

    GO

    Tuesday, November 18, 2014 6:57 AM
  • Hi Tom,

    Here it is:

    SQL Server 2008: 

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

    SQL Server 2012: 

    Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Thanks!

    Tuesday, November 18, 2014 6:59 AM
  • Hi Hilary or Tom, 

    Any clues on what could be the problem?

    Thanks!

    Wednesday, November 19, 2014 6:18 PM
  • You are running the RTM version of both versions.   Since this appears to be a problem with the MS tables, I would suggest installing the latest Service Pack for SQL 2008 R2 and Cumulative update for 2014 and retest.


    https://support.microsoft.com/kb/321185?wa=wsignin1.0
    • Edited by Tom Phillips Wednesday, November 19, 2014 6:50 PM
    Wednesday, November 19, 2014 6:47 PM
  • Hi Tom,

    I installed the cumulative update 4 for SQL Server 2014, but it didn't work. We are starting to believe that this is actually a bug from 2014 and the way that it scripts the default constraints on tables. Because the default constraint is now in the "CREATE TABLE" statement instead of using an "ALTER TABLE", the snapshot agent tries to create a duplicate constraint on the merge conflict system table since it uses the same schema as the "CREATE TABLE" statement.

    My solution for now is to drop all the default constraints on the tables, run the snapshot agent and then add all the constraints again. This is not a desirable solution of course, but is all I have so far.



    Tuesday, November 25, 2014 7:05 PM