Re: Identity Block Reserved For Inserts Issue - Are you good?
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Wed, 1 Nov 2006 12:45:19 -0500
Well, we don't have the timeline of these inserted records (is their
insertions have been started just before you acquire the lock on the table
or sometime after and after, when? (Before, while or after the BCP?) ) , so
it's hard to tell what's happening here.
Probably that you will hear a lot of comments about the use of
Ident_Current() and dbcc, maybe someone will even suggest you to use the
greatest value in the table Member instead of using Ident_Current();
however, are you sure that it's not the values to be inserted with BCC
themselves that could be wrong?
Finally, I don't understand why you aren't posting this in the
m.p.sqlserver.programming newsgroup.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7CFCC393-F243-40B4-A928-CDD9329662EC@xxxxxxxxxxxxxxxx
Hi all,
I have a bit of a complicated question, hope we have an SQL guru out there
that can help us solve this killer problem.
Due to the size of SQL Database we have (largest in the US), we try to
pre-process large data files in IO until we are ready to insert directly
into
the database via BCP (quick, no constraints, no mess... well um that's the
problem)
Because we need to get all the linking before we BCP, we find the current
identity value, push the value up past the number of inserts we are going
to
push in from BCP. We then take the block of identities and insert them
into
the identity column in our files.
Our problem is that sometimes, the identities from the block somehow get
used or were used by a competing process that uses only automated identity
inserts by SQL.
Why? It really sucks...
Here is a simple example:
Row in the file before "reserving" a block of identities:
Identity|projectid|memberno|name
|313|100|Bob
|313|200|Jane
|313|234|Larry
We then run a sproc like this
Begin Tran
Select top 1 @Member_id_start_value = Ident_Current('Member')
from Member with(TabLockx)
Select @newSeed = @Member_id_start_value + @NumRecords + @range_pad
dbcc checkident(Member, reseed, @newSeed)
Commit Tran
Assuming the current Identity was 1000, and the @range_pad was 5 at the
time
of the execution of the sproc:
@Member_id_start_value = 1000
@NumRecords would be 3
@range_pad = 5
making the @newSeed = 1007
We would then take the low value (1000) as the first identity value for
the
first record and ++ for each row thereafter resulting in something like
this:
identity|projectid|memberno|name
1000|313|100|Bob
1001|313|200|Jane
1002|313|234|Larry
This would then get BCP'd up with "Insert Identity Column" switched on.
There is a lag between getting the block and uploading using BCP of
anywhere
from 1 minute to 6 hours.
What happens is that we sometimes find records in the table that have been
assigned an identity in our block.
So, if you got an answer can help us, we'd love to hear it.
BTW - please don't tell me that the problem is because there is a lag
between the time we push the identity past our block range and the insert
from BCP. If that was your answer, you're just un-informed.
Thanks BIG time folks! I know you can do it.
Rob
.
- References:
- Prev by Date: Re: Combine 2 lines in text file conditionally
- Next by Date: Re: issue with collecting Hwnd
- Previous by thread: Re: Identity Block Reserved For Inserts Issue - Are you good?
- Next by thread: RE: Identity Block Reserved For Inserts Issue - Are you good?
- Index(es):
Relevant Pages
|
