Add a field to target table?

I would like to add a field to a table that is the target table in a
transactional replication. The source table does not have this field (and
never will). Can it be done without interfering with the existing replication?
Thanks in advance,
/Anders
nope.
You will probably have to either create a custom sync object, replicate an
indexed view which looks like your table on the subscriber, or do a nosync
and edit the stored procedures for the different schema on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"AndersBj" <AndersBj@.discussions.microsoft.com> wrote in message
news:6DA74EBF-3994-42E8-BBE4-B4261C1FA334@.microsoft.com...
> I would like to add a field to a table that is the target table in a
> transactional replication. The source table does not have this field (and
> never will). Can it be done without interfering with the existing
replication?
> Thanks in advance,
> /Anders
|||Thanks for your answer. After I posted this question I did a small test by
setting up a transactional replication between two databases on the same SQL
server.
The publisher in db A had the following fields in table X:
ID, name, email
The subscriber in db B had the following fields in table X:
ID, name, email
I then added a field to the subscriber table X, to make it look like this:
ID, name, email, age
This worked without any problems, new items replicated from db A to db B
just left age being null. Can this little test of mine prove that the concept
works or is there something else I should consider?
Thank you,
/Anders
"AndersBj" wrote:

> I would like to add a field to a table that is the target table in a
> transactional replication. The source table does not have this field (and
> never will). Can it be done without interfering with the existing replication?
> Thanks in advance,
> /Anders
|||Hmm, did some further testing and realized that if a new snapshot ever should
be required, my changes on the table in db B would be overwritten... so I
guess this isn't a good solution after all...
"AndersBj" wrote:
[vbcol=seagreen]
> Thanks for your answer. After I posted this question I did a small test by
> setting up a transactional replication between two databases on the same SQL
> server.
> The publisher in db A had the following fields in table X:
> ID, name, email
> The subscriber in db B had the following fields in table X:
> ID, name, email
> I then added a field to the subscriber table X, to make it look like this:
> ID, name, email, age
> This worked without any problems, new items replicated from db A to db B
> just left age being null. Can this little test of mine prove that the concept
> works or is there something else I should consider?
> Thank you,
> /Anders
>
> "AndersBj" wrote:
|||Anders,
on the subscriber you could have the extra column, ensuring it is recreated
is easy is done using @.creation_script argument of sp_addarticle. How to
ensure the data isn't lost? Use an instead-of trigger on the subscriber to
input the changes to the extra column into an audit table to ensure they're
not lost.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you, I will try this as soon as possible.
/Anders
"Paul Ibison" wrote:

> Anders,
> on the subscriber you could have the extra column, ensuring it is recreated
> is easy is done using @.creation_script argument of sp_addarticle. How to
> ensure the data isn't lost? Use an instead-of trigger on the subscriber to
> input the changes to the extra column into an audit table to ensure they're
> not lost.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>