question about DBCC checkident

Hi all,
In our application we use a special table(only 2 columns, one of which
is identity) to generate unique keys to use in our client application.One of
my recent requests was to create a procedure that would reserve a set of
keys in the table and return it to client.
The procedure I wrote:
1. Inserts a new row into the table to get the current identity
2. execute dbcc checkident with reseed parameter and the
blocksize+current identity.
3. Another insert into the table to ensure the identity is reset
properly. (I added this step only because in testing I found that this makes
identity setup work correctly).
I have included the code for the procedure at the end of the message.
Now this procedure works fine for a single user. However in multiuser
scenario with more than 100 users running this procedure concurrently,
application server has started crashing.
While trying to simulate this problem, I created a batch process that runs
125 concurrent processes running this procedure. I found something strange
in this. One of things I observed is that the sessions that successfully
run, show the following dbcc output:
"C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
Checking identity information: current identity value '153757', current
column value '153906'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
But some sessions do not report this messages and I think this sessions are
failing the DBCC CheckIdent call siliently. There are no error messages in
sql server error log.
Has anyone seen or experienced this before. Is running DBCC checkident for
such a high number of concurrent users very bad?
Thanx, Amol.
ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
integer,@.al_startkey integer output)
as
begin
declare @.ls_revision varchar(40);
declare @.ls_msgprefix varchar(100);
declare @.ls_sql varchar(1024);
declare @.li_range_end integer;
set @.ls_revision='$Revision: 1.7 $';
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
(tablockx) (dummyvalue) values (1)';
execute(@.ls_sql);
set @.al_startkey=@.@.identity;
set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account for
the previous insert;
set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
'_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
execute (@.ls_sql)
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
(dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
execute(@.ls_sql);
endDon't do it like that. You can create a simple table and sp that will allow
you to get the next ID for a specific table very easily without using
Identities. Have a look at this example:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Amol" <apk@.nospam.cbord.com> wrote in message
news:OxXVhERHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> In our application we use a special table(only 2 columns, one of which
> is identity) to generate unique keys to use in our client application.One
> of my recent requests was to create a procedure that would reserve a set
> of keys in the table and return it to client.
> The procedure I wrote:
> 1. Inserts a new row into the table to get the current identity
> 2. execute dbcc checkident with reseed parameter and the
> blocksize+current identity.
> 3. Another insert into the table to ensure the identity is reset
> properly. (I added this step only because in testing I found that this
> makes identity setup work correctly).
> I have included the code for the procedure at the end of the message.
> Now this procedure works fine for a single user. However in multiuser
> scenario with more than 100 users running this procedure concurrently,
> application server has started crashing.
> While trying to simulate this problem, I created a batch process that runs
> 125 concurrent processes running this procedure. I found something strange
> in this. One of things I observed is that the sessions that successfully
> run, show the following dbcc output:
> "C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
> Checking identity information: current identity value '153757', current
> column value '153906'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
>
> But some sessions do not report this messages and I think this sessions
> are failing the DBCC CheckIdent call siliently. There are no error
> messages in sql server error log.
> Has anyone seen or experienced this before. Is running DBCC checkident for
> such a high number of concurrent users very bad?
> Thanx, Amol.
>
>
> ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
> integer,@.al_startkey integer output)
> as
> begin
> declare @.ls_revision varchar(40);
> declare @.ls_msgprefix varchar(100);
> declare @.ls_sql varchar(1024);
> declare @.li_range_end integer;
> set @.ls_revision='$Revision: 1.7 $';
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
> (tablockx) (dummyvalue) values (1)';
> execute(@.ls_sql);
> set @.al_startkey=@.@.identity;
> set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account
> for the previous insert;
> set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
> '_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
> execute (@.ls_sql)
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
> (dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
> execute(@.ls_sql);
> end
>
>

Question about "Data Transformation Services " : How to define myselfs query without DTS W

hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS

the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard.

but my requirement seems can't be done by DTS wizard.

here are my requirement below.

[move online Database to offline Database ]

1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..)

2. we will have only 2 kind of preserve time one is 6 months another is 12 months

3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data

4. We will have to reference the history data preserve time to delete data after finished data movement

those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?

thank you very much and have a nice day

anyone ? please help...

|||

Hi,

Is there any field (such like datetime) in your datatable which can identify the time of the records. If so, you can use a query to specify the data to transfer. When you run your wizard, choose "use a query to transfer" item in "Specify table copy or query" step. Click on QueryBuilder button to build your filter sql statement and create your query criteria.(datetime filed) In this way, you can transform the data by datetime condition.

Hope that helps. Thanks.

|||

hi thank you is the only way ( run wizard) to finish this job?

could it be done by write a program? thank you

|||

Hi,

Of cause you can write programs to achieve your goal. Just filter the data according to the datetime condition and insert the data into your local(or backup) data server.But actually it's much complicated I think. Since we have a such a tool to ease our work, why don't we enjoy it

Thanks.