sp_adjustpublisheridentityrange

[brommies]

the agent has no updated my server identity ranges and i tried to run
sp_adjustpublisheridentityrange but nothing has happened i am still
getting the error where it wont allow me to insert into those tables.

this is a serious problem please help,
thanks

--
Posted via http://dbforums.com

[brommies]

[brommies]

[brommies]

[Jing Sun [MSFT]]

Make sure in your sp_adjustpublisheridentityrange call one of the two
parameter @publication and @tablename is NULL and the other one is not null.
sp_adjustpublisheridentityrange supports two levels: either publication
level or table level since a publication might have multiple articles. So
you can use one of the following two calls depending on which level you want
to change:

sp_adjustpublisheridentityrange @publication='PublicationName'

sp_adjustpublisheridentityrange @table_name ='PublicationName'

but not

sp_adjustpublisheridentityrange @publication='PublicationName', @table_name
='PublicationName'

If above is not the issue. Please provide me more information on what type
of replication you are using -Merge or Updateable(Queued) Transactional and
what is your sp_adjustpublisheridentityrange call.

Jing Sun
SQL Server Product Unit
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

"brommies" <membe...@dbforums.com> wrote in message
news:2756088.1...@dbforums.com...

[Jing Sun [MSFT]]

Sorry the working sample should be

sp_adjustpublisheridentityrange @publication='PublicationName'
or
sp_adjustpublisheridentityrange @table_name ='TableName'

but the following won't work

sp_adjustpublisheridentityrange @publication='PublicationName', @table_name

= 'TableName'

Jing Sun
SQL Server Product Unit
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

"Jing Sun [MSFT]" <JIN...@online.microsoft.com> wrote in message
news:#jWPOtFA...@TK2MSFTNGP11.phx.gbl...

[Jing Sun [MSFT]]

If my previous reply doesn't apply to your scenario and the identity column
value on your publication table is still within the range, you might want to
check the following:

1. Run sp_help TableName to find out the range for publication table under
column <constraint_keys> for the check constraint on ID column. It should be
something like((([id]>(2) AND ([id]<(1000)))) where 2 and 1000 should be
the pub range you defined.

2. Run select ident_current('TableName') to make sure the value returned is
within the range returned by sp_help TableName. In this case <1000.

3. If select ident_current returns wrong value (for example 2000), you may
hit a known bug which has been addressed in SQL2000 SP2 QFE 615 and SQL2000
SP3. You can either apply sp3 or SQL2000 QFE 615(which can be obtained from
Microsoft Product Support) or run following workaround

DBCC CHECKIDENT('TableName, RESEED, new_value) where new_value should be the
next new identity value for ID column generated by SQL server.

Jing Sun
SQL Server Product Unit
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

"brommies" <membe...@dbforums.com> wrote in message
news:2756088.1...@dbforums.com...
>

[brommies]

thanks 4 the reply,

i am running it properly but the id ranges are still not getting updated
correctly. im using merge publication

i have discovered one thing though, there was more than one check
constraint on each published table and when the sp_adjust was run the
one check constraint was updated but the other was not. what would cause
the tables to have more than one constraint? would it be from possibly
publishing the DB at an earlier stage and when the publication was
dropped it did not clean out the constraints?

thank you,
warren

[Jing Sun [MSFT]]

That's strange. Normally, the check constraint on identity column created by
replication should be removed if you drop publication/article from UI or by
calling sp_dropmergearticle unless the record was deleted directly from
sysarticles. Do you mind to run <sp_help TableName > and send me the output?

"brommies" <membe...@dbforums.com> wrote in message

news:2758731.1...@dbforums.com...