December 13, 2001 at 3:04 pm
We are receiving an error with replication because of our identity columns. We have been told about the "Not For Replication" option on the identity property but don't know where this option can be found inside the Enterpise Manager. Can this option only be used if you create the table by script? That does not seem right. Where can I turn this option on inside of the Enterprise Manager?
Please help
December 13, 2001 at 3:16 pm
In the EM Design View the Identity Col drop down select has "Yes (Not for replication)".
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 13, 2001 at 3:49 pm
David, Thanks for your help but I don't know where that is. I forgot to mention that I am running SQL 7.0. Please help
December 14, 2001 at 3:37 pm
Um, Er, Well, after digging around a tiny bit during my 2 minute lunch, I did not find that you could do this within EM in 7.0. May be best to script it out with T-SQL. Some good references exist in BOL. Sorry.
Anyone else know how to do this within EM in 7.0?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2001 at 7:16 pm
"Not For Replication" option on the identity property is in table design: columns: Identity property. Trouble is we have tried this on SQL2000 SP1 but replication to a table with a field set up with this property still fails with 'cannot update identitity column'. If it works for you we'de love to know how you made it work!
December 23, 2001 at 2:13 pm
How to maintain identity property of a column on both publishing and
subscribing databases in transactional replication. Here is one way to do it on
SQL2000. In this instance we have just one ID column that is a primary key with
unique values.
1. The ID column can be defined as 'Yes (Not For Replication)' or just 'Yes'. Do
this on the publisher. This creates a standard ID property on the subscriber.
2. Set up replication for the table with default options i.e. allow default
stored procedures to be used and allow these to be generated at the subscriber
during initialization and also allow the table to be created automatically at
the subscriber. Ignore (but remember) the warning message about not replicating
identity fields.
3. Create the subscription allowing initialization of schema and data and
immediate start of snapshot agent. This will create the table and replication
stored procedures on the subscriber and start the snapshot and distribution
agents. We also allow triggers and other properties to be propagated.
4. Stop the log reader agent associated with the replication.
5. Go to the subscriber and in design table set the ID field to 'Yes (Not For
Replication)'. This will allow insert to the ID field on the subscriber. The ID
field was dropped in accordance with the warning message given in step 2. We are
manually reinstating it.
6. In the stored procedures for the subscriber database find the update stored
procedure for the table usually 'sp_MSupd_TableName'. Open its properties and
delete the line
"ID" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "ID" end,
not forgetting to delete the comma at the start of the next line. In this case
the ID field is called 'ID'. The correct line is the one immediately after the
SET command of the first UPDATE statement.
7. Start the log reader agent associated with the replication.
Now you can insert and update records in the publisher table and the changes will
replicate successfully to the subscriber.
Note it was not necessary to use the SET IDENTITY_INSERT ON/OFF, nor write your
own stored procedures.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply