March 20, 2003 at 9:04 pm
As I don't need updated information and I don't need to do any insert, update, delete action on the subscriber, I decided to use the snapshot publication
However, when I use the snapshot publication, I found that the subscriber loses the primary keys in all tables. How can I retain this key?
Thanks a lot.
March 21, 2003 at 5:42 am
Do you have those primary keys on the publisher? You could also use transactional publications for the needs you have. If you have a big database, maybe you should check that replication type too.
March 21, 2003 at 7:05 pm
yes, those primary keys exist on the publisher and I tried the transaction publication and snapshot publication but both resulted in losing the primary key on the subscriber.
March 21, 2003 at 7:53 pm
In your publication find the articles tab, you can set some snapshot options there. Don't know that my usage is typical, but I always run a post snapshot script to set indexes on the subscriber - often need a different set than I do on the publisher.
Andy
March 21, 2003 at 9:12 pm
thanks for all kindly reply. 🙂
April 2, 2003 at 10:20 pm
Don't know if anyone still cares about this, but here's what I just posted in another thread on this topic...
----- Thread extract -----
There's a stored procedure called sp_MSdroparticleconstraints which lives in the master database on your subscribers and this is the thing that removes your Primary Keys (amongst other things).
I have heard tell from others that you can comment out this functionality from the SP to retain your PKs &c. I tried it and it worked, but it strikes me as not an elegant solution and I'm not sure what the side effects would be so I ended up changing to merge replication instead which doesn't have this issue.
HTH
April 3, 2003 at 12:32 pm
If you perform the replication by use of the system procs and scripting, as I do, there is an option called schema_option which determines how the end tables are created. Here are the available options you can use, along with the one I commonly use, which creates your tables correctly.
@schema_option = 0x8000
--Is a bitmask of the schema generation option for the given article. It specifies the automatic creation of the stored procedure in the destination database for all CALL/MCALL/XCALL. schema_option is binary(8), and can be a combination of these values. If this value is NULL, the system will auto-generate a valid schema option for the article. The table given in the Remarks shows the value that will be chosen based upon the combination of the article type and the replication type.
--0x00 Disables scripting by the Snapshot Agent and uses creation_script.
--0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
--0x02 Generates custom stored procedures for the article, if defined.
--0x10 Generates a corresponding clustered index.
--0x20 Converts user-defined data types to base data types.
--0x40 Generates corresponding nonclustered index(es).
--0x80 Includes declared referential integrity on the primary keys.
--0x100 Replicates user triggers on a table article, if defined.
--0x200 Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
--0x400 Replicates check constraints.
--0x800 Replicates defaults.
--0x1000 Replicates column-level collation.
--0x2000 Replicates extended properties associated with the published article source object.
--0x4000 Replicates unique keys if defined on a table article.
--0x8000 Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements.
--NULL
April 8, 2003 at 8:13 am
There is also a way in EM to make sure primary keys are replicated. When you specify articles to be published, you can go to the properties of each article and specify that primary key should be replicated during initial snapshot. If you have already created the snapshot, you can edit the properties to make this change and then reinitialize the snapshot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply