Can SQL Server Replication include the source dbid in the replicated data?

There might be an easier way to do it, but the first thing I thought of is wrapping TableA in an indexed view on the source database and then replicating the view as a table (i.e. , type = "indexed view logbased"). I don't think this would work with merge replication, though.

There might be an easier way to do it, but the first thing I thought of is wrapping TableA in an indexed view on the source database and then replicating the view as a table (i.e. , type = "indexed view logbased"). I don't think this would work with merge replication, though.So, that would roughly be like: CREATE VIEW TableA_with_dbid WITH SCHEMABINDING AS SELECT DatabaseA.

Dbid, Id, Name FROM TableA CREATE UNIQUE CLUSTERED INDEX ON TableA_with_dbid (Id) -- or whatever your PK is EXEC sp_addarticle ..., @source_object = 'TableA_with_dbid', @destination_table = 'TableA', @type = 'indexed view logbased', ... Big caveat: indexed views have a lot of requirements that may not be appropriate for your application. For example, certain options have to be set any time you update the base table.(In response to the edit in your question...) This won't work for combining multiple sources into one table. AFAIK, an object in a subscribing database can only come from one published article.

And you can't do an indexed view on the subscribing side since UNION is not allowed in an indexed view.(The docs don't explicitly state UNION ALL is disallowed, but it wouldn't surprise me. You might try it just in case.) But it still does answer your explicit question: the dbid would be in the replicated table.

Thanks Tadmas, using Views had crossed my mind too. Also, as I commented on rwmnau's answer - I updated my question to include a little more detail. I need to combine multiple databases into one, and (obviously) each record in the combined table needs to be unique.

– MrDustpan Jan 7 '09 at 19:54 I've updated my answer to address this. I don't think my technique will work for this, sorry. – Tadmas Jan 7 '09 at 19:59.

Replicating only comes from one source - it's one-to-one, so the source ID doesn't seem like it would make much sense. If you're aggregating data from multiple sources, maybe linked servers and triggers is a better choice, and if that's the case, then you could absolutely include any information about the source that you want. If you can clarify your question to describe the purpose, it would help us find the best solution.

UPDATED FROM NEW DETAIL IN QUESTION: Does this solution sound like it might be what you need? Set up AFTER triggers on the source databases that send any changed rows to the central repository database, in some kind of holding table. These rows can include additional columns, like "Source", "Change type" (for insert, delete, etc).

Some central process watches the table and processes new rows (or runs periodically - once/minute, maybe), incorporating them into the central database You could adjust how frequently the check/merge process runs on the server based on your needs (even running it constantly to handle new rows as they appear, perhaps even with an AFTER trigger on that table as well).

Thanks rwmnau, you're exactly right - I need to aggregate multiple sources. I've updated my original post with a little more detail. – MrDustpan Jan 7 '09 at 19:45.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.