Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Add Article to Transactional Replication

    I am fed up of having to use the GUI to add articles to my transactional replication so thought I would make something to aide my laziness.

    Here's what my script boils down to when adding a new view (admin.replication_test):
    Code:
    EXEC sp_addarticle 
       @publication   = N'<publication name>'
     , @article       = N'replication_test'
     , @source_owner  = N'admin'
     , @source_object = N'replication_test'
     , @type          = N'view schema only'
    ;
    If I check the GUI, the article appears to be ticked in the publication.

    Generate a new snapshot.

    View not found in replicated database!

    What's my numpty mistake?
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I take it you also applied the snapshot (ran the distribution job)?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Re-init Subscription > Use a New Snapshot > Generate the Snapshot Now
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have the tables in the subscriber database been dropped and re-created (have new create_Date timestamps)? If not, the re-init may either not be working, or not work as expected (the re-init may have only refreshed the data, and not the DDL, in other words). Since it sounds like a small-ish publication, maybe you can set up a new subscriber to see if the view appears in a fresh subscriber. If it does not, then there is something missing from the script, if it does, something else is wrong.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    All objects look to have been re-created (quick scan of sys.objects).

    If I add the article manually using the GUI and re-init, I get the desired result, so I can only assume my code is missing something?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Profiler to the rescue, then. See what Management Studio is being so helpful with, and you will have your answer.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can't believe I didn't think to use Profiler

    <facepalm/>


    sp_refreshsubscriptions
    Last edited by gvee; 10-15-14 at 14:05.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you really should have used Extended Events, but I did not catch the version of SQL Server you were using. Profiler and the trace procedures and functions have been deprecated, I think. They'll work for a few releases, but there won't be any new enhancements to them.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    True, but starting in SQL 2012 Extended Events get GUI support and they can dance rings around SQL Profiler!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    2008 R2 in this instance.
    Added Extended Events to the reading list, quite near the top.
    I am going to miss Profiler like I *still* miss Query Analyser!
    George
    Home | Blog

Tags for this Thread

Posting Permissions

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