Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx|||

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?