Re: ERROR 515: while creating a merge replication publication

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 14:55:00 -0400

I am afraid I don't. I did a search on your problem and found some matches indicating it might be a bug.

Can you call PSS on this one?
  "Chris McKenzie" <taganov@charter.net> wrote in message news:%23dZ6$EmoEHA.3668@TK2MSFTNGP15.phx.gbl...
  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

  • ANNOUNCE: Rose::DB::Object 0.601 released
    ... There have been many changes and bug fixes since the last announcement to ... * Added pre_init_hookmethod to metadata objects and the loader. ... * Added support for bigserial columns. ... * Improved auto-detection of primary key sequence names. ...
    (perl.dbi.users)
  • DBD::Informix test failure for lvarchar - bug found!
    ... LVARCHAR NOT NULL, and only if the table is not a temporary table. ... This bug is now idsdb00139040 in the IBM/Informix CQ database. ... SQL DESCRIPTOR does not handle LVARCHAR NOT NULL properly. ... row_number serial not null primary key, ...
    (perl.dbi.users)
  • Re: for Lynn Trapp RE: two records for one relationship
    ... BTW: your previous concern about a finished good being associated with another finished good has been addresssed. ... "Lynn Trapp" wrote: ... > primary key and the foreign key MUST be the same. ... The code shows a bug when selecting a txtProfileID ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Any way to make PG driver obey PrintWarn?
    ... Hash: SHA1 ... CREATE TABLE / PRIMARY KEY will create implicit index ... This is a bug in DBD::Pg. ...
    (perl.dbi.users)
  • Re: Report on Total Population changes
    ... czn_fk is the replicated ID field (Primary Key) which is a text field. ... >> Client ID is a replicated number which is also Primary ... >> I get an error as undefined function when it is typed as is in a report. ... >>> If you are using a primary key that consists of a Replication ID field, ...
    (microsoft.public.access.reports)