from sp_addarticle (Transact-SQL)

I get an example as how to add an article to a publication.

this adds the table dbo.Product to the publication AdvWorksProductTran

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran'; 
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL'; 
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';

-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema 
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle 
    @publication = @publication, 
    @article = @table, 
    @source_object = @table,
    @source_owner = @schemaowner, 
    @schema_option = 0x80030F3,
    @vertical_partition = N'true', 
    @type = N'logbased',
    @filter_clause = @filterclause;

How would I do if I had also the following tables from different schemas to add to this publication?

I want to add these two tables to the publication, how do I do it using sp_addarticle?

  1. my_schema01.Product
  2. my_schema02.Product

The meta-answer to this is to use SSMS to publish the article and examine the replication scripts. The answer is that the @source_owner parameter to sp_addarticle has the target schema. This dates from before schema/owner separation in SQL 2005, where an object owner and a schema were the same thing.

And the script you list uses does not publish dbo.Product, it publishes Production.Product


It is my experience that the 'Article' name is required to be a unique value, regardless if you have a duplicate object names but different owners (schema). The article name does not have to be the name of the object. When profiling the addition of articles in the UI with this scenario, you will notice it appends a numerical value to the end of the object name, making the article name 'table1', 'table2' etc.

My solution was to append the schema name to the object name ('table_dbo', 'table_dba', etc.) as the article name. You will clearly have to pass in the source_object and source_owner as the correct values, same with destination values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.