How to add a constant column when replicating a database?

Question

I am using SQL Server 2000 and I have two databases that both replicate (transactional push subscription) to a single database. I need to know which database the records came from.

So I want to add a fixed column specified in the publication to my table so I can tell which database the row originated from.

How do I go about doing this?

I would like to avoid altering the main databases mostly due to the fact there are many tables I would need to do this to. I was hoping for some built in feature of replication that would do this for me some where. Other than that I would go with the view idea.

Answer 1

You can create a view, which adds the "constant" column, and use it as a replication source.

Answer 2

You could use a calculated column Use the following on the two databases:

ALTER TABLE TableName ADD
    MyColumn AS 'Server1'

Then just define the single "master" database to use a VARCHAR column (or whatever you want) that you fill using the calculated columns value.

Answer 3

So the solution for me was to set up the replication publications to allow transformations and create a DTS package for each site that appends the siteid into the tables to keep the ids unique as I can't use guids.