November 12, 2007 at 6:24 am
I have updated a database to 2005. The database itself is part of an externally managed application so I'm reluctant to mess with any part of it. I used to be able to replicate out snapshots of several tables, now replication fails for some tables with identity columns. I don't want subscriber dbs to make alterations to the data at all, but there doesn't seem to be a way to do this.
Replication on these tables fails with the message "You cannot specify schema_option 0x4 (script identity as identity rather than the base data type) for article 'curr_class_period'. The value specified for the parameter @identityrangemanagementoption is NONE. To replicate identity as identity, the value must be MANUAL or AUTO for publications that do not support queued updating subscriptions.
Changed database context to 'sims'. (.Net SqlClient Data Provider)"
I've tried to set autoidentitymanagement to something other than none, but the option is greyed out in studio. When I tried to alter a script to set the option the query process rejected that attempt to add_article saying that this option was only of value in transactional replication.
What am I missing?
Thanks in advance for any and all help
November 13, 2007 at 12:47 pm
GUI is not most reliable method for managing repl - nor does it allow you to manipulate advanced features. Learn the sql scripts for administration
Use the following to add article w/ identity -
exec sp_addarticle @publication = N'PubNameHere', @article = N'TableNameHere', @source_owner = N'dbo'
, @source_object = N'TableNameHere', @type = N'logbased', @description = N'', @creation_script = N''
, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual'
, @destination_table = N'TableNameHere', @destination_owner = N'dbo', @status = 8
, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTableNameHere]'
, @del_cmd = N'CALL [sp_MSdel_dboTableNameHere]', @upd_cmd = N'SCALL [sp_MSupd_dboTableNameHere]'
Regards,
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 13, 2007 at 12:56 pm
Thanks very much for this Chris, I'll be studying it in detail as I have been gradually scripting more and more of what I do.
As a matter of fact I had found the answer to my problem today in the GUI interface. Rather than highlighting the table as a whole I needed to expand the table and highlight the identity field itself to tell it to use Manual. As I frequently have to drop replication to update the database with patches from the outside suppliers I've got the system to build a script from the successful replication - I'll use yours to improve it.
Thanks again.
Jay
November 13, 2007 at 1:01 pm
Yes - much easier to manage in script if you are often breaking repl down for maint. Especially when your db has hundreds of tables.
Chris
Chris Becker bcsdata.net
May 23, 2008 at 1:39 am
Excellent!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply