July 18, 2005 at 12:31 pm
Not sure if anyone has come across a situation where data needs to be replicated to a subscriber when its changed on publisher but on a table which doesnt have a primary key. Is this possible at all? any suggestions will be greatly appreciated.
TIA
July 18, 2005 at 12:43 pm
I'm pretty sure it's not possible, using Transactional replication. How can it perform an update if it can't use a key to find the record to be updated ?? How does it get changed on the publisher if there's no PK ??
However, you can use snapshot replication which will replace the entire table at the subscriber with a new copy of the table. I use this once a day for tables that don't change very much, such a product codes, state codes etc. If it's a small table, you could use snapshot replication more frequently.
July 18, 2005 at 12:46 pm
Yeah thats what i thought so . you are right snapshot works fine in such a situation. is it possible to have a snapshot replication for one article and a transaction one for another article on same publication? please let me know.
Thanks
July 18, 2005 at 12:56 pm
No, you need to set up you snapshot articles in a snapshot publication, and your transaction articles in a transaction publication.
BTW, When you create transaction publication, and get to the screen where you choose the tables (articles), you'll notice that tables without PKs are not selectable
July 19, 2005 at 6:13 am
Is it possible to add a non meaningfull PK that is created using a default? Just add a column of type uniqueidentifier and set it's default to newid(). Make this your PK and it will replicate.
July 20, 2005 at 9:42 am
It is also possible to use merge replication, which does not require primary keys.
Dylan Peters
SQL Server DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply