Re: ERROR 515: while creating a merge replication publication

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chris McKenzie (taganov_at_charter.net)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 14:06:41 -0400

HI Hilary,

I know what you mean by a composite primary key now. I don't use those normally, so I was a little thrown by terminology, lol. As far as manipulating the database goes, I have sole discretion over that.

I did as you suggested and manually changed my IDENTITY PRIMARY KEY columns to PRIMARY KEY IDENTITY NOT FOR REPLICATION.

Every table in the databas has one and only one PRIMARY KEY column now, and they are all created as IDENTITY NOT FOR REPLICATION. STill, when I try to create the new publication article, I get the same error.

Thanks for your help, and please let me know if you have any other ideas.

Chris
  "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:ePBmXploEHA.1308@TK2MSFTNGP14.phx.gbl...
  There is a bug if you have a composite primary key which may generate the error message you are seeing.

  The best way to check for this is to open up Enterprise Manager, connect to your publisher, expand your publication database and click on the tables folder. For each table you are replication right click on it and select design table. Look for icons to the left of columns which have a key on them. This is your primary key. If more than one column has the key icon on per table you have a composite primary key. You will have to talk to your developers or the vendor who created the database about changing the composite primary key.

  You might also want to open a support incident with Microsoft on this on how to proceed.

  Regarding the informational messages EM is throwing up.

  Cause insert statements without column lists to fail.

  If your application issues queries like this

  insert into tablename1
  select * from tablename2

  you may get an application failure unless the GUID column (used to track changes in merge replication) is added to both tables. You will have to consult your developers or the vendor to confirm this is not happening. Or you can replicated every table using merge replication.

  Regarding the changing size of the table - merge replication adds a GUID column of 16 bytes. This may cause very slight performance degradation on heavily utilized systems, and may make wide tables exceed the 8k maximum width of a table. Unless your tables are wide you should not have to worry about it.

  Regarding the guid column - you should not have to worry about this as it almost always is not problematic. It will cause the snapshot creation time to increase especially on very large tables.

  Regarding the identity column. As a good practice you should manually change your identity columns to not for replication. To make this change right click on your tables and select design table. Give focus to your identity columns and in the drop down box in the lower portion of the dialog change identity(YES) to Identity (NOT FOR REPLICATION).

  HTH

    "Chris McKenzie" <taganov@charter.net> wrote in message news:ObSMlFloEHA.3900@TK2MSFTNGP10.phx.gbl...
    Since I'm not sure "what that is/ why I need it", I guess I'd have to say no. When I attempt to create the publication, I get the following messages:

    SQL Server requires that all merge articles contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server will add a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.

    Adding a new column will:
         » Cause INSERT statements without column lists to fail
         » Increase the size of the table
         » Increase the time required to generate the first snapshot

    SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.

     [dbo].[tblActivity]
     [dbo].[tblAssetPayable]
     [dbo].[tblClass]
     [dbo].[tblControl]
     [dbo].[tblGLJournal]
     [dbo].[tblItemClasses]
     [dbo].[tblItemMaster]
     [dbo].[tblItemVendors]
     [dbo].[tblPicture]
     [dbo].[tblReportParameters]
     [dbo].[tblUsers]

    AND

    It is strongly recommendeds that all replicated IDENTITY columns use the NOT FOR REPLICATION option. When automatic identity range management is enabled for an article, SQL Server automatically adds the NOT FOR REPLICATION option to the IDENTITY column.

    The following published tables, for which automatic identity range management has not been enabled, contain IDENTITY columns without the NOT FOR REPLICATION option:

     [dbo].[tblActivity]
     [dbo].[tblAssetPayable]
     [dbo].[tblClass]
     [dbo].[tblGLJournal]
     [dbo].[tblItemClasses]
     [dbo].[tblItemMaster]
     [dbo].[tblItemVendors]
     [dbo].[tblPicture]

    SQL Server automatically adds what I need, right?

    Thanks,
    Chris McKenzie
      "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:OGF4$2koEHA.1088@TK2MSFTNGP09.phx.gbl...
      do you have a composite primary key?

      --
      Hilary Cotter
      Looking for a SQL Server replication book?
      http://www.nwsu.com/0974973602.html

        "Chris McKenzie" <taganov@charter.net> wrote in message news:e6fdwpkoEHA.1776@TK2MSFTNGP14.phx.gbl...
        I've been trying to work through the example listed at http://www.databasejournal.com/features/mssql/article.php/1438231 but I keep getting the following error when I try to create the pubs_article publication:

        SQL Server Enterprise Manager could not create publication 'pubs_article' from database 'pubs.'

        Error 515: Cannot insert the value NULL into column 'step_name', table 'msdb.dbo.sysjobsteps'. column does not allow nulls. INSERT failed.

        Any ideas?

        Chris

Relevant Pages

  • Re: PK Column Required
    ... then CLOSE OUT this post so you don't multi post. ... The table involved has a composite primary key composed of ... "This table cannot be published because it does not have a primary key ... Primary key columns are required for all tables in transactional ...
    (microsoft.public.sqlserver.programming)
  • Re: Primary Key in Composite Index
    ... searching on the primary key columns in the WHERE clause and referencing ... mypk is the primary key ... blah is a unique index. ...
    (microsoft.public.sqlserver.programming)
  • PK Column Required
    ... SP2 server. ... The table involved has a composite primary key composed of three ... "This table cannot be published because it does not have a primary key ... Primary key columns are required for all tables in transactional ...
    (microsoft.public.sqlserver.programming)
  • Re: reading random records with JDBC?
    ... where clause with all the primary key columns. ... And while comparing, ... the primary key columns should not be compared. ...
    (comp.databases.oracle.misc)
  • Re: Oracle 9i Views Primary Key constraint
    ... >In Oracle 9i we can create primary key constraint for views. ... >I am writing a PL/SQL script to retrieve the primary key columns from ...
    (comp.databases.oracle.misc)