Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: sp_addarticle: what's the difference between @source_object and @sync_object?

    BOL says:

    @source_object - Is the table or stored procedure to be published.
    @sync_object - Is the name of the table or view used for producing the data file used to represent the snapshot for this article.

    I get how transactional replication is tied to changes in a single table or indexed view (the "source_object"). But what is the role of the sync_object? It appears as if the sync_object can be any simple (non-indexed) view. Hilary Cotter uses it to replicate a calculated field (the number of titles associated with each author) in "Case 4" in this article: http://www.dbazine.com/sql/sql-articles/cotter1.

    Does the replication pull data from the sync_object whenever it replicates data, even though BOL says that it is used only for the snapshot data? Am I lacking some fundamental understanding of what the snapshot data is? Isn't it a snapshot of all the data at a particular point in time? If the snapshot contains a calculated field, don't the subsequent replicated transactions pulled from the transaction logs also have to be able to calculate that field?

    Thanks,

    -=michael=-

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    This is a bit of a guess based on reading the complete definition from BOL:

    [@sync_object = ] 'sync_object'

    Is the name of the table or view used for producing the data file used to represent the snapshot for this article. sync_object is nvarchar(386), with a default of NULL. If NULL, sp_articleview is called to automatically create the view used to generate the output file. This occurs after adding any columns with sp_articlecolumn. If not NULL, a view is not created (assumes the view is manually created).
    I think that @synch_object is the name of the file that is generated which the subscriber uses to generate the object. It is a file that is published in the replication directory and can be viewed using Notepad or any other text viewer.

    I'm doing some more work this afternoon on a merge replication setup. I can try to post additional information later...

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    Quote Originally Posted by hmscott
    I think that @synch_object is the name of the file that is generated which the subscriber uses to generate the object.

    That doesn't appear to be the way Hiliary Cotter used it. Which is part of my confusion.

    -=michael=-

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by m.sean.kelly
    That doesn't appear to be the way Hiliary Cotter used it. Which is part of my confusion.

    -=michael=-
    Actually, in reviewing the link you provided, I think it's exactly the way that Cotter is using the @sync_object parameter. It refers to a file with the DDL of the target object of the published article (since the target object is not of the default structure). I hadn't seen this done before (merging two objects into a single object); it certainly looks pretty cool. I'd hate to be the DBA who walked into this environment cold though.

    If you're going to go this route, please be sure to document the heck out of it, have a solid development/test environment, lock the source code down and then spend some more time documenting it.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Nov 2005
    Posts
    7
    Quote Originally Posted by hmscott
    Actually, in reviewing the link you provided, I think it's exactly the way that Cotter is using the @sync_object parameter. It refers to a file with the DDL of the target object of the published article (since the target object is not of the default structure).
    Hmmm.... We may be saying the same thing in different ways. Cotter has this for the call to sp_addarticle:

    sp_addarticle @publication='authortitles', @article='authortitles', source_table='titleauthor', @destination_table='authortitles', @sync_object='AuthorTitles_Sync_Object', @schema_option=0x00,
    @creation_script='c:\test.sql', @ins_cmd='CALL sp_MSins_authortitles', @del_cmd='CALL sp_MSdel_authortitles', @upd_cmd='MCALL sp_MSupd_authortitles'

    @sync_object is a view defined thusly:

    Create View AuthorTitles_Sync_Object
    as
    select authors.au_id, au_lname, au_fname, phone, address, city, state, zip, contract, author_titles =count(title_id) From authors,titleauthor
    where authors.au_id=titleauthor.au_id
    group by authors.au_id, au_lname, au_fname, phone, address, city, state, zip, contract

    At one point in Cotter's book on SQL Server Replication he describes @sync_object as the object SQL Server BCPs from (presumably during both the snapshot creation and transactional replication), while in another place he appears to copy the BOL definition indicating that the @sync_object is only used only during the snapshot.

    For the moment, I'll just assume that the @sync_object is used for both snapshot creation and transactional replication. Honestly, it's the only thing that makes sense. I'll, of course, test before pushing to production.

    -=michael=-

Posting Permissions

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