Transactional repl to larger table -- sql server replication

Steve H
4/13/2006 9:24:02 AM

Greetings!

I am planning simple trans replication on SQL 2000 of a few tables. The
publisher and distributor are on same server and we are pushing to remote
(but still on internal network) subscriber.

There will be no partitioning of the replicated tables, the subscriber gets
the whole she-bang. However, to tell an external process that there are
updates, the tables on the subscriber side have an extra column that I will
use the default of 1 if they are changed. This breaks any attempts to
perform the snapshot because of an unexpected end of file since there are
more columns on the destination than the source. The only way I have worked
around this is by using DTS package to transform the data, which seems like
overkill.

Should it be easier than this? Am I missing something in replication that
could clean this up?

Thanks

Steve H
4/13/2006 10:29:02 AM

Thanks for the reply, Paul.
The intent is that when there are changes via replication applied to the
subscriber, the developers looking at the subscriber database want to see if
changes had been made to the column. Then their process only needs to look
at the updated flag in the subscriber tables. I've never used the updateable
subscribers thinking that those would send changes back to the publisher
which is definitely not what I wanted, but maybe I am misunderstanding the
functionality of updateable subscribers option. The columns that are being
replicated from source to destination will not be modified on the
destination. Only the "extra" updated flag column would be modified on the
subscriber (destination).

[quoted text, click to view]

Paul Ibison
4/13/2006 5:55:24 PM

Steve,
you could use an indexed view or use the @creation_script parameter of
sp_addarticle. However I'm confused that you're updating the subscriber and
yet you don't have an updatable subscription. Also, surely the default would
only apply to the inserts via replication and therefore always be set to 1?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul Ibison
4/13/2006 9:12:41 PM

Steve,
thanks for the explanation, but I still don't entirely follow: "the
developers looking at the subscriber database want to see if
changes had been made to the column" - this part seems odd to me. To flag
this type of change to the subscribers, who only see a RO database, the
change must have occurred on the publisher, so I'd use an update trigger to
set the flag on the publisher and have the value replicate down to the
subscriber. Is it perhaps the case that you can't change the schema on the
publisher to add this updated flag? If this is so, then you could indeed
have the schema change on the subscriber and modify the update stored
procedure used by replication to additionally set the update flag.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Transactional repl to larger table