Showing posts with label assigned. Show all posts
Showing posts with label assigned. Show all posts

Monday, March 19, 2012

Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place

Identity Range

Hi,
It has been a while that I have been struggling with an issue of Identity
Range. I have manually assigned Identity Ranges to the table. Inspite of
assigning a good range of 1000 records after adding only 100 records the
table says that it is out of identity range. Why is that?
Please help.
Thanks
issue a dbcc checkident('MytableName') and see what it says. Chances are
that you have blown the range somehow.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> It has been a while that I have been struggling with an issue of Identity
> Range. I have manually assigned Identity Ranges to the table. Inspite of
> assigning a good range of 1000 records after adding only 100 records the
> table says that it is out of identity range. Why is that?
> Please help.
> Thanks
>
|||I am wondering how did a user blow up the range without entering any data...
here is the result from the dbcc
Checking identity information: current identity value '230030', current
column value '310054'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJCF3K79GHA.4468@.TK2MSFTNGP05.phx.gbl...
> issue a dbcc checkident('MytableName') and see what it says. Chances are
> that you have blown the range somehow.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
> news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
>

Friday, February 24, 2012

Identity Column

Hi all,
I am using a identity column in my table. When any rows are deleted in the identity column. The next value is not assigned to the previously deleted number. ie In one column iam generating sequence of number from 1 to 10. for example, the current row is 5. Now iam deleting the row 5. Next number should come only 5. Instead of that, the next value generated is 6. Can u suggest me any idea on this. Of couse, it is the default property how to over come this. Pls help me.

HenryHi there
AFAIK identities (auto assigned) are unique - that means used just once.

I had the same usage for a identity-row, i've simply used non auto-assignment and a procedure wich searches for the lowest free key and assigns it...

greetz
c0r0n3r|||This is the nature of the IDENTITY property. The following is an excerpt from BOL:

"If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON."

So basically if you continue to use the IDENTITY property and wish to attempt to fill the gaps you will have to use the SET IDENTITY_INSERT ON statement.

coroner already mentioned an alternative method if this is a problem. Others just ignore the gaps. Or you can use the method mentioned above.|||Thank u rnealejr & coroner. It was so useful to me.
Thank u so much.|||I'm wondering where SQL Server saves this information about what the "next" generated identity number should be?

i.e How does it know it's been used and deleted.

thanks
js