Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: merge repl/new bcp files after 2005 upgrade

    I'll start off by saying these questions were submitted to Microsoft and no
    one can give us an answer without them doing a source code review. Their
    response was "these are internal BCP scripts that are used by SQL"....yes, I
    know that....that doesn't help to explain why the system created them. At
    this point, I don't want my compary to pay support hours for Microsoft
    personnel to learn what their software does.
    --------------------
    We've had merge publication in place for several years.

    Last year, we added some tables/articles to publications.

    After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2
    Standard, when the snapshot was run, the system created some new files that
    had never existed before. Also, and more importantly, these files were only
    created for the articles added after the initial snapshot was created.
    Articles existing in the intial snapshot did not have these type of files
    created.

    Examples of these files for a SA_MOVE table that was added to an existing
    publication:

    MSmerge_contents_SA_MOVE.bcp
    MSmerge_contents_SA_MOVE_forall.bcp
    MSmerge_rowtrack_SA_MOVE90.bcp
    sysmergesubsetfilters_SA_MOVE.bcp
    sysmergesubsetfilters_SA_MOVE90.bcp


    Also some new bcps created were:

    MSmerge_contents90_forall.bcp
    MSmerge_genhistory90.bcp
    MSmerge_rowtrack90.bcp
    sysmergesubsetfilters90.bcp


    The system wanted to apply these files before it would start synchronizing
    data so they had to be delivered to the subscriber. It appears it was the
    sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in
    size and we do not have filter on(at least not as far as I know...see the
    addmergearticle code below).

    New databases created this year that had all of the articles defined in the
    publication before the initial snapshot was taken did not get these
    additional files created, nor did the system want to try to send files to the
    subscriber before syncrhonization could begin.

    This is an example of how an article had been cretead under SQL Server 2000
    when adding a new database, before an initial snapshot is run:

    exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
    @source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
    @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
    @creation_script = null, @schema_option = 0x000000003000EFF1,
    @article_resolver = null, @subset_filterclause = null, @vertical_partition =
    N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0,
    @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
    @check_permissions = 0

    This is an example of how an article was added under SQL Server 2000 to an
    existing publication that already had the initial snapshot run:

    exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
    @source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
    @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
    @creation_script = null, @schema_option = 0x000000000000EFF1,
    @article_resolver = null, @subset_filterclause = null, @vertical_partition =
    N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
    @verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
    @fast_multicol_updateproc = N'true', @check_permissions = 0,
    @force_invalidate_snapshot = 1


    A few questions:

    Does anyone have an idea why the system created these files are part of the
    2005 upgrade?

    What is the real purpose of the files with names such as _forall.bcp ?

    Thanks for any help/info on this,

    Doug

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I assume that the second sp_addmergearticle you intented to mark as SQL Server 2005, right?

    Is your snapshot failing on either one of the files you mentioned, or you're just curious what they are? If it's the first, then I don't see an error you posted (did I miss it?). If the latter, then I would create horizontal filtering for one of the articles, like "where 1=1", and see if sysmergesubsetfilters90.bcp gets some data in it, along with sysmergesubsetfilters_SA_MOVE90.bcp (without the 90 is for backward compatibility, but I am not sure if their code actually uses it).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2008
    Posts
    3
    I assume that the second sp_addmergearticle you intented to mark as SQL Server 2005, right?

    Not sure what you mean by this. The second sp_addmergarticle is the coding used to add the table to an existing publication under SQL Server 2000 last fall. When the upgrade to 2005 occurred a couple weeks ago and the snapshot was run for this publication, these new files were created just for the articles that had been added the initial snapshot. All articles were created while the system was still 2000 SP4 Standard.


    Is your snapshot failing on either one of the files you mentioned, or you're just curious what they are? If it's the first, then I don't see an error you posted (did I miss it?). If the latter, then I would create horizontal filtering for one of the articles, like "where 1=1", and see if sysmergesubsetfilters90.bcp gets some data in it, along with sysmergesubsetfilters_SA_MOVE90.bcp (without the 90 is for backward compatibility, but I am not sure if their code actually uses it).

    The snapshot is not failing. I'd like to know why these were created since the system wanted to apply the files before synchronization could continue. This has caused problems with some people who have had difficulty getting the snapshots from our FTP server becuase of poor connections, etc. We've had to create a special routine to ftp the files down and have the subscription look locally for the snapshot for these people where the replication process could not deliver the needed files from our FTP server during the replication process.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •