Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
abarringer
Starting Member
13 Posts |
 Posted - 2007-02-07 : 18:37:59
|
| I need to import some data into a table that is in a merge bi-directional publication. The table has an identity column that is being "auto managed". The ID numbers I need to import are not within any of the assigned "ranges". They are less than any of the current ranges. Turning ident_insert on does not work because of the check constraints error. The merge replication triggers validate the ID range as well.Edit: using SQL 2005 standard publisher and subsciber. Publication is 2005. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-07 : 22:36:27
|
| I'm not sure it'll work for merge replication, but the solution to input rows with the identity values is to use SET IDENTITY_INSERT option.Tara Kizer |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-08 : 00:01:10
|
| the only way that I am aware of to insert values that aren't auto-generated into a identity field is to use the IDENTITY_INSERT option that tara pointed out.If this is merge replicated, can you insert these rows into another subscriber whose range does match?-ec |
|
|
|
abarringer
Starting Member
13 Posts |
Posted - 2007-02-08 : 09:01:18
|
| The problem is that none of the subscribers ranges are as low as this ID number. All subscribers have had at least one identity range increment, leaving the very low id numbers with no valid ranges. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-08 : 11:45:10
|
| adjust the managed range on one of your subscribers then. You can do that with sp_adjustpublisheridentityrange. Here is a KB article that might help you out http://support.microsoft.com/kb/322910Of course, it goes without saying that you should do this in a test environment first-ec |
|
|
|
abarringer
Starting Member
13 Posts |
Posted - 2007-02-08 : 13:48:52
|
quote:
adjust the managed range on one of your subscribers then
There isn't a way to manually assign an identity range for a published article when it is set to "Automatically manage identity range" Or if there is I haven't been able to find it.Reinitializing replication is not an option. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-08 : 14:43:43
|
quote:
Originally posted by abarringer
quote:
adjust the managed range on one of your subscribers then
There isn't a way to manually assign an identity range for a published article when it is set to "Automatically manage identity range" Or if there is I haven't been able to find it.Reinitializing replication is not an option.
ok, I see. What about modifying the check constraint while using the IDENT_INSERT. You can disable the check constraint (temporarily) for INSERTs and UPDATES.see http://msdn2.microsoft.com/en-US/library/ms179491.aspx-ec |
|
|
|
abarringer
Starting Member
13 Posts |
Posted - 2007-02-08 : 15:27:44
|
Answer is below, somewhat obvious in retrospect I guess.quote:
ALTER TABLE customer NOCHECK CONSTRAINT repl_identity_range_4E0542C3_B86F_477D_96F0_55365E263EF8set identity_insert customer oninsert into customer(id)values(1)set identity_insert customer offALTER TABLE customer CHECK CONSTRAINT repl_identity_range_4E0542C3_B86F_477D_96F0_55365E263EF8
Thanks for your help =) |
|
|
|
|
|
|
Insert out of range ID value into Identity Column?