I'm trying to restore a database back to two nights ago. I
choose the backup I want and the force over existing
database option. It comes back with I need exclusive use
of db. How do I find out who else is on the db? I did
the sp_who active proc and the only one that shows up is
the sa account and me.
HELP!Sp_who should of done it.
Another way of finding out is in EM selecting your server,
then Managment Current Activity, Process Info.
One of the oddities of SQL server is if you have two
connections to the db, then it will not allow you restore.
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>I'm trying to restore a database back to two nights ago.
I
>choose the backup I want and the force over existing
>database option. It comes back with I need exclusive use
>of db. How do I find out who else is on the db? I did
>the sp_who active proc and the only one that shows up is
>the sa account and me.
>HELP!
>.
>|||Hi
If it is sa and you, that makes it 2. sp_who or sp_who2 are the correct SP's
to run. You need to get rid of the sa connection in able to restore the DB.
Cheers
Mike
"Edie Richardson" wrote:
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
> HELP!
>|||"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:48d301c4a17c$928a2480$a501280a@.phx.gbl...
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
>
Don't do sp_who active as even inactive connections can be a problem.
Just use sp_who
(or select * from sysprocesses where dbid= your database id)
> HELP!
Showing posts with label exclusive. Show all posts
Showing posts with label exclusive. Show all posts
Friday, March 23, 2012
Exclusive use during restore - need answer asap
I'm trying to restore a database back to two nights ago. I
choose the backup I want and the force over existing
database option. It comes back with I need exclusive use
of db. How do I find out who else is on the db? I did
the sp_who active proc and the only one that shows up is
the sa account and me.
HELP!
Hi
If it is sa and you, that makes it 2. sp_who or sp_who2 are the correct SP's
to run. You need to get rid of the sa connection in able to restore the DB.
Cheers
Mike
"Edie Richardson" wrote:
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
> HELP!
>
|||"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:48d301c4a17c$928a2480$a501280a@.phx.gbl...
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
>
Don't do sp_who active as even inactive connections can be a problem.
Just use sp_who
(or select * from sysprocesses where dbid= your database id)
> HELP!
choose the backup I want and the force over existing
database option. It comes back with I need exclusive use
of db. How do I find out who else is on the db? I did
the sp_who active proc and the only one that shows up is
the sa account and me.
HELP!
Hi
If it is sa and you, that makes it 2. sp_who or sp_who2 are the correct SP's
to run. You need to get rid of the sa connection in able to restore the DB.
Cheers
Mike
"Edie Richardson" wrote:
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
> HELP!
>
|||"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:48d301c4a17c$928a2480$a501280a@.phx.gbl...
> I'm trying to restore a database back to two nights ago. I
> choose the backup I want and the force over existing
> database option. It comes back with I need exclusive use
> of db. How do I find out who else is on the db? I did
> the sp_who active proc and the only one that shows up is
> the sa account and me.
>
Don't do sp_who active as even inactive connections can be a problem.
Just use sp_who
(or select * from sysprocesses where dbid= your database id)
> HELP!
exclusive set of data query
hi all, given that i have the following tables and data:
mst_locs.locid ctl_loctypes.loctypeid [description]
1 1 [plant]
2 2 [hub]
3 3 [warehouse]
intersect_loc_loctype
locid loctypeid
1 1
1 2
1 3
2 1
2 2
3 3
i want to query out exclusive loctypes data for loctypeid = 3. using the sql
:
select * from intersect_loc_loctype where loctypeid = 3
will return:
locid loctypeid
1 3
3 3
i need a way to loc that are exclusively loctype = 3 [warehouse] such that
the returned data will be:
locid loctypeid
3 3
this is a bit long but i hope my problem was stated clearly.Here's one way:
SELECT *
FROM intersect_loc_loctype ill
WHERE loctypeid = 3
AND NOT EXISTS
(
SELECT *
FROM intresect_loc_loctype ill2
WHERE ill2.locid = ill.locid
AND ill2.loctype <> 3
)
To solve problems like this going forward, I recommend you read the
following article:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Amil" <Amil@.discussions.microsoft.com> wrote in message
news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> hi all, given that i have the following tables and data:
> mst_locs.locid ctl_loctypes.loctypeid [description]
> 1 1 [plant]
> 2 2 [hub]
> 3 3 [warehouse]
> intersect_loc_loctype
> locid loctypeid
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 3
> i want to query out exclusive loctypes data for loctypeid = 3. using the
sql:
> select * from intersect_loc_loctype where loctypeid = 3
> will return:
> locid loctypeid
> 1 3
> 3 3
> i need a way to loc that are exclusively loctype = 3 [warehouse] such that
> the returned data will be:
> locid loctypeid
> 3 3
> this is a bit long but i hope my problem was stated clearly.|||Adam,
Thank you for the solution, and thank you for providing me the link. You
helped me a great deal.
"Adam Machanic" wrote:
> Here's one way:
>
> SELECT *
> FROM intersect_loc_loctype ill
> WHERE loctypeid = 3
> AND NOT EXISTS
> (
> SELECT *
> FROM intresect_loc_loctype ill2
> WHERE ill2.locid = ill.locid
> AND ill2.loctype <> 3
> )
>
> To solve problems like this going forward, I recommend you read the
> following article:
> http://www.dbazine.com/ofinterest/oi-articles/celko1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Amil" <Amil@.discussions.microsoft.com> wrote in message
> news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> sql:
>
>sql
mst_locs.locid ctl_loctypes.loctypeid [description]
1 1 [plant]
2 2 [hub]
3 3 [warehouse]
intersect_loc_loctype
locid loctypeid
1 1
1 2
1 3
2 1
2 2
3 3
i want to query out exclusive loctypes data for loctypeid = 3. using the sql
:
select * from intersect_loc_loctype where loctypeid = 3
will return:
locid loctypeid
1 3
3 3
i need a way to loc that are exclusively loctype = 3 [warehouse] such that
the returned data will be:
locid loctypeid
3 3
this is a bit long but i hope my problem was stated clearly.Here's one way:
SELECT *
FROM intersect_loc_loctype ill
WHERE loctypeid = 3
AND NOT EXISTS
(
SELECT *
FROM intresect_loc_loctype ill2
WHERE ill2.locid = ill.locid
AND ill2.loctype <> 3
)
To solve problems like this going forward, I recommend you read the
following article:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Amil" <Amil@.discussions.microsoft.com> wrote in message
news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> hi all, given that i have the following tables and data:
> mst_locs.locid ctl_loctypes.loctypeid [description]
> 1 1 [plant]
> 2 2 [hub]
> 3 3 [warehouse]
> intersect_loc_loctype
> locid loctypeid
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 3
> i want to query out exclusive loctypes data for loctypeid = 3. using the
sql:
> select * from intersect_loc_loctype where loctypeid = 3
> will return:
> locid loctypeid
> 1 3
> 3 3
> i need a way to loc that are exclusively loctype = 3 [warehouse] such that
> the returned data will be:
> locid loctypeid
> 3 3
> this is a bit long but i hope my problem was stated clearly.|||Adam,
Thank you for the solution, and thank you for providing me the link. You
helped me a great deal.
"Adam Machanic" wrote:
> Here's one way:
>
> SELECT *
> FROM intersect_loc_loctype ill
> WHERE loctypeid = 3
> AND NOT EXISTS
> (
> SELECT *
> FROM intresect_loc_loctype ill2
> WHERE ill2.locid = ill.locid
> AND ill2.loctype <> 3
> )
>
> To solve problems like this going forward, I recommend you read the
> following article:
> http://www.dbazine.com/ofinterest/oi-articles/celko1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Amil" <Amil@.discussions.microsoft.com> wrote in message
> news:4E1F7C1E-8C48-4EEE-BD4B-1477A2F4ADC0@.microsoft.com...
> sql:
>
>sql
Exclusive selection
Maybe it is really simple but right now it's pretty late and I don't have a clue:
Basicly I have two tables with two columns (first one is numeric)
Table A
1:A
2:A
3:B
5:NULL
7:NULL
8:C
Table B
1:A
2:NULL
3:F
5:F
7:NULL
8:NULL
The result should be:
Result Table
1:A
2:A
3:NULL
5:F
7:NULL
8:C
I tried a variaty of joins, subselects and whatever, but failed.
I would appreciate any help.
Kindest regards,
kromoyou will have to explain what you want
the results do not give a clue
for example, how do you get NULL from 3:B and 3:F ??
what are you trying to do?|||Suppose your tables are created as
CREATE TABLE taba (rb number, val varchar2(1));
CREATE TABLE tabb (rb number, val varchar2(1));
and populated as in your example.
Would this do the job?
SELECT rb, MAX(result)
FROM (
SELECT
a.rb,
DECODE(a.val, b.val, b.val, NULL, DECODE(b.val, NULL, NULL, b.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
UNION
SELECT
b.rb,
DECODE(b.val, a.val, a.val, NULL, DECODE(a.val, NULL, NULL, a.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
)
GROUP BY rb
;|||Sorry I didn't explain it further. Say the first column is named ID and the second VALUE.
The result should be for (A.ID = B.ID) and sort of XOR for VALUES.
IF (A.VALUE = B.VALUE)
A.VALUE [OR B.VALUE, it doesn't matter]
IF ( (A.VALUE IS NOT NULL) AND (B.VALUE IS NULL) )
A.VALUE
IF ( (A.VALUE IS NULL) AND (B.VALUE IS NOT NULL) )
B.VALUE
IF ( (A.VALUE IS NOTNULL) AND (B.VALUE IS NOT NULL) AND (A.VALUE <> B.VALUE))
NULL
I am working with Oracle 9.2 (right now), if there is a special thing for Oracle I'll take it, if there is an general solution I would prefer that one.
Thank you.
Kindest regards,
kromo|||Select ta.id, (CASE WHEN ta.value = tb.value THEN ...)
from tableA ta
INNER JOIN
tableB tb ON
ta.id = tb.id|||Thank you all very much.
I tried the "CASE" approach and it worked like a charm.
You saved my day.
Kindest regards,
kromo
Basicly I have two tables with two columns (first one is numeric)
Table A
1:A
2:A
3:B
5:NULL
7:NULL
8:C
Table B
1:A
2:NULL
3:F
5:F
7:NULL
8:NULL
The result should be:
Result Table
1:A
2:A
3:NULL
5:F
7:NULL
8:C
I tried a variaty of joins, subselects and whatever, but failed.
I would appreciate any help.
Kindest regards,
kromoyou will have to explain what you want
the results do not give a clue
for example, how do you get NULL from 3:B and 3:F ??
what are you trying to do?|||Suppose your tables are created as
CREATE TABLE taba (rb number, val varchar2(1));
CREATE TABLE tabb (rb number, val varchar2(1));
and populated as in your example.
Would this do the job?
SELECT rb, MAX(result)
FROM (
SELECT
a.rb,
DECODE(a.val, b.val, b.val, NULL, DECODE(b.val, NULL, NULL, b.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
UNION
SELECT
b.rb,
DECODE(b.val, a.val, a.val, NULL, DECODE(a.val, NULL, NULL, a.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
)
GROUP BY rb
;|||Sorry I didn't explain it further. Say the first column is named ID and the second VALUE.
The result should be for (A.ID = B.ID) and sort of XOR for VALUES.
IF (A.VALUE = B.VALUE)
A.VALUE [OR B.VALUE, it doesn't matter]
IF ( (A.VALUE IS NOT NULL) AND (B.VALUE IS NULL) )
A.VALUE
IF ( (A.VALUE IS NULL) AND (B.VALUE IS NOT NULL) )
B.VALUE
IF ( (A.VALUE IS NOTNULL) AND (B.VALUE IS NOT NULL) AND (A.VALUE <> B.VALUE))
NULL
I am working with Oracle 9.2 (right now), if there is a special thing for Oracle I'll take it, if there is an general solution I would prefer that one.
Thank you.
Kindest regards,
kromo|||Select ta.id, (CASE WHEN ta.value = tb.value THEN ...)
from tableA ta
INNER JOIN
tableB tb ON
ta.id = tb.id|||Thank you all very much.
I tried the "CASE" approach and it worked like a charm.
You saved my day.
Kindest regards,
kromo
Exclusive Row Locking in SQL SERVER 2000
Hi
I have a table with 1 million records. I Have 5 applications (identical) that read from that table and perform the actions. Now, I want an exclusive locks on the data selected. The sql statement is below.
I have a table with 1 million records. I Have 5 applications (identical) that read from that table and perform the actions. Now, I want an exclusive locks on the data selected. The sql statement is below.
select top 1000 * from Numbers
With (***I need this part *** Exclusive lock on the selected data, not allowing other apps to even read)
where IsSent = 1
How to achieve this. Please explain.
Regards,
Noorul
You can do something like below:
begin tran
select top 1000 * from Numbers
With (xlock, rowlock)
where IsSent = 1
And you will have to keep the transaction open for the locks to be effective. But this is not really a good way to use the database engine. Holding a transaction open with locks consumes lot of resources & with above locking you will essentially be blocking any process that tries to do anything with these rows. You will be better off having another column in the table that tracks the state of each row. You can update the selected row(s) from each application and work with that data offline. There are also other ways of doing the same.
|||Thanks Uma,Ok, if that would consume more resources then how about splitting the table into 5 tables (with no repetition of course) and using those tables? If so, then could you provide me the code to how to split the tables plz. I am relatively new to SQL
Regards,
Noorul
Exclusive Query
I want a query that will give me all rows that are in one table but not in
another.
For example:
Table1.ID Table2.ID Result.ID
1 1 4
2 2 5
3 3 6
4
5
6
Thanks
Try:
select id from table1 a
where not exists
(select * from table2 b
where a.id = b.id)
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||That seems to work well. Now I'd like to add the result back into the second
table so that table2 ends up like table1.
I tried:
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
but that doesn't seem to work.
"Vishal Parkar" wrote:
> Try:
> select id from table1 a
> where not exists
> (select * from table2 b
> where a.id = b.id)
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
|||i dont know why it shouldn't work. are you getting any error?
see following example.
create table table1(id int)
create table table2(id int)
insert into table1 values(1)
insert into table1 values(2)
insert into table1 values(3)
insert into table2 values(1)
insert into table2 values(2)
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
another.
For example:
Table1.ID Table2.ID Result.ID
1 1 4
2 2 5
3 3 6
4
5
6
Thanks
Try:
select id from table1 a
where not exists
(select * from table2 b
where a.id = b.id)
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||That seems to work well. Now I'd like to add the result back into the second
table so that table2 ends up like table1.
I tried:
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
but that doesn't seem to work.
"Vishal Parkar" wrote:
> Try:
> select id from table1 a
> where not exists
> (select * from table2 b
> where a.id = b.id)
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
|||i dont know why it shouldn't work. are you getting any error?
see following example.
create table table1(id int)
create table table2(id int)
insert into table1 values(1)
insert into table1 values(2)
insert into table1 values(3)
insert into table2 values(1)
insert into table2 values(2)
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksTry this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>|||Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksTry this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>|||Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
Exclusive OR condition needed for Check Constraint
I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>
Exclusive or
On Mon, 26 Sep 2005 16:17:46 -0700, tshad wrote:
>"tshad" <tscheiderich@.ftsolutions.com> wrote in message
>news:eia2W5uwFHA.2312@.TK2MSFTNGP14.phx.gbl...
(snip)
>I guess the best you can do is:
>SELECT x from y
>where not (hours = 0 and amount = 0) and not (hours <> 0 and amount <> 0)
Hi Tom,
From this code, I gather that the results have to be the same if some
(but not all) fives change to sixes or sevens. Right?
If the values can only be 0 or > 0:
SELECT Hours, Amount
FROM MyTable
WHERE SIGN(Hours) <> SIGN(Amount)
If the values can also be < 0:
SELECT Hours, Amount
FROM MyTable
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
> On Mon, 26 Sep 2005 16:17:46 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> From this code, I gather that the results have to be the same if some
> (but not all) fives change to sixes or sevens. Right?
> If the values can only be 0 or > 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE SIGN(Hours) <> SIGN(Amount)
I don't think this would work. I guess using 5
the issue.
Let me change it to:
hours = 0 amount = 0 no
hours = non-zero amount = 0 yes
hours = 0 amount = non-zero yes
hours = non-zero amount = non-zero no
So a zero in one value or the other. And they both cannot be zero and they
both can't be non-zero.
If you sql had an XOR operator, would be:
...when (hours = 0) XOR (amount=0)
(I'm sure Celko will jump in saying I am thinking like a programmer, again -
which I am because I am)
Tom
> If the values can also be < 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
(snip)
>I don't think this would work.
Hi Tom,
Did you test it?
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4) AS x(Hours, Amount)
WHERE SIGN(Hours) <> SIGN(Amount)
Hours Amount
-- --
0 1
2 0
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4 UNION ALL
SELECT-5, 0 UNION ALL
SELECT 6,-7) AS x(Hours, Amount)
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Hours Amount
-- --
0 1
2 0
-5 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gpjjj1928djumbcp2nt0mhsimo2eimurr0@.
4ax.com...
> On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> Did you test it?
I hadn't actually tested. But I have never used Sign before and at first,
after I figured out how it work, it didn't seem to work (in my head) for
(amount and hours equal to non-zero).
But I changed the select to:
SELECT Hours, SIGN(Hours) AS Expr1, amount, SIGN(amount) AS Expr2
FROM testtable2
WHERE (SIGN(Hours) <> SIGN(amount))
and than I saw why it worked.
Hours Expr1 amount Expr2
-- -- -- --
0 0 3 1
5 1 0 0
Clean.
Thanks,
Tom
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4) AS x(Hours, Amount)
> WHERE SIGN(Hours) <> SIGN(Amount)
> Hours Amount
> -- --
> 0 1
> 2 0
>
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4 UNION ALL
> SELECT-5, 0 UNION ALL
> SELECT 6,-7) AS x(Hours, Amount)
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Hours Amount
> -- --
> 0 1
> 2 0
> -5 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
(re: SIGN)
>Clean.
Hi Tom,
Thank Joe Celko for that. The first time I saw the SIGN function used in
a query was in either a usenet post or a book by Joe. I saw it, and
thought "nice - must add that to my bag of tricks that are seldom used
but can be very useful in specific situation".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:v7ulj1l4rdggdmdg43ip4nqgmt9qi4rc9f@.
4ax.com...
> On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
> (re: SIGN)
> Hi Tom,
> Thank Joe Celko for that.
OK.
I take back All (well, maybe not all) the things I have been saying about
Celko. :)
Thanks,
Tom
>The first time I saw the SIGN function used in
> a query was in either a usenet post or a book by Joe. I saw it, and
> thought "nice - must add that to my bag of tricks that are seldom used
> but can be very useful in specific situation".
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>"tshad" <tscheiderich@.ftsolutions.com> wrote in message
>news:eia2W5uwFHA.2312@.TK2MSFTNGP14.phx.gbl...
(snip)
>I guess the best you can do is:
>SELECT x from y
>where not (hours = 0 and amount = 0) and not (hours <> 0 and amount <> 0)
Hi Tom,
From this code, I gather that the results have to be the same if some
(but not all) fives change to sixes or sevens. Right?
If the values can only be 0 or > 0:
SELECT Hours, Amount
FROM MyTable
WHERE SIGN(Hours) <> SIGN(Amount)
If the values can also be < 0:
SELECT Hours, Amount
FROM MyTable
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
> On Mon, 26 Sep 2005 16:17:46 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> From this code, I gather that the results have to be the same if some
> (but not all) fives change to sixes or sevens. Right?
> If the values can only be 0 or > 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE SIGN(Hours) <> SIGN(Amount)
I don't think this would work. I guess using 5
the issue.Let me change it to:
hours = 0 amount = 0 no
hours = non-zero amount = 0 yes
hours = 0 amount = non-zero yes
hours = non-zero amount = non-zero no
So a zero in one value or the other. And they both cannot be zero and they
both can't be non-zero.
If you sql had an XOR operator, would be:
...when (hours = 0) XOR (amount=0)
(I'm sure Celko will jump in saying I am thinking like a programmer, again -
which I am because I am)
Tom
> If the values can also be < 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
(snip)
>I don't think this would work.
Hi Tom,
Did you test it?
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4) AS x(Hours, Amount)
WHERE SIGN(Hours) <> SIGN(Amount)
Hours Amount
-- --
0 1
2 0
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4 UNION ALL
SELECT-5, 0 UNION ALL
SELECT 6,-7) AS x(Hours, Amount)
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Hours Amount
-- --
0 1
2 0
-5 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gpjjj1928djumbcp2nt0mhsimo2eimurr0@.
4ax.com...
> On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> Did you test it?
I hadn't actually tested. But I have never used Sign before and at first,
after I figured out how it work, it didn't seem to work (in my head) for
(amount and hours equal to non-zero).
But I changed the select to:
SELECT Hours, SIGN(Hours) AS Expr1, amount, SIGN(amount) AS Expr2
FROM testtable2
WHERE (SIGN(Hours) <> SIGN(amount))
and than I saw why it worked.
Hours Expr1 amount Expr2
-- -- -- --
0 0 3 1
5 1 0 0
Clean.
Thanks,
Tom
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4) AS x(Hours, Amount)
> WHERE SIGN(Hours) <> SIGN(Amount)
> Hours Amount
> -- --
> 0 1
> 2 0
>
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4 UNION ALL
> SELECT-5, 0 UNION ALL
> SELECT 6,-7) AS x(Hours, Amount)
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Hours Amount
> -- --
> 0 1
> 2 0
> -5 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
(re: SIGN)
>Clean.
Hi Tom,
Thank Joe Celko for that. The first time I saw the SIGN function used in
a query was in either a usenet post or a book by Joe. I saw it, and
thought "nice - must add that to my bag of tricks that are seldom used
but can be very useful in specific situation".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:v7ulj1l4rdggdmdg43ip4nqgmt9qi4rc9f@.
4ax.com...
> On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
> (re: SIGN)
> Hi Tom,
> Thank Joe Celko for that.
OK.
I take back All (well, maybe not all) the things I have been saying about
Celko. :)
Thanks,
Tom
>The first time I saw the SIGN function used in
> a query was in either a usenet post or a book by Joe. I saw it, and
> thought "nice - must add that to my bag of tricks that are seldom used
> but can be very useful in specific situation".
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Exclusive locks and end users
Hello All,
We have a small database -- CREDITDB -- with about 10 tables with one table
being the most important -- CrdTransactions -- containing all the
transactions. The input to this table comes from two sources (a) xls files
from Third Party Vendors and (b) updates from the end users via a .Net
application.
There is a windows service that reads and loads (Inserts) these xls files
into CrdTransactions frequently and to load it uses a SP named
(InsCrdTransactions).Normally, the xls files contain about 100 to 1000
records. The end users continue to use the application while the windows
service loads the data.
The issue is, when the load kicks in (by the windows service), the
application used by the end users freezes and they are not able to do
anything. I did a sp_who2 and noticed the windows service process blocking
the rest of the processes.
I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on the
KEY of the Clustered Index of CrdTransactions. I had to kill the process to
let the users use the application. However, the users are trying to Update or
View only the existing transactions where as the Windows service is inserting
new records.
I have made sure there are indexes and statistics and that they are rebuilt
and updated regularly.
Did anyone experience something simillar. This is something that I dont
understand.
The SP InsCrdTransactions is a simple and a straight
INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
('col1value','col2value','col2value' ...etc)
spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
Indentity column. Indid 26 is a covering index which was recommended by
Database Engine Tuning advisor and given that it is not helping I can even
remove it.
rgn
spid dbid ObjId IndId Type Resource Mode
Status
-- -- -- -- -- --
-- --
68,5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
GRANT
68,5,0,0,DB, ,S,GRANT
68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
68,5,258099960,2,PAG,1:186 ,IX,GRANT
68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
68,5,258099960,0,TAB, ,IX,GRANT
68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
68,5,2105058535,26,PAG,1:350 ,IX,GRANT
68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
68,5,258099960,1,PAG,1:358 ,IX,GRANT
68,5,2105058535,26,PAG,1:359 ,IX,GRANT
68,5,2105058535,26,PAG,1:355 ,IX,GRANT
68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
68,5,2105058535,26,PAG,1:399 ,IX,GRANT
68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
68,5,2105058535,26,PAG,1:422 ,IX,GRANT
68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
68,5,2105058535,26,PAG,1:629 ,IX,GRANT
68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
68,5,2105058535,1,KEY,(d600aa7c47db) ,X,GRANT
68,5,2105058535,26,KEY,(6004a3965660) ,X,GRANT
68,5,2105058535,26,KEY,(6504ddc5e02d) ,X,GRANT
68,5,2105058535,26,KEY,(5b04e9720e0f) ,X,GRANT
68,5,2105058535,1,KEY,(f00075cb4255) ,X,GRANT
68,5,2105058535,1,KEY,(a200614b1a3f) ,X,GRANT
68,5,2105058535,26,KEY,(cf0381b88883) ,X,GRANT
68,5,2105058535,1,KEY,(fc00cd74941f) ,X,GRANT
68,5,2105058535,1,KEY,(ae00d9f4cc75) ,X,GRANT
68,5,2105058535,26,KEY,(1c0491538797) ,X,GRANT
68,5,2105058535,1,KEY,(ba001134b7aa) ,X,GRANT
68,5,2105058535,26,KEY,(0f04718ff7f8) ,X,GRANT
68,5,2105058535,1,KEY,(e80005b4efc0) ,X,GRANT
68,5,2105058535,1,KEY,(b600a98b61e0) ,X,GRANT
68,5,2105058535,26,KEY,(4e043a57fe5b) ,X,GRANT
68,5,2105058535,1,KEY,(e400bd0b398a) ,X,GRANT
68,5,2105058535,26,KEY,(4304e6b4825f) ,X,GRANT
68,5,2105058535,26,KEY,(c30374929890) ,X,GRANT
68,5,2105058535,1,KEY,(5600a6a0dc37) ,X,GRANT
68,5,2105058535,1,KEY,(4100b7a5d0fb) ,X,GRANT
68,5,2105058535,26,KEY,(9504f4305771) ,X,GRANT
68,5,2105058535,1,KEY,(5a001e1f0a7d) ,X,GRANT
68,5,2105058535,1,KEY,(4d000f1a06b1) ,X,GRANT
68,5,2105058535,26,KEY,(3504adb6d657) ,X,GRANT
68,5,2105058535,1,KEY,(5900c7da7d6e) ,X,GRANT
68,5,2105058535,26,KEY,(3504e8b0a7f3) ,X,GRANT
68,5,2105058535,1,KEY,(4e00d6df71a2) ,X,GRANT
68,5,2105058535,1,KEY,(55007f65ab24) ,X,GRANT
68,5,2105058535,1,KEY,(42006e60a7e8) ,X,GRANT
68,5,2105058535,26,KEY,(4f047fb3e0a7) ,X,GRANT
68,5,2105058535,1,KEY,(2100b452f6c0) ,X,GRANT
68,5,2105058535,26,KEY,(690478a0695b) ,X,GRANT
68,5,2105058535,1,KEY,(3600a557fa0c) ,X,GRANT
68,5,2105058535,26,KEY,(8a04ed6bac80) ,X,GRANT
68,5,2105058535,26,KEY,(f7039e61f863) ,X,GRANT
68,5,2105058535,26,KEY,(87047d956513) ,X,GRANT
68,5,2105058535,26,KEY,(8e04ae8234f3) ,X,GRANT
68,5,2105058535,26,KEY,(db034ca5f010) ,X,GRANT
68,5,2105058535,26,KEY,(7f048dd48c16) ,X,GRANT
68,5,2105058535,1,KEY,(2d000ced208a) ,X,GRANT
68,5,2105058535,1,KEY,(3a001de82c46) ,X,GRANT
68,5,2105058535,26,KEY,(bf037befa965) ,X,GRANT
68,5,2105058535,26,KEY,(cd033f6195bf) ,X,GRANT
68,5,2105058535,1,KEY,(2e00d5285799) ,X,GRANT
68,5,2105058535,1,KEY,(3900c42d5b55) ,X,GRANT
68,5,2105058535,26,KEY,(0304518ad76b) ,X,GRANT
68,5,2105058535,1,KEY,(22006d9781d3) ,X,GRANT
68,5,2105058535,1,KEY,(35007c928d1f) ,X,GRANT
68,5,2105058535,1,KEY,(df001d7413ad) ,X,GRANT
68,5,2105058535,26,KEY,(e703685d930e) ,X,GRANT
68,5,2105058535,1,KEY,(8d0009f44bc7) ,X,GRANT
68,5,2105058535,26,KEY,(da03027738b6) ,X,GRANT
68,5,2105058535,26,KEY,(bb0365630956) ,X,GRANT
68,5,2105058535,1,KEY,(d300a5cbc5e7) ,X,GRANT
68,5,2105058535,1,KEY,(8100b14b9d8d) ,X,GRANT
68,5,2105058535,26,KEY,(4d042c88441e) ,X,GRANT
68,5,2105058535,26,KEY,(57042137dd90) ,X,GRANT
68,5,2105058535,26,KEY,(ba03ca2e5c07) ,X,GRANT
68,5,2105058535,1,KEY,(9500798be652) ,X,GRANT
68,5,2105058535,1,KEY,(c7006d0bbe38) ,X,GRANT
68,5,2105058535,26,KEY,(7304a11b54a4) ,X,GRANT
68,5,2105058535,1,KEY,(9900c1343018) ,X,GRANT
68,5,2105058535,1,KEY,(cb00d5b46872) ,X,GRANT
68,5,2105058535,26,KEY,(ad0377361d5f) ,X,GRANT
68,5,2105058535,1,KEY,(ed000a036dfc) ,X,GRANT
68,5,2105058535,1,KEY,(bf001e833596) ,X,GRANT
68,5,2105058535,26,KEY,(6004917328d9) ,X,GRANT
68,5,2105058535,1,KEY,(e100b2bcbbb6) ,X,GRANT
68,5,2105058535,26,KEY,(7504d0fa2f93) ,X,GRANT
68,5,2105058535,1,KEY,(b300a63ce3dc) ,X,GRANT
68,5,2105058535,1,KEY,(f5007a7cc069) ,X,GRANT
68,5,2105058535,26,KEY,(a3048dc36913) ,X,GRANT
68,5,2105058535,26,KEY,(b703a0f2b080) ,X,GRANT
68,5,2105058535,1,KEY,(f900c2c31623) ,X,GRANT
68,5,2105058535,26,KEY,(ff034f678042) ,X,GRANT
68,5,2105058535,1,KEY,(cc0051cbe3e4) ,X,GRANT
68,5,2105058535,26,KEY,(f90309e3713b) ,X,GRANT
68,5,2105058535,26,KEY,(e6035ec8f5f7) ,X,GRANT
68,5,2105058535,1,KEY,(c000e97435ae) ,X,GRANT
68,5,2105058535,1,KEY,(d40021b44e71) ,X,GRANT
68,5,2105058535,26,KEY,(d103f43977a0) ,X,GRANT
68,5,2105058535,26,KEY,(4d048a5140aa) ,X,GRANT
68,5,2105058535,26,KEY,(2a04a06cd096) ,X,GRANT
68,5,2105058535,1,KEY,(d800990b983b) ,X,GRANT
68,5,2105058535,26,KEY,(9404413c104f) ,X,GRANT
68,5,2105058535,26,KEY,(d00389394f84) ,X,GRANT
68,5,2105058535,26,KEY,(790435d25b14) ,X,GRANT
68,5,2105058535,26,KEY,(85044a3b0367) ,X,GRANT
68,5,2105058535,26,KEY,(8504dbe57ae0) ,X,GRANT
68,5,2105058535,26,KEY,(c603559d29a6) ,X,GRANT
68,5,2105058535,1,KEY,(ac00523cc5df) ,X,GRANT
68,5,2105058535,26,KEY,(de0349a6c802) ,X,GRANT
68,5,2105058535,1,KEY,(fe0046bc9db5) ,X,GRANT
68,5,2105058535,26,KEY,(7004328ff391) ,X,GRANT
68,5,2105058535,1,KEY,(a000ea831395) ,X,GRANT
68,5,2105058535,1,KEY,(f200fe034bff) ,X,GRANT
68,5,2105058535,26,KEY,(7c0425098c1f) ,X,GRANT
68,5,2105058535,1,KEY,(e60036c33020) ,X,GRANT
68,5,2105058535,26,KEY,(4704708aac51) ,X,GRANT
68,5,2105058535,26,KEY,(39049adf8dcc) ,X,GRANT
68,5,2105058535,1,KEY,(b4002243684a) ,X,GRANT
68,5,2105058535,1,KEY,(ea008e7ce66a) ,X,GRANT
68,5,2105058535,1,KEY,(b8009afcbe00) ,X,GRANT
68,5,2105058535,26,KEY,(20047d66ad6b) ,X,GRANT
68,5,2105058535,26,KEY,(a303e443bdc0) ,X,GRANT
68,5,2105058535,1,KEY,(1700fe9f7414) ,X,GRANT
68,5,2105058535,1,KEY,(0000ef9a78d8) ,X,GRANT
68,5,2105058535,26,KEY,(6804079301f8) ,X,GRANT
68,5,2105058535,1,KEY,(1b004620a25e) ,X,GRANT
68,5,2105058535,1,KEY,(0c005725ae92) ,X,GRANT
68,5,2105058535,26,KEY,(5d0430e8d029) ,X,GRANT
68,5,2105058535,1,KEY,(18009fe5d54d) ,X,GRANT
68,5,2105058535,1,KEY,(0f008ee0d981) ,X,GRANT
68,5,2105058535,26,KEY,(1b04916ce5e7) ,X,GRANT
68,5,2105058535,1,KEY,(1400275a0307) ,X,GRANT
68,5,2105058535,1,KEY,(0300365f0fcb) ,X,GRANT
68,5,2105058535,1,KEY,(6000ec6d5ee3) ,X,GRANT
68,5,2105058535,26,KEY,(0804f7241e26) ,X,GRANT
68,5,2105058535,1,KEY,(6c0054d288a9) ,X,GRANT
68,5,2105058535,26,KEY,(f10356f583f2) ,X,GRANT
68,5,2105058535,26,KEY,(b203dcebb422) ,X,GRANT
68,5,2105058535,1,KEY,(6f008d17ffba) ,X,GRANT
68,5,2105058535,26,KEY,(82040b4f6f4b) ,X,GRANT
68,5,2105058535,1,KEY,(78009c12f376) ,X,GRANT
68,5,2105058535,26,KEY,(c403b25b68e8) ,X,GRANT
68,5,2105058535,26,KEY,(6a0452305e0e) ,X,GRANT
68,5,2105058535,1,KEY,(630035a829f0) ,X,GRANT
68,5,2105058535,1,KEY,(740024ad253c) ,X,GRANT
68,5,2105058535,26,KEY,(6a0478c79ca5) ,X,GRANT
68,5,2105058535,26,KEY,(0304eae9b21a) ,X,GRANT
68,5,2105058535,26,KEY,(3f0409b9dfdf) ,X,GRANT
68,5,2105058535,26,KEY,(230420d476aa) ,X,GRANT
68,5,2105058535,1,KEY,(83003a839427) ,X,GRANT
68,5,2105058535,1,KEY,(d1002e03cc4d) ,X,GRANT
68,5,2105058535,26,KEY,(4804bf3b92f5) ,X,GRANT
68,5,2105058535,1,KEY,(8f00823c426d) ,X,GRANT
68,5,2105058535,1,KEY,(dd0096bc1a07) ,X,GRANT
68,5,2105058535,26,KEY,(6704e3002269) ,X,GRANT
68,5,2105058535,26,KEY,(0004ee1515c9) ,X,GRANT
68,5,2105058535,26,KEY,(ae04231e961d) ,X,GRANT
68,5,2105058535,1,KEY,(c9005e7c61d8) ,X,GRANT
68,5,2105058535,1,KEY,(9b004afc39b2) ,X,GRANT
68,5,2105058535,1,KEY,(c500e6c3b792) ,X,GRANT
Locks are held to ensure transactional consistency. In and of themselves
they are not a bad thing. What concerns me here is why a load of such a
small amount of data is taking more than a few seconds. Figure THAT out and
you will have your solution. Not knowing the details of the load process I
can't really advise on what is broken about it. But SOMETHING isn't right.
:-)
As a workaround - and a good practice when you have 'batch' activity you
need to occur during interactive sessions by users - here is a suggestion.
Load the data in small batches (usually this is like 1000-10000 records for
most bulk loads but for some reason your system is barfing on an order of
magnitude less than that). Perhaps even 1 row at a time until you can
figure out the load issue. Maybe even use a staging table and then try the
inserts from that instead of direct via file.
As for DTA and the index, I would consider any index DTA suggested to be
suspect and quite likely a good candidate for removal - or at least review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:95BF4C12-2F17-46EE-B48D-D1A8D43E0B47@.microsoft.com...
> Hello All,
> We have a small database -- CREDITDB -- with about 10 tables with one
> table
> being the most important -- CrdTransactions -- containing all the
> transactions. The input to this table comes from two sources (a) xls files
> from Third Party Vendors and (b) updates from the end users via a .Net
> application.
> There is a windows service that reads and loads (Inserts) these xls files
> into CrdTransactions frequently and to load it uses a SP named
> (InsCrdTransactions).Normally, the xls files contain about 100 to 1000
> records. The end users continue to use the application while the windows
> service loads the data.
> The issue is, when the load kicks in (by the windows service), the
> application used by the end users freezes and they are not able to do
> anything. I did a sp_who2 and noticed the windows service process blocking
> the rest of the processes.
> I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on
> the
> KEY of the Clustered Index of CrdTransactions. I had to kill the process
> to
> let the users use the application. However, the users are trying to Update
> or
> View only the existing transactions where as the Windows service is
> inserting
> new records.
> I have made sure there are indexes and statistics and that they are
> rebuilt
> and updated regularly.
>
> Did anyone experience something simillar. This is something that I dont
> understand.
>
> The SP InsCrdTransactions is a simple and a straight
> INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
> ('col1value','col2value','col2value' ...etc)
> spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
> Indentity column. Indid 26 is a covering index which was recommended by
> Database Engine Tuning advisor and given that it is not helping I can even
> remove it.
> rgn
> spid dbid ObjId IndId Type Resource
> Mode
> Status
> -- -- -- -- -- --
> -- --
> 68, 5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
> GRANT
> 68,5,0,0,DB, ,S,GRANT
> 68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
> 68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
> 68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
> 68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
> 68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
> 68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
> 68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
> 68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
> 68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
> 68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
> 68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
> 68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
> 68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
> 68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
> 68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
> 68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
> 68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
> 68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
> 68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
> 68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
> 68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
> 68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
> 68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
> 68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
> 68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
> 68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
> 68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
> 68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
> 68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
> 68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
> 68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
> 68,5,258099960,2,PAG,1:186 ,IX,GRANT
> 68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
> 68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
> 68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
> 68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
> 68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
> 68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
> 68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
> 68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
> 68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
> 68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
> 68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
> 68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
> 68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
> 68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
> 68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
> 68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
> 68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
> 68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
> 68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
> 68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
> 68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
> 68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
> 68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
> 68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
> 68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
> 68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
> 68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
> 68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
> 68,5,258099960,0,TAB, ,IX,GRANT
> 68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
> 68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
> 68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
> 68,5,2105058535,26,PAG,1:350 ,IX,GRANT
> 68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
> 68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
> 68,5,258099960,1,PAG,1:358 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:359 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:355 ,IX,GRANT
> 68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
> 68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
> 68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
> 68,5,2105058535,26,PAG,1:399 ,IX,GRANT
> 68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
> 68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
> 68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
> 68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
> 68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
> 68,5,2105058535,26,PAG,1:422 ,IX,GRANT
> 68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
> 68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
> 68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
> 68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
> 68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
> 68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
> 68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
> 68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
> 68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
> 68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
> 68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
> 68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
> 68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
> 68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
> 68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
> 68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
> 68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
> 68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
> 68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
> 68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
> 68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
> 68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
> 68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
> 68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
> 68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
> 68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
> 68,5,2105058535,26,PAG,1:629 ,IX,GRANT
> 68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
> 68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
> 68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
> 68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
> 68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
> 68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
> 68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
> 68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
> 68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
> 68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
> 68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
> 68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
> 68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
> 68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
> 68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
> 68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
> 68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
> 68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
> 68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
> 68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
> 68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
> 68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
> 68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
> 68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
> 68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
> 68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
> 68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
> 68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
> 68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
> 68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
> 68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
> 68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
> 68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
> 68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
> 68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
> 68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
> 68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
> 68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
> 68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
> 68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
> 68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
> 68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
> 68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
> 68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
> 68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
> 68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
> 68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
> 68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
> 68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
> 68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
> 68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
> 68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
> 68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
> 68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
> 68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
> 68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
> 68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
> 68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
> 68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
> 68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
> 68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
> 68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
> 68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
> 68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
> 68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
> 68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
> 68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
> 68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
> 68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
> 68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
> 68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
> 68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
> 68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
> 68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
> 68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
> 68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
> 68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
> 68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
> 68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600aa7c47db) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004a3965660) ,X,GRANT
> 68,5,2105058535,26,KEY,(6504ddc5e02d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5b04e9720e0f) ,X,GRANT
> 68,5,2105058535,1,KEY,(f00075cb4255) ,X,GRANT
> 68,5,2105058535,1,KEY,(a200614b1a3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0381b88883) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00cd74941f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ae00d9f4cc75) ,X,GRANT
> 68,5,2105058535,26,KEY,(1c0491538797) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba001134b7aa) ,X,GRANT
> 68,5,2105058535,26,KEY,(0f04718ff7f8) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80005b4efc0) ,X,GRANT
> 68,5,2105058535,1,KEY,(b600a98b61e0) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e043a57fe5b) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400bd0b398a) ,X,GRANT
> 68,5,2105058535,26,KEY,(4304e6b4825f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c30374929890) ,X,GRANT
> 68,5,2105058535,1,KEY,(5600a6a0dc37) ,X,GRANT
> 68,5,2105058535,1,KEY,(4100b7a5d0fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(9504f4305771) ,X,GRANT
> 68,5,2105058535,1,KEY,(5a001e1f0a7d) ,X,GRANT
> 68,5,2105058535,1,KEY,(4d000f1a06b1) ,X,GRANT
> 68,5,2105058535,26,KEY,(3504adb6d657) ,X,GRANT
> 68,5,2105058535,1,KEY,(5900c7da7d6e) ,X,GRANT
> 68,5,2105058535,26,KEY,(3504e8b0a7f3) ,X,GRANT
> 68,5,2105058535,1,KEY,(4e00d6df71a2) ,X,GRANT
> 68,5,2105058535,1,KEY,(55007f65ab24) ,X,GRANT
> 68,5,2105058535,1,KEY,(42006e60a7e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(4f047fb3e0a7) ,X,GRANT
> 68,5,2105058535,1,KEY,(2100b452f6c0) ,X,GRANT
> 68,5,2105058535,26,KEY,(690478a0695b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3600a557fa0c) ,X,GRANT
> 68,5,2105058535,26,KEY,(8a04ed6bac80) ,X,GRANT
> 68,5,2105058535,26,KEY,(f7039e61f863) ,X,GRANT
> 68,5,2105058535,26,KEY,(87047d956513) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04ae8234f3) ,X,GRANT
> 68,5,2105058535,26,KEY,(db034ca5f010) ,X,GRANT
> 68,5,2105058535,26,KEY,(7f048dd48c16) ,X,GRANT
> 68,5,2105058535,1,KEY,(2d000ced208a) ,X,GRANT
> 68,5,2105058535,1,KEY,(3a001de82c46) ,X,GRANT
> 68,5,2105058535,26,KEY,(bf037befa965) ,X,GRANT
> 68,5,2105058535,26,KEY,(cd033f6195bf) ,X,GRANT
> 68,5,2105058535,1,KEY,(2e00d5285799) ,X,GRANT
> 68,5,2105058535,1,KEY,(3900c42d5b55) ,X,GRANT
> 68,5,2105058535,26,KEY,(0304518ad76b) ,X,GRANT
> 68,5,2105058535,1,KEY,(22006d9781d3) ,X,GRANT
> 68,5,2105058535,1,KEY,(35007c928d1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(df001d7413ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(e703685d930e) ,X,GRANT
> 68,5,2105058535,1,KEY,(8d0009f44bc7) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03027738b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(bb0365630956) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300a5cbc5e7) ,X,GRANT
> 68,5,2105058535,1,KEY,(8100b14b9d8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(4d042c88441e) ,X,GRANT
> 68,5,2105058535,26,KEY,(57042137dd90) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03ca2e5c07) ,X,GRANT
> 68,5,2105058535,1,KEY,(9500798be652) ,X,GRANT
> 68,5,2105058535,1,KEY,(c7006d0bbe38) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304a11b54a4) ,X,GRANT
> 68,5,2105058535,1,KEY,(9900c1343018) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00d5b46872) ,X,GRANT
> 68,5,2105058535,26,KEY,(ad0377361d5f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed000a036dfc) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf001e833596) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004917328d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(e100b2bcbbb6) ,X,GRANT
> 68,5,2105058535,26,KEY,(7504d0fa2f93) ,X,GRANT
> 68,5,2105058535,1,KEY,(b300a63ce3dc) ,X,GRANT
> 68,5,2105058535,1,KEY,(f5007a7cc069) ,X,GRANT
> 68,5,2105058535,26,KEY,(a3048dc36913) ,X,GRANT
> 68,5,2105058535,26,KEY,(b703a0f2b080) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900c2c31623) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff034f678042) ,X,GRANT
> 68,5,2105058535,1,KEY,(cc0051cbe3e4) ,X,GRANT
> 68,5,2105058535,26,KEY,(f90309e3713b) ,X,GRANT
> 68,5,2105058535,26,KEY,(e6035ec8f5f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(c000e97435ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(d40021b44e71) ,X,GRANT
> 68,5,2105058535,26,KEY,(d103f43977a0) ,X,GRANT
> 68,5,2105058535,26,KEY,(4d048a5140aa) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a04a06cd096) ,X,GRANT
> 68,5,2105058535,1,KEY,(d800990b983b) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404413c104f) ,X,GRANT
> 68,5,2105058535,26,KEY,(d00389394f84) ,X,GRANT
> 68,5,2105058535,26,KEY,(790435d25b14) ,X,GRANT
> 68,5,2105058535,26,KEY,(85044a3b0367) ,X,GRANT
> 68,5,2105058535,26,KEY,(8504dbe57ae0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603559d29a6) ,X,GRANT
> 68,5,2105058535,1,KEY,(ac00523cc5df) ,X,GRANT
> 68,5,2105058535,26,KEY,(de0349a6c802) ,X,GRANT
> 68,5,2105058535,1,KEY,(fe0046bc9db5) ,X,GRANT
> 68,5,2105058535,26,KEY,(7004328ff391) ,X,GRANT
> 68,5,2105058535,1,KEY,(a000ea831395) ,X,GRANT
> 68,5,2105058535,1,KEY,(f200fe034bff) ,X,GRANT
> 68,5,2105058535,26,KEY,(7c0425098c1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(e60036c33020) ,X,GRANT
> 68,5,2105058535,26,KEY,(4704708aac51) ,X,GRANT
> 68,5,2105058535,26,KEY,(39049adf8dcc) ,X,GRANT
> 68,5,2105058535,1,KEY,(b4002243684a) ,X,GRANT
> 68,5,2105058535,1,KEY,(ea008e7ce66a) ,X,GRANT
> 68,5,2105058535,1,KEY,(b8009afcbe00) ,X,GRANT
> 68,5,2105058535,26,KEY,(20047d66ad6b) ,X,GRANT
> 68,5,2105058535,26,KEY,(a303e443bdc0) ,X,GRANT
> 68,5,2105058535,1,KEY,(1700fe9f7414) ,X,GRANT
> 68,5,2105058535,1,KEY,(0000ef9a78d8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6804079301f8) ,X,GRANT
> 68,5,2105058535,1,KEY,(1b004620a25e) ,X,GRANT
> 68,5,2105058535,1,KEY,(0c005725ae92) ,X,GRANT
> 68,5,2105058535,26,KEY,(5d0430e8d029) ,X,GRANT
> 68,5,2105058535,1,KEY,(18009fe5d54d) ,X,GRANT
> 68,5,2105058535,1,KEY,(0f008ee0d981) ,X,GRANT
> 68,5,2105058535,26,KEY,(1b04916ce5e7) ,X,GRANT
> 68,5,2105058535,1,KEY,(1400275a0307) ,X,GRANT
> 68,5,2105058535,1,KEY,(0300365f0fcb) ,X,GRANT
> 68,5,2105058535,1,KEY,(6000ec6d5ee3) ,X,GRANT
> 68,5,2105058535,26,KEY,(0804f7241e26) ,X,GRANT
> 68,5,2105058535,1,KEY,(6c0054d288a9) ,X,GRANT
> 68,5,2105058535,26,KEY,(f10356f583f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(b203dcebb422) ,X,GRANT
> 68,5,2105058535,1,KEY,(6f008d17ffba) ,X,GRANT
> 68,5,2105058535,26,KEY,(82040b4f6f4b) ,X,GRANT
> 68,5,2105058535,1,KEY,(78009c12f376) ,X,GRANT
> 68,5,2105058535,26,KEY,(c403b25b68e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a0452305e0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(630035a829f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(740024ad253c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a0478c79ca5) ,X,GRANT
> 68,5,2105058535,26,KEY,(0304eae9b21a) ,X,GRANT
> 68,5,2105058535,26,KEY,(3f0409b9dfdf) ,X,GRANT
> 68,5,2105058535,26,KEY,(230420d476aa) ,X,GRANT
> 68,5,2105058535,1,KEY,(83003a839427) ,X,GRANT
> 68,5,2105058535,1,KEY,(d1002e03cc4d) ,X,GRANT
> 68,5,2105058535,26,KEY,(4804bf3b92f5) ,X,GRANT
> 68,5,2105058535,1,KEY,(8f00823c426d) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd0096bc1a07) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704e3002269) ,X,GRANT
> 68,5,2105058535,26,KEY,(0004ee1515c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ae04231e961d) ,X,GRANT
> 68,5,2105058535,1,KEY,(c9005e7c61d8) ,X,GRANT
> 68,5,2105058535,1,KEY,(9b004afc39b2) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500e6c3b792) ,X,GRANT
>
|||Do you know the isolation level used by the load data operation? Can you use
DBCC USEROPTIONS to see this isolation level? Any lock hints used?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> Hello All,
> We have a small database -- CREDITDB -- with about 10 tables with one table
> being the most important -- CrdTransactions -- containing all the
> transactions. The input to this table comes from two sources (a) xls files
> from Third Party Vendors and (b) updates from the end users via a .Net
> application.
> There is a windows service that reads and loads (Inserts) these xls files
> into CrdTransactions frequently and to load it uses a SP named
> (InsCrdTransactions).Normally, the xls files contain about 100 to 1000
> records. The end users continue to use the application while the windows
> service loads the data.
> The issue is, when the load kicks in (by the windows service), the
> application used by the end users freezes and they are not able to do
> anything. I did a sp_who2 and noticed the windows service process blocking
> the rest of the processes.
> I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on the
> KEY of the Clustered Index of CrdTransactions. I had to kill the process to
> let the users use the application. However, the users are trying to Update or
> View only the existing transactions where as the Windows service is inserting
> new records.
> I have made sure there are indexes and statistics and that they are rebuilt
> and updated regularly.
>
> Did anyone experience something simillar. This is something that I dont
> understand.
>
> The SP InsCrdTransactions is a simple and a straight
> INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
> ('col1value','col2value','col2value' ...etc)
> spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
> Indentity column. Indid 26 is a covering index which was recommended by
> Database Engine Tuning advisor and given that it is not helping I can even
> remove it.
> rgn
> spid dbid ObjId IndId Type Resource Mode
> Status
> -- -- -- -- -- --
> -- --
> 68,5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
> GRANT
> 68,5,0,0,DB, ,S,GRANT
> 68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
> 68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
> 68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
> 68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
> 68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
> 68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
> 68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
> 68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
> 68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
> 68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
> 68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
> 68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
> 68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
> 68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
> 68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
> 68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
> 68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
> 68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
> 68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
> 68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
> 68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
> 68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
> 68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
> 68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
> 68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
> 68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
> 68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
> 68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
> 68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
> 68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
> 68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
> 68,5,258099960,2,PAG,1:186 ,IX,GRANT
> 68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
> 68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
> 68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
> 68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
> 68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
> 68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
> 68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
> 68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
> 68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
> 68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
> 68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
> 68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
> 68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
> 68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
> 68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
> 68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
> 68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
> 68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
> 68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
> 68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
> 68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
> 68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
> 68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
> 68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
> 68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
> 68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
> 68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
> 68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
> 68,5,258099960,0,TAB, ,IX,GRANT
> 68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
> 68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
> 68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
> 68,5,2105058535,26,PAG,1:350 ,IX,GRANT
> 68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
> 68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
> 68,5,258099960,1,PAG,1:358 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:359 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:355 ,IX,GRANT
> 68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
> 68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
> 68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
> 68,5,2105058535,26,PAG,1:399 ,IX,GRANT
> 68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
> 68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
> 68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
> 68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
> 68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
> 68,5,2105058535,26,PAG,1:422 ,IX,GRANT
> 68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
> 68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
> 68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
> 68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
> 68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
> 68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
> 68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
> 68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
> 68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
> 68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
> 68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
> 68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
> 68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
> 68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
> 68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
> 68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
> 68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
> 68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
> 68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
> 68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
> 68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
> 68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
> 68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
> 68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
> 68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
> 68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
> 68,5,2105058535,26,PAG,1:629 ,IX,GRANT
> 68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
> 68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
> 68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
> 68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
> 68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
> 68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
> 68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
> 68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
> 68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
> 68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
> 68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
> 68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
> 68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
> 68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
> 68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
> 68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
> 68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
> 68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
> 68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
> 68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
> 68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
> 68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
> 68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
> 68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
> 68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
> 68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
> 68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
> 68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
> 68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
> 68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
> 68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
> 68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
> 68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
> 68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
> 68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
> 68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
> 68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
> 68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
> 68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
> 68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
> 68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
> 68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
> 68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
> 68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
> 68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
> 68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
> 68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
> 68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
> 68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
> 68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
> 68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
> 68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
> 68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
> 68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
> 68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
> 68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
> 68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
> 68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
> 68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
> 68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
> 68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
> 68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
> 68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
> 68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
> 68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
> 68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
> 68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
> 68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
> 68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
> 68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
> 68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
> 68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
> 68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
> 68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
> 68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
> 68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
> 68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
> 68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
> 68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
We have a small database -- CREDITDB -- with about 10 tables with one table
being the most important -- CrdTransactions -- containing all the
transactions. The input to this table comes from two sources (a) xls files
from Third Party Vendors and (b) updates from the end users via a .Net
application.
There is a windows service that reads and loads (Inserts) these xls files
into CrdTransactions frequently and to load it uses a SP named
(InsCrdTransactions).Normally, the xls files contain about 100 to 1000
records. The end users continue to use the application while the windows
service loads the data.
The issue is, when the load kicks in (by the windows service), the
application used by the end users freezes and they are not able to do
anything. I did a sp_who2 and noticed the windows service process blocking
the rest of the processes.
I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on the
KEY of the Clustered Index of CrdTransactions. I had to kill the process to
let the users use the application. However, the users are trying to Update or
View only the existing transactions where as the Windows service is inserting
new records.
I have made sure there are indexes and statistics and that they are rebuilt
and updated regularly.
Did anyone experience something simillar. This is something that I dont
understand.
The SP InsCrdTransactions is a simple and a straight
INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
('col1value','col2value','col2value' ...etc)
spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
Indentity column. Indid 26 is a covering index which was recommended by
Database Engine Tuning advisor and given that it is not helping I can even
remove it.
rgn
spid dbid ObjId IndId Type Resource Mode
Status
-- -- -- -- -- --
-- --
68,5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
GRANT
68,5,0,0,DB, ,S,GRANT
68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
68,5,258099960,2,PAG,1:186 ,IX,GRANT
68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
68,5,258099960,0,TAB, ,IX,GRANT
68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
68,5,2105058535,26,PAG,1:350 ,IX,GRANT
68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
68,5,258099960,1,PAG,1:358 ,IX,GRANT
68,5,2105058535,26,PAG,1:359 ,IX,GRANT
68,5,2105058535,26,PAG,1:355 ,IX,GRANT
68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
68,5,2105058535,26,PAG,1:399 ,IX,GRANT
68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
68,5,2105058535,26,PAG,1:422 ,IX,GRANT
68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
68,5,2105058535,26,PAG,1:629 ,IX,GRANT
68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
68,5,2105058535,1,KEY,(d600aa7c47db) ,X,GRANT
68,5,2105058535,26,KEY,(6004a3965660) ,X,GRANT
68,5,2105058535,26,KEY,(6504ddc5e02d) ,X,GRANT
68,5,2105058535,26,KEY,(5b04e9720e0f) ,X,GRANT
68,5,2105058535,1,KEY,(f00075cb4255) ,X,GRANT
68,5,2105058535,1,KEY,(a200614b1a3f) ,X,GRANT
68,5,2105058535,26,KEY,(cf0381b88883) ,X,GRANT
68,5,2105058535,1,KEY,(fc00cd74941f) ,X,GRANT
68,5,2105058535,1,KEY,(ae00d9f4cc75) ,X,GRANT
68,5,2105058535,26,KEY,(1c0491538797) ,X,GRANT
68,5,2105058535,1,KEY,(ba001134b7aa) ,X,GRANT
68,5,2105058535,26,KEY,(0f04718ff7f8) ,X,GRANT
68,5,2105058535,1,KEY,(e80005b4efc0) ,X,GRANT
68,5,2105058535,1,KEY,(b600a98b61e0) ,X,GRANT
68,5,2105058535,26,KEY,(4e043a57fe5b) ,X,GRANT
68,5,2105058535,1,KEY,(e400bd0b398a) ,X,GRANT
68,5,2105058535,26,KEY,(4304e6b4825f) ,X,GRANT
68,5,2105058535,26,KEY,(c30374929890) ,X,GRANT
68,5,2105058535,1,KEY,(5600a6a0dc37) ,X,GRANT
68,5,2105058535,1,KEY,(4100b7a5d0fb) ,X,GRANT
68,5,2105058535,26,KEY,(9504f4305771) ,X,GRANT
68,5,2105058535,1,KEY,(5a001e1f0a7d) ,X,GRANT
68,5,2105058535,1,KEY,(4d000f1a06b1) ,X,GRANT
68,5,2105058535,26,KEY,(3504adb6d657) ,X,GRANT
68,5,2105058535,1,KEY,(5900c7da7d6e) ,X,GRANT
68,5,2105058535,26,KEY,(3504e8b0a7f3) ,X,GRANT
68,5,2105058535,1,KEY,(4e00d6df71a2) ,X,GRANT
68,5,2105058535,1,KEY,(55007f65ab24) ,X,GRANT
68,5,2105058535,1,KEY,(42006e60a7e8) ,X,GRANT
68,5,2105058535,26,KEY,(4f047fb3e0a7) ,X,GRANT
68,5,2105058535,1,KEY,(2100b452f6c0) ,X,GRANT
68,5,2105058535,26,KEY,(690478a0695b) ,X,GRANT
68,5,2105058535,1,KEY,(3600a557fa0c) ,X,GRANT
68,5,2105058535,26,KEY,(8a04ed6bac80) ,X,GRANT
68,5,2105058535,26,KEY,(f7039e61f863) ,X,GRANT
68,5,2105058535,26,KEY,(87047d956513) ,X,GRANT
68,5,2105058535,26,KEY,(8e04ae8234f3) ,X,GRANT
68,5,2105058535,26,KEY,(db034ca5f010) ,X,GRANT
68,5,2105058535,26,KEY,(7f048dd48c16) ,X,GRANT
68,5,2105058535,1,KEY,(2d000ced208a) ,X,GRANT
68,5,2105058535,1,KEY,(3a001de82c46) ,X,GRANT
68,5,2105058535,26,KEY,(bf037befa965) ,X,GRANT
68,5,2105058535,26,KEY,(cd033f6195bf) ,X,GRANT
68,5,2105058535,1,KEY,(2e00d5285799) ,X,GRANT
68,5,2105058535,1,KEY,(3900c42d5b55) ,X,GRANT
68,5,2105058535,26,KEY,(0304518ad76b) ,X,GRANT
68,5,2105058535,1,KEY,(22006d9781d3) ,X,GRANT
68,5,2105058535,1,KEY,(35007c928d1f) ,X,GRANT
68,5,2105058535,1,KEY,(df001d7413ad) ,X,GRANT
68,5,2105058535,26,KEY,(e703685d930e) ,X,GRANT
68,5,2105058535,1,KEY,(8d0009f44bc7) ,X,GRANT
68,5,2105058535,26,KEY,(da03027738b6) ,X,GRANT
68,5,2105058535,26,KEY,(bb0365630956) ,X,GRANT
68,5,2105058535,1,KEY,(d300a5cbc5e7) ,X,GRANT
68,5,2105058535,1,KEY,(8100b14b9d8d) ,X,GRANT
68,5,2105058535,26,KEY,(4d042c88441e) ,X,GRANT
68,5,2105058535,26,KEY,(57042137dd90) ,X,GRANT
68,5,2105058535,26,KEY,(ba03ca2e5c07) ,X,GRANT
68,5,2105058535,1,KEY,(9500798be652) ,X,GRANT
68,5,2105058535,1,KEY,(c7006d0bbe38) ,X,GRANT
68,5,2105058535,26,KEY,(7304a11b54a4) ,X,GRANT
68,5,2105058535,1,KEY,(9900c1343018) ,X,GRANT
68,5,2105058535,1,KEY,(cb00d5b46872) ,X,GRANT
68,5,2105058535,26,KEY,(ad0377361d5f) ,X,GRANT
68,5,2105058535,1,KEY,(ed000a036dfc) ,X,GRANT
68,5,2105058535,1,KEY,(bf001e833596) ,X,GRANT
68,5,2105058535,26,KEY,(6004917328d9) ,X,GRANT
68,5,2105058535,1,KEY,(e100b2bcbbb6) ,X,GRANT
68,5,2105058535,26,KEY,(7504d0fa2f93) ,X,GRANT
68,5,2105058535,1,KEY,(b300a63ce3dc) ,X,GRANT
68,5,2105058535,1,KEY,(f5007a7cc069) ,X,GRANT
68,5,2105058535,26,KEY,(a3048dc36913) ,X,GRANT
68,5,2105058535,26,KEY,(b703a0f2b080) ,X,GRANT
68,5,2105058535,1,KEY,(f900c2c31623) ,X,GRANT
68,5,2105058535,26,KEY,(ff034f678042) ,X,GRANT
68,5,2105058535,1,KEY,(cc0051cbe3e4) ,X,GRANT
68,5,2105058535,26,KEY,(f90309e3713b) ,X,GRANT
68,5,2105058535,26,KEY,(e6035ec8f5f7) ,X,GRANT
68,5,2105058535,1,KEY,(c000e97435ae) ,X,GRANT
68,5,2105058535,1,KEY,(d40021b44e71) ,X,GRANT
68,5,2105058535,26,KEY,(d103f43977a0) ,X,GRANT
68,5,2105058535,26,KEY,(4d048a5140aa) ,X,GRANT
68,5,2105058535,26,KEY,(2a04a06cd096) ,X,GRANT
68,5,2105058535,1,KEY,(d800990b983b) ,X,GRANT
68,5,2105058535,26,KEY,(9404413c104f) ,X,GRANT
68,5,2105058535,26,KEY,(d00389394f84) ,X,GRANT
68,5,2105058535,26,KEY,(790435d25b14) ,X,GRANT
68,5,2105058535,26,KEY,(85044a3b0367) ,X,GRANT
68,5,2105058535,26,KEY,(8504dbe57ae0) ,X,GRANT
68,5,2105058535,26,KEY,(c603559d29a6) ,X,GRANT
68,5,2105058535,1,KEY,(ac00523cc5df) ,X,GRANT
68,5,2105058535,26,KEY,(de0349a6c802) ,X,GRANT
68,5,2105058535,1,KEY,(fe0046bc9db5) ,X,GRANT
68,5,2105058535,26,KEY,(7004328ff391) ,X,GRANT
68,5,2105058535,1,KEY,(a000ea831395) ,X,GRANT
68,5,2105058535,1,KEY,(f200fe034bff) ,X,GRANT
68,5,2105058535,26,KEY,(7c0425098c1f) ,X,GRANT
68,5,2105058535,1,KEY,(e60036c33020) ,X,GRANT
68,5,2105058535,26,KEY,(4704708aac51) ,X,GRANT
68,5,2105058535,26,KEY,(39049adf8dcc) ,X,GRANT
68,5,2105058535,1,KEY,(b4002243684a) ,X,GRANT
68,5,2105058535,1,KEY,(ea008e7ce66a) ,X,GRANT
68,5,2105058535,1,KEY,(b8009afcbe00) ,X,GRANT
68,5,2105058535,26,KEY,(20047d66ad6b) ,X,GRANT
68,5,2105058535,26,KEY,(a303e443bdc0) ,X,GRANT
68,5,2105058535,1,KEY,(1700fe9f7414) ,X,GRANT
68,5,2105058535,1,KEY,(0000ef9a78d8) ,X,GRANT
68,5,2105058535,26,KEY,(6804079301f8) ,X,GRANT
68,5,2105058535,1,KEY,(1b004620a25e) ,X,GRANT
68,5,2105058535,1,KEY,(0c005725ae92) ,X,GRANT
68,5,2105058535,26,KEY,(5d0430e8d029) ,X,GRANT
68,5,2105058535,1,KEY,(18009fe5d54d) ,X,GRANT
68,5,2105058535,1,KEY,(0f008ee0d981) ,X,GRANT
68,5,2105058535,26,KEY,(1b04916ce5e7) ,X,GRANT
68,5,2105058535,1,KEY,(1400275a0307) ,X,GRANT
68,5,2105058535,1,KEY,(0300365f0fcb) ,X,GRANT
68,5,2105058535,1,KEY,(6000ec6d5ee3) ,X,GRANT
68,5,2105058535,26,KEY,(0804f7241e26) ,X,GRANT
68,5,2105058535,1,KEY,(6c0054d288a9) ,X,GRANT
68,5,2105058535,26,KEY,(f10356f583f2) ,X,GRANT
68,5,2105058535,26,KEY,(b203dcebb422) ,X,GRANT
68,5,2105058535,1,KEY,(6f008d17ffba) ,X,GRANT
68,5,2105058535,26,KEY,(82040b4f6f4b) ,X,GRANT
68,5,2105058535,1,KEY,(78009c12f376) ,X,GRANT
68,5,2105058535,26,KEY,(c403b25b68e8) ,X,GRANT
68,5,2105058535,26,KEY,(6a0452305e0e) ,X,GRANT
68,5,2105058535,1,KEY,(630035a829f0) ,X,GRANT
68,5,2105058535,1,KEY,(740024ad253c) ,X,GRANT
68,5,2105058535,26,KEY,(6a0478c79ca5) ,X,GRANT
68,5,2105058535,26,KEY,(0304eae9b21a) ,X,GRANT
68,5,2105058535,26,KEY,(3f0409b9dfdf) ,X,GRANT
68,5,2105058535,26,KEY,(230420d476aa) ,X,GRANT
68,5,2105058535,1,KEY,(83003a839427) ,X,GRANT
68,5,2105058535,1,KEY,(d1002e03cc4d) ,X,GRANT
68,5,2105058535,26,KEY,(4804bf3b92f5) ,X,GRANT
68,5,2105058535,1,KEY,(8f00823c426d) ,X,GRANT
68,5,2105058535,1,KEY,(dd0096bc1a07) ,X,GRANT
68,5,2105058535,26,KEY,(6704e3002269) ,X,GRANT
68,5,2105058535,26,KEY,(0004ee1515c9) ,X,GRANT
68,5,2105058535,26,KEY,(ae04231e961d) ,X,GRANT
68,5,2105058535,1,KEY,(c9005e7c61d8) ,X,GRANT
68,5,2105058535,1,KEY,(9b004afc39b2) ,X,GRANT
68,5,2105058535,1,KEY,(c500e6c3b792) ,X,GRANT
Locks are held to ensure transactional consistency. In and of themselves
they are not a bad thing. What concerns me here is why a load of such a
small amount of data is taking more than a few seconds. Figure THAT out and
you will have your solution. Not knowing the details of the load process I
can't really advise on what is broken about it. But SOMETHING isn't right.
:-)
As a workaround - and a good practice when you have 'batch' activity you
need to occur during interactive sessions by users - here is a suggestion.
Load the data in small batches (usually this is like 1000-10000 records for
most bulk loads but for some reason your system is barfing on an order of
magnitude less than that). Perhaps even 1 row at a time until you can
figure out the load issue. Maybe even use a staging table and then try the
inserts from that instead of direct via file.
As for DTA and the index, I would consider any index DTA suggested to be
suspect and quite likely a good candidate for removal - or at least review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:95BF4C12-2F17-46EE-B48D-D1A8D43E0B47@.microsoft.com...
> Hello All,
> We have a small database -- CREDITDB -- with about 10 tables with one
> table
> being the most important -- CrdTransactions -- containing all the
> transactions. The input to this table comes from two sources (a) xls files
> from Third Party Vendors and (b) updates from the end users via a .Net
> application.
> There is a windows service that reads and loads (Inserts) these xls files
> into CrdTransactions frequently and to load it uses a SP named
> (InsCrdTransactions).Normally, the xls files contain about 100 to 1000
> records. The end users continue to use the application while the windows
> service loads the data.
> The issue is, when the load kicks in (by the windows service), the
> application used by the end users freezes and they are not able to do
> anything. I did a sp_who2 and noticed the windows service process blocking
> the rest of the processes.
> I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on
> the
> KEY of the Clustered Index of CrdTransactions. I had to kill the process
> to
> let the users use the application. However, the users are trying to Update
> or
> View only the existing transactions where as the Windows service is
> inserting
> new records.
> I have made sure there are indexes and statistics and that they are
> rebuilt
> and updated regularly.
>
> Did anyone experience something simillar. This is something that I dont
> understand.
>
> The SP InsCrdTransactions is a simple and a straight
> INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
> ('col1value','col2value','col2value' ...etc)
> spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
> Indentity column. Indid 26 is a covering index which was recommended by
> Database Engine Tuning advisor and given that it is not helping I can even
> remove it.
> rgn
> spid dbid ObjId IndId Type Resource
> Mode
> Status
> -- -- -- -- -- --
> -- --
> 68, 5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
> GRANT
> 68,5,0,0,DB, ,S,GRANT
> 68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
> 68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
> 68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
> 68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
> 68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
> 68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
> 68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
> 68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
> 68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
> 68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
> 68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
> 68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
> 68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
> 68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
> 68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
> 68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
> 68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
> 68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
> 68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
> 68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
> 68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
> 68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
> 68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
> 68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
> 68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
> 68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
> 68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
> 68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
> 68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
> 68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
> 68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
> 68,5,258099960,2,PAG,1:186 ,IX,GRANT
> 68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
> 68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
> 68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
> 68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
> 68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
> 68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
> 68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
> 68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
> 68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
> 68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
> 68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
> 68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
> 68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
> 68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
> 68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
> 68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
> 68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
> 68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
> 68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
> 68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
> 68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
> 68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
> 68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
> 68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
> 68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
> 68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
> 68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
> 68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
> 68,5,258099960,0,TAB, ,IX,GRANT
> 68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
> 68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
> 68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
> 68,5,2105058535,26,PAG,1:350 ,IX,GRANT
> 68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
> 68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
> 68,5,258099960,1,PAG,1:358 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:359 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:355 ,IX,GRANT
> 68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
> 68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
> 68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
> 68,5,2105058535,26,PAG,1:399 ,IX,GRANT
> 68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
> 68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
> 68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
> 68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
> 68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
> 68,5,2105058535,26,PAG,1:422 ,IX,GRANT
> 68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
> 68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
> 68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
> 68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
> 68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
> 68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
> 68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
> 68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
> 68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
> 68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
> 68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
> 68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
> 68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
> 68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
> 68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
> 68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
> 68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
> 68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
> 68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
> 68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
> 68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
> 68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
> 68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
> 68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
> 68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
> 68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
> 68,5,2105058535,26,PAG,1:629 ,IX,GRANT
> 68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
> 68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
> 68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
> 68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
> 68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
> 68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
> 68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
> 68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
> 68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
> 68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
> 68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
> 68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
> 68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
> 68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
> 68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
> 68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
> 68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
> 68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
> 68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
> 68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
> 68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
> 68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
> 68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
> 68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
> 68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
> 68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
> 68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
> 68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
> 68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
> 68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
> 68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
> 68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
> 68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
> 68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
> 68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
> 68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
> 68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
> 68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
> 68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
> 68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
> 68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
> 68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
> 68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
> 68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
> 68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
> 68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
> 68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
> 68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
> 68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
> 68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
> 68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
> 68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
> 68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
> 68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
> 68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
> 68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
> 68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
> 68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
> 68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
> 68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
> 68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
> 68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
> 68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
> 68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
> 68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
> 68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
> 68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
> 68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
> 68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
> 68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
> 68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
> 68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
> 68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
> 68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
> 68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
> 68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
> 68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
> 68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
> 68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600aa7c47db) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004a3965660) ,X,GRANT
> 68,5,2105058535,26,KEY,(6504ddc5e02d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5b04e9720e0f) ,X,GRANT
> 68,5,2105058535,1,KEY,(f00075cb4255) ,X,GRANT
> 68,5,2105058535,1,KEY,(a200614b1a3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0381b88883) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00cd74941f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ae00d9f4cc75) ,X,GRANT
> 68,5,2105058535,26,KEY,(1c0491538797) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba001134b7aa) ,X,GRANT
> 68,5,2105058535,26,KEY,(0f04718ff7f8) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80005b4efc0) ,X,GRANT
> 68,5,2105058535,1,KEY,(b600a98b61e0) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e043a57fe5b) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400bd0b398a) ,X,GRANT
> 68,5,2105058535,26,KEY,(4304e6b4825f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c30374929890) ,X,GRANT
> 68,5,2105058535,1,KEY,(5600a6a0dc37) ,X,GRANT
> 68,5,2105058535,1,KEY,(4100b7a5d0fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(9504f4305771) ,X,GRANT
> 68,5,2105058535,1,KEY,(5a001e1f0a7d) ,X,GRANT
> 68,5,2105058535,1,KEY,(4d000f1a06b1) ,X,GRANT
> 68,5,2105058535,26,KEY,(3504adb6d657) ,X,GRANT
> 68,5,2105058535,1,KEY,(5900c7da7d6e) ,X,GRANT
> 68,5,2105058535,26,KEY,(3504e8b0a7f3) ,X,GRANT
> 68,5,2105058535,1,KEY,(4e00d6df71a2) ,X,GRANT
> 68,5,2105058535,1,KEY,(55007f65ab24) ,X,GRANT
> 68,5,2105058535,1,KEY,(42006e60a7e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(4f047fb3e0a7) ,X,GRANT
> 68,5,2105058535,1,KEY,(2100b452f6c0) ,X,GRANT
> 68,5,2105058535,26,KEY,(690478a0695b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3600a557fa0c) ,X,GRANT
> 68,5,2105058535,26,KEY,(8a04ed6bac80) ,X,GRANT
> 68,5,2105058535,26,KEY,(f7039e61f863) ,X,GRANT
> 68,5,2105058535,26,KEY,(87047d956513) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04ae8234f3) ,X,GRANT
> 68,5,2105058535,26,KEY,(db034ca5f010) ,X,GRANT
> 68,5,2105058535,26,KEY,(7f048dd48c16) ,X,GRANT
> 68,5,2105058535,1,KEY,(2d000ced208a) ,X,GRANT
> 68,5,2105058535,1,KEY,(3a001de82c46) ,X,GRANT
> 68,5,2105058535,26,KEY,(bf037befa965) ,X,GRANT
> 68,5,2105058535,26,KEY,(cd033f6195bf) ,X,GRANT
> 68,5,2105058535,1,KEY,(2e00d5285799) ,X,GRANT
> 68,5,2105058535,1,KEY,(3900c42d5b55) ,X,GRANT
> 68,5,2105058535,26,KEY,(0304518ad76b) ,X,GRANT
> 68,5,2105058535,1,KEY,(22006d9781d3) ,X,GRANT
> 68,5,2105058535,1,KEY,(35007c928d1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(df001d7413ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(e703685d930e) ,X,GRANT
> 68,5,2105058535,1,KEY,(8d0009f44bc7) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03027738b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(bb0365630956) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300a5cbc5e7) ,X,GRANT
> 68,5,2105058535,1,KEY,(8100b14b9d8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(4d042c88441e) ,X,GRANT
> 68,5,2105058535,26,KEY,(57042137dd90) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03ca2e5c07) ,X,GRANT
> 68,5,2105058535,1,KEY,(9500798be652) ,X,GRANT
> 68,5,2105058535,1,KEY,(c7006d0bbe38) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304a11b54a4) ,X,GRANT
> 68,5,2105058535,1,KEY,(9900c1343018) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00d5b46872) ,X,GRANT
> 68,5,2105058535,26,KEY,(ad0377361d5f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed000a036dfc) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf001e833596) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004917328d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(e100b2bcbbb6) ,X,GRANT
> 68,5,2105058535,26,KEY,(7504d0fa2f93) ,X,GRANT
> 68,5,2105058535,1,KEY,(b300a63ce3dc) ,X,GRANT
> 68,5,2105058535,1,KEY,(f5007a7cc069) ,X,GRANT
> 68,5,2105058535,26,KEY,(a3048dc36913) ,X,GRANT
> 68,5,2105058535,26,KEY,(b703a0f2b080) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900c2c31623) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff034f678042) ,X,GRANT
> 68,5,2105058535,1,KEY,(cc0051cbe3e4) ,X,GRANT
> 68,5,2105058535,26,KEY,(f90309e3713b) ,X,GRANT
> 68,5,2105058535,26,KEY,(e6035ec8f5f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(c000e97435ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(d40021b44e71) ,X,GRANT
> 68,5,2105058535,26,KEY,(d103f43977a0) ,X,GRANT
> 68,5,2105058535,26,KEY,(4d048a5140aa) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a04a06cd096) ,X,GRANT
> 68,5,2105058535,1,KEY,(d800990b983b) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404413c104f) ,X,GRANT
> 68,5,2105058535,26,KEY,(d00389394f84) ,X,GRANT
> 68,5,2105058535,26,KEY,(790435d25b14) ,X,GRANT
> 68,5,2105058535,26,KEY,(85044a3b0367) ,X,GRANT
> 68,5,2105058535,26,KEY,(8504dbe57ae0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603559d29a6) ,X,GRANT
> 68,5,2105058535,1,KEY,(ac00523cc5df) ,X,GRANT
> 68,5,2105058535,26,KEY,(de0349a6c802) ,X,GRANT
> 68,5,2105058535,1,KEY,(fe0046bc9db5) ,X,GRANT
> 68,5,2105058535,26,KEY,(7004328ff391) ,X,GRANT
> 68,5,2105058535,1,KEY,(a000ea831395) ,X,GRANT
> 68,5,2105058535,1,KEY,(f200fe034bff) ,X,GRANT
> 68,5,2105058535,26,KEY,(7c0425098c1f) ,X,GRANT
> 68,5,2105058535,1,KEY,(e60036c33020) ,X,GRANT
> 68,5,2105058535,26,KEY,(4704708aac51) ,X,GRANT
> 68,5,2105058535,26,KEY,(39049adf8dcc) ,X,GRANT
> 68,5,2105058535,1,KEY,(b4002243684a) ,X,GRANT
> 68,5,2105058535,1,KEY,(ea008e7ce66a) ,X,GRANT
> 68,5,2105058535,1,KEY,(b8009afcbe00) ,X,GRANT
> 68,5,2105058535,26,KEY,(20047d66ad6b) ,X,GRANT
> 68,5,2105058535,26,KEY,(a303e443bdc0) ,X,GRANT
> 68,5,2105058535,1,KEY,(1700fe9f7414) ,X,GRANT
> 68,5,2105058535,1,KEY,(0000ef9a78d8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6804079301f8) ,X,GRANT
> 68,5,2105058535,1,KEY,(1b004620a25e) ,X,GRANT
> 68,5,2105058535,1,KEY,(0c005725ae92) ,X,GRANT
> 68,5,2105058535,26,KEY,(5d0430e8d029) ,X,GRANT
> 68,5,2105058535,1,KEY,(18009fe5d54d) ,X,GRANT
> 68,5,2105058535,1,KEY,(0f008ee0d981) ,X,GRANT
> 68,5,2105058535,26,KEY,(1b04916ce5e7) ,X,GRANT
> 68,5,2105058535,1,KEY,(1400275a0307) ,X,GRANT
> 68,5,2105058535,1,KEY,(0300365f0fcb) ,X,GRANT
> 68,5,2105058535,1,KEY,(6000ec6d5ee3) ,X,GRANT
> 68,5,2105058535,26,KEY,(0804f7241e26) ,X,GRANT
> 68,5,2105058535,1,KEY,(6c0054d288a9) ,X,GRANT
> 68,5,2105058535,26,KEY,(f10356f583f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(b203dcebb422) ,X,GRANT
> 68,5,2105058535,1,KEY,(6f008d17ffba) ,X,GRANT
> 68,5,2105058535,26,KEY,(82040b4f6f4b) ,X,GRANT
> 68,5,2105058535,1,KEY,(78009c12f376) ,X,GRANT
> 68,5,2105058535,26,KEY,(c403b25b68e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a0452305e0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(630035a829f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(740024ad253c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a0478c79ca5) ,X,GRANT
> 68,5,2105058535,26,KEY,(0304eae9b21a) ,X,GRANT
> 68,5,2105058535,26,KEY,(3f0409b9dfdf) ,X,GRANT
> 68,5,2105058535,26,KEY,(230420d476aa) ,X,GRANT
> 68,5,2105058535,1,KEY,(83003a839427) ,X,GRANT
> 68,5,2105058535,1,KEY,(d1002e03cc4d) ,X,GRANT
> 68,5,2105058535,26,KEY,(4804bf3b92f5) ,X,GRANT
> 68,5,2105058535,1,KEY,(8f00823c426d) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd0096bc1a07) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704e3002269) ,X,GRANT
> 68,5,2105058535,26,KEY,(0004ee1515c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ae04231e961d) ,X,GRANT
> 68,5,2105058535,1,KEY,(c9005e7c61d8) ,X,GRANT
> 68,5,2105058535,1,KEY,(9b004afc39b2) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500e6c3b792) ,X,GRANT
>
|||Do you know the isolation level used by the load data operation? Can you use
DBCC USEROPTIONS to see this isolation level? Any lock hints used?
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> Hello All,
> We have a small database -- CREDITDB -- with about 10 tables with one table
> being the most important -- CrdTransactions -- containing all the
> transactions. The input to this table comes from two sources (a) xls files
> from Third Party Vendors and (b) updates from the end users via a .Net
> application.
> There is a windows service that reads and loads (Inserts) these xls files
> into CrdTransactions frequently and to load it uses a SP named
> (InsCrdTransactions).Normally, the xls files contain about 100 to 1000
> records. The end users continue to use the application while the windows
> service loads the data.
> The issue is, when the load kicks in (by the windows service), the
> application used by the end users freezes and they are not able to do
> anything. I did a sp_who2 and noticed the windows service process blocking
> the rest of the processes.
> I did a sp_lock and found that it had nearly 950 Exclusive (X) locks on the
> KEY of the Clustered Index of CrdTransactions. I had to kill the process to
> let the users use the application. However, the users are trying to Update or
> View only the existing transactions where as the Windows service is inserting
> new records.
> I have made sure there are indexes and statistics and that they are rebuilt
> and updated regularly.
>
> Did anyone experience something simillar. This is something that I dont
> understand.
>
> The SP InsCrdTransactions is a simple and a straight
> INSERT dbo.InsCrdTransactions (col1,col2,col3...etc) Values
> ('col1value','col2value','col2value' ...etc)
> spid 68 is of the Windows Service and IndID 1 is a clustered Index on an
> Indentity column. Indid 26 is a covering index which was recommended by
> Database Engine Tuning advisor and given that it is not helping I can even
> remove it.
> rgn
> spid dbid ObjId IndId Type Resource Mode
> Status
> -- -- -- -- -- --
> -- --
> 68,5, 2105058535, 1, KEY, (1c00ff1875c3) ,X,
> GRANT
> 68,5,0,0,DB, ,S,GRANT
> 68,5,2105058535,1,KEY,(0b00ee1d790f) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603a3c3c54b) ,X,GRANT
> 68,5,2105058535,1,KEY,(100047a7a389) ,X,GRANT
> 68,5,2105058535,26,KEY,(b50393e41547) ,X,GRANT
> 68,5,2105058535,1,KEY,(070056a2af45) ,X,GRANT
> 68,5,2105058535,26,KEY,(8904e2b2628d) ,X,GRANT
> 68,5,2105058535,1,KEY,(13009e62d49a) ,X,GRANT
> 68,5,2105058535,26,KEY,(fa03d81fd43c) ,X,GRANT
> 68,5,2105058535,1,KEY,(04008f67d856) ,X,GRANT
> 68,5,2105058535,26,KEY,(640448e6b850) ,X,GRANT
> 68,5,2105058535,26,KEY,(59044fc400c4) ,X,GRANT
> 68,5,2105058535,1,KEY,(1f0026dd02d0) ,X,GRANT
> 68,5,2105058535,1,KEY,(080037d80e1c) ,X,GRANT
> 68,5,2105058535,26,KEY,(f403a61dfd40) ,X,GRANT
> 68,5,2105058535,26,KEY,(080436e7f77c) ,X,GRANT
> 68,5,2105058535,26,KEY,(6004b11c8d97) ,X,GRANT
> 68,5,2105058535,1,KEY,(6b00edea5f34) ,X,GRANT
> 68,5,2105058535,26,KEY,(a004f51d72ab) ,X,GRANT
> 68,5,2105058535,1,KEY,(67005555897e) ,X,GRANT
> 68,5,2105058535,1,KEY,(7000445085b2) ,X,GRANT
> 68,5,2105058535,26,KEY,(5c04f793c155) ,X,GRANT
> 68,5,2105058535,1,KEY,(64008c90fe6d) ,X,GRANT
> 68,5,2105058535,1,KEY,(73009d95f2a1) ,X,GRANT
> 68,5,2105058535,1,KEY,(6800342f2827) ,X,GRANT
> 68,5,2105058535,1,KEY,(7f00252a24eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(e003cb82f9c3) ,X,GRANT
> 68,5,2105058535,26,KEY,(9404f3fd7f8d) ,X,GRANT
> 68,5,2105058535,26,KEY,(89047825075a) ,X,GRANT
> 68,5,2105058535,26,KEY,(130482d25e15) ,X,GRANT
> 68,5,2105058535,1,KEY,(c700504ce233) ,X,GRANT
> 68,5,2105058535,26,KEY,(d0031b752f78) ,X,GRANT
> 68,5,2105058535,1,KEY,(cb00e8f33479) ,X,GRANT
> 68,5,2105058535,26,KEY,(5904cd5c75b3) ,X,GRANT
> 68,5,2105058535,26,KEY,(1604f1161622) ,X,GRANT
> 68,5,2105058535,26,KEY,(d603e92d27c1) ,X,GRANT
> 68,5,2105058535,1,KEY,(df0020334fa6) ,X,GRANT
> 68,5,2105058535,26,KEY,(da03d36604dc) ,X,GRANT
> 68,5,2105058535,26,KEY,(8d04ca28629e) ,X,GRANT
> 68,5,2105058535,1,KEY,(d300988c99ec) ,X,GRANT
> 68,5,258099960,2,PAG,1:186 ,IX,GRANT
> 68,5,2105058535,1,KEY,(a70053bbc408) ,X,GRANT
> 68,5,2105058535,1,KEY,(f500473b9c62) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac04f8d77cc1) ,X,GRANT
> 68,5,2105058535,26,KEY,(d10307132a29) ,X,GRANT
> 68,5,2105058535,1,KEY,(ab00eb041242) ,X,GRANT
> 68,5,2105058535,26,KEY,(2b0420f251c9) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03fec40486) ,X,GRANT
> 68,5,2105058535,1,KEY,(f900ff844a28) ,X,GRANT
> 68,5,2105058535,26,KEY,(630426e45c1d) ,X,GRANT
> 68,5,2105058535,26,KEY,(22048e8ba0e8) ,X,GRANT
> 68,5,2105058535,26,KEY,(6704709286e4) ,X,GRANT
> 68,5,2105058535,1,KEY,(ed00374431f7) ,X,GRANT
> 68,5,2105058535,1,KEY,(bf0023c4699d) ,X,GRANT
> 68,5,2105058535,26,KEY,(7304e6f774dd) ,X,GRANT
> 68,5,2105058535,26,KEY,(860421d2058d) ,X,GRANT
> 68,5,2105058535,1,KEY,(e1008ffbe7bd) ,X,GRANT
> 68,5,2105058535,1,KEY,(b3009b7bbfd7) ,X,GRANT
> 68,5,2105058535,1,KEY,(440085550ecc) ,X,GRANT
> 68,5,2105058535,26,KEY,(25045deb8a9e) ,X,GRANT
> 68,5,2105058535,1,KEY,(530094500200) ,X,GRANT
> 68,5,2105058535,26,KEY,(d003d27f24d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(1704cafdd392) ,X,GRANT
> 68,5,2105058535,1,KEY,(48003dead886) ,X,GRANT
> 68,5,2105058535,1,KEY,(5f002cefd44a) ,X,GRANT
> 68,5,2105058535,26,KEY,(460490dc1e62) ,X,GRANT
> 68,5,2105058535,26,KEY,(67048f0c7d69) ,X,GRANT
> 68,5,2105058535,26,KEY,(8204170cef75) ,X,GRANT
> 68,5,2105058535,1,KEY,(4b00e42faf95) ,X,GRANT
> 68,5,2105058535,26,KEY,(fe03da85a664) ,X,GRANT
> 68,5,2105058535,1,KEY,(5c00f52aa359) ,X,GRANT
> 68,5,2105058535,26,KEY,(820486703828) ,X,GRANT
> 68,5,2105058535,1,KEY,(47005c9079df) ,X,GRANT
> 68,5,2105058535,26,KEY,(5204281174f9) ,X,GRANT
> 68,5,2105058535,1,KEY,(50004d957513) ,X,GRANT
> 68,5,2105058535,1,KEY,(330097a7243b) ,X,GRANT
> 68,5,2105058535,1,KEY,(240086a228f7) ,X,GRANT
> 68,5,258099960,0,TAB, ,IX,GRANT
> 68,5,2105058535,26,KEY,(7104336e467c) ,X,GRANT
> 68,5,2105058535,1,KEY,(3f002f18f271) ,X,GRANT
> 68,5,2105058535,1,KEY,(28003e1dfebd) ,X,GRANT
> 68,5,2105058535,26,PAG,1:350 ,IX,GRANT
> 68,5,2105058535,26,KEY,(790425d68cd6) ,X,GRANT
> 68,5,2105058535,1,KEY,(3c00f6dd8562) ,X,GRANT
> 68,5,258099960,1,PAG,1:358 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:359 ,IX,GRANT
> 68,5,2105058535,26,PAG,1:355 ,IX,GRANT
> 68,5,2105058535,1,KEY,(2b00e7d889ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(30004e625328) ,X,GRANT
> 68,5,2105058535,1,KEY,(27005f675fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(00046895b2eb) ,X,GRANT
> 68,5,2105058535,26,KEY,(6c04cedffd06) ,X,GRANT
> 68,5,2105058535,1,KEY,(88003b0495f0) ,X,GRANT
> 68,5,2105058535,1,KEY,(da002f84cd9a) ,X,GRANT
> 68,5,2105058535,26,PAG,1:399 ,IX,GRANT
> 68,5,2105058535,26,KEY,(07046dd0493b) ,X,GRANT
> 68,5,2105058535,26,KEY,(2604a30f2fe4) ,X,GRANT
> 68,5,2105058535,26,KEY,(c7034fcecab0) ,X,GRANT
> 68,5,2105058535,1,KEY,(840083bb43ba) ,X,GRANT
> 68,5,2105058535,26,KEY,(c4038cf91fe8) ,X,GRANT
> 68,5,2105058535,1,KEY,(d600973b1bd0) ,X,GRANT
> 68,5,2105058535,26,KEY,(c603cd53ae06) ,X,GRANT
> 68,5,2105058535,1,KEY,(c2005ffb600f) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04d5983aba) ,X,GRANT
> 68,5,2105058535,26,PAG,1:422 ,IX,GRANT
> 68,5,2105058535,1,KEY,(90004b7b3865) ,X,GRANT
> 68,5,2105058535,26,KEY,(7904d2ca0923) ,X,GRANT
> 68,5,2105058535,26,KEY,(460419b3cd0e) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00e744b645) ,X,GRANT
> 68,5,258099960,1,KEY,(4300b793643a) ,X,GRANT
> 68,5,2105058535,1,KEY,(9c00f3c4ee2f) ,X,GRANT
> 68,5,2105058535,1,KEY,(ba002c73eba1) ,X,GRANT
> 68,5,2105058535,1,KEY,(e80038f3b3cb) ,X,GRANT
> 68,5,2105058535,1,KEY,(b60094cc3deb) ,X,GRANT
> 68,5,2105058535,1,KEY,(e400804c6581) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2034321efb3) ,X,GRANT
> 68,5,2105058535,1,KEY,(f000488c1e5e) ,X,GRANT
> 68,5,2105058535,26,KEY,(c3030869ab66) ,X,GRANT
> 68,5,2105058535,1,KEY,(fc00f033c814) ,X,GRANT
> 68,5,2105058535,26,KEY,(740483e8d484) ,X,GRANT
> 68,5,2105058535,1,KEY,(c900633b3dd3) ,X,GRANT
> 68,5,2105058535,1,KEY,(c500db84eb99) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004283c84b7) ,X,GRANT
> 68,5,2105058535,1,KEY,(d10013449046) ,X,GRANT
> 68,5,2105058535,26,KEY,(87049f02f8fb) ,X,GRANT
> 68,5,2105058535,26,KEY,(49040a7fd0c2) ,X,GRANT
> 68,5,2105058535,1,KEY,(dd00abfb460c) ,X,GRANT
> 68,5,2105058535,26,KEY,(a203f579d7d5) ,X,GRANT
> 68,5,2105058535,1,KEY,(fb00744c4382) ,X,GRANT
> 68,5,2105058535,1,KEY,(a90060cc1be8) ,X,GRANT
> 68,5,2105058535,26,KEY,(fd03258779f2) ,X,GRANT
> 68,5,2105058535,26,KEY,(860454021647) ,X,GRANT
> 68,5,2105058535,26,KEY,(6a04da43e302) ,X,GRANT
> 68,5,2105058535,26,KEY,(e20354094650) ,X,GRANT
> 68,5,2105058535,1,KEY,(f700ccf395c8) ,X,GRANT
> 68,5,2105058535,1,KEY,(a500d873cda2) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803efcd3acb) ,X,GRANT
> 68,5,2105058535,26,KEY,(0e049efd52f4) ,X,GRANT
> 68,5,2105058535,26,KEY,(9704558f015d) ,X,GRANT
> 68,5,2105058535,26,KEY,(670473fb5838) ,X,GRANT
> 68,5,2105058535,1,KEY,(b10010b3b67d) ,X,GRANT
> 68,5,2105058535,26,KEY,(5a046e7650ae) ,X,GRANT
> 68,5,2105058535,1,KEY,(e3000433ee17) ,X,GRANT
> 68,5,2105058535,26,KEY,(2004d156bcf6) ,X,GRANT
> 68,5,2105058535,1,KEY,(bd00a80c6037) ,X,GRANT
> 68,5,2105058535,1,KEY,(ef00bc8c385d) ,X,GRANT
> 68,5,2105058535,26,PAG,1:629 ,IX,GRANT
> 68,5,2105058535,26,KEY,(af045a034def) ,X,GRANT
> 68,5,2105058535,26,KEY,(2c04116e7816) ,X,GRANT
> 68,5,2105058535,1,KEY,(0500dd6aa6ef) ,X,GRANT
> 68,5,2105058535,1,KEY,(1200cc6faa23) ,X,GRANT
> 68,5,2105058535,26,KEY,(8804b291d474) ,X,GRANT
> 68,5,2105058535,1,KEY,(090065d570a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(1e0074d07c69) ,X,GRANT
> 68,5,2105058535,26,KEY,(4e048f75b87f) ,X,GRANT
> 68,5,2105058535,1,KEY,(0a00bc1007b6) ,X,GRANT
> 68,5,2105058535,26,KEY,(6904c45765d7) ,X,GRANT
> 68,5,2105058535,26,KEY,(ff03a8d1e216) ,X,GRANT
> 68,5,2105058535,1,KEY,(1d00ad150b7a) ,X,GRANT
> 68,5,2105058535,26,KEY,(b403bbf915c6) ,X,GRANT
> 68,5,2105058535,26,KEY,(b2039b2463a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(060004afd1fc) ,X,GRANT
> 68,5,2105058535,1,KEY,(110015aadd30) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd0361253521) ,X,GRANT
> 68,5,2105058535,1,KEY,(7200cf988c18) ,X,GRANT
> 68,5,2105058535,26,KEY,(a504f8822b1c) ,X,GRANT
> 68,5,2105058535,1,KEY,(6500de9d80d4) ,X,GRANT
> 68,5,2105058535,26,KEY,(130458b05a00) ,X,GRANT
> 68,5,2105058535,26,KEY,(0c04a9c1bf09) ,X,GRANT
> 68,5,2105058535,1,KEY,(69006622569e) ,X,GRANT
> 68,5,2105058535,26,KEY,(cf0305d3f205) ,X,GRANT
> 68,5,2105058535,1,KEY,(94001cec5aea) ,X,GRANT
> 68,5,2105058535,1,KEY,(c600086c0280) ,X,GRANT
> 68,5,2105058535,26,KEY,(5704b1c3cd3e) ,X,GRANT
> 68,5,2105058535,1,KEY,(e000d7db070e) ,X,GRANT
> 68,5,2105058535,1,KEY,(b200c35b5f64) ,X,GRANT
> 68,5,2105058535,26,KEY,(3004c012e3d9) ,X,GRANT
> 68,5,2105058535,1,KEY,(ec006f64d144) ,X,GRANT
> 68,5,2105058535,1,KEY,(be007be4892e) ,X,GRANT
> 68,5,2105058535,1,KEY,(f800a7a4aa9b) ,X,GRANT
> 68,5,2105058535,26,KEY,(1504aa10a9a5) ,X,GRANT
> 68,5,2105058535,1,KEY,(f4001f1b7cd1) ,X,GRANT
> 68,5,2105058535,26,KEY,(210454f3074e) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103aa5c2a9f) ,X,GRANT
> 68,5,2105058535,1,KEY,(5b007b78b6c5) ,X,GRANT
> 68,5,2105058535,1,KEY,(4c006a7dba09) ,X,GRANT
> 68,5,2105058535,26,KEY,(5e0462054d18) ,X,GRANT
> 68,5,2105058535,26,KEY,(37047073f1cd) ,X,GRANT
> 68,5,2105058535,1,KEY,(5700c3c7608f) ,X,GRANT
> 68,5,2105058535,26,KEY,(9804fe99c1f2) ,X,GRANT
> 68,5,2105058535,1,KEY,(4000d2c26c43) ,X,GRANT
> 68,5,2105058535,26,KEY,(700498443729) ,X,GRANT
> 68,5,2105058535,1,KEY,(54001a02179c) ,X,GRANT
> 68,5,2105058535,1,KEY,(43000b071b50) ,X,GRANT
> 68,5,2105058535,26,KEY,(8e04a206cd38) ,X,GRANT
> 68,5,2105058535,26,KEY,(d503239fe181) ,X,GRANT
> 68,5,2105058535,1,KEY,(5800a2bdc1d6) ,X,GRANT
> 68,5,2105058535,1,KEY,(4f00b3b8cd1a) ,X,GRANT
> 68,5,2105058535,1,KEY,(2c00698a9c32) ,X,GRANT
> 68,5,2105058535,1,KEY,(3b00788f90fe) ,X,GRANT
> 68,5,2105058535,26,KEY,(f3035907644f) ,X,GRANT
> 68,5,2105058535,1,KEY,(2000d1354a78) ,X,GRANT
> 68,5,2105058535,26,KEY,(5f04d2cad80b) ,X,GRANT
> 68,5,2105058535,1,KEY,(3700c03046b4) ,X,GRANT
> 68,5,2105058535,1,KEY,(230008f03d6b) ,X,GRANT
> 68,5,2105058535,1,KEY,(340019f531a7) ,X,GRANT
> 68,5,2105058535,26,KEY,(be03d0cb6068) ,X,GRANT
> 68,5,2105058535,1,KEY,(2f00b04feb21) ,X,GRANT
> 68,5,2105058535,26,KEY,(2904a81ebd04) ,X,GRANT
> 68,5,2105058535,1,KEY,(3800a14ae7ed) ,X,GRANT
> 68,5,2105058535,26,KEY,(1104d953b689) ,X,GRANT
> 68,5,2105058535,26,KEY,(aa0391b56406) ,X,GRANT
> 68,5,2105058535,1,KEY,(0e00dceda738) ,X,GRANT
> 68,5,2105058535,1,KEY,(1900cde8abf4) ,X,GRANT
> 68,5,2105058535,26,KEY,(2a0497925c81) ,X,GRANT
> 68,5,2105058535,1,KEY,(020064527172) ,X,GRANT
> 68,5,2105058535,1,KEY,(150075577dbe) ,X,GRANT
> 68,5,2105058535,26,KEY,(ba03d22a3a3c) ,X,GRANT
> 68,5,2105058535,26,KEY,(b103dbe7705b) ,X,GRANT
> 68,5,2105058535,26,KEY,(c703f4270450) ,X,GRANT
> 68,5,2105058535,26,KEY,(ac03be3809e8) ,X,GRANT
> 68,5,2105058535,1,KEY,(0100bd970661) ,X,GRANT
> 68,5,2105058535,1,KEY,(1600ac920aad) ,X,GRANT
> 68,5,2105058535,26,KEY,(ef0362622f3d) ,X,GRANT
> 68,5,2105058535,26,KEY,(910482d2a7e0) ,X,GRANT
> 68,5,2105058535,1,KEY,(0d000528d02b) ,X,GRANT
> 68,5,2105058535,1,KEY,(1a00142ddce7) ,X,GRANT
> 68,5,2105058535,1,KEY,(6e00df1a8103) ,X,GRANT
> 68,5,2105058535,26,KEY,(5504c543c6d6) ,X,GRANT
> 68,5,2105058535,26,KEY,(2204ba3677cf) ,X,GRANT
> 68,5,2105058535,26,KEY,(8c044264ab26) ,X,GRANT
> 68,5,2105058535,26,KEY,(b804bcd45a30) ,X,GRANT
> 68,5,2105058535,1,KEY,(620067a55749) ,X,GRANT
> 68,5,2105058535,26,KEY,(47046000ee3f) ,X,GRANT
> 68,5,2105058535,26,KEY,(dd033ee2fc1b) ,X,GRANT
> 68,5,2105058535,26,KEY,(3904c8ba04ad) ,X,GRANT
> 68,5,2105058535,26,KEY,(68044043b3f6) ,X,GRANT
> 68,5,2105058535,1,KEY,(7600af652c96) ,X,GRANT
> 68,5,2105058535,1,KEY,(6100be60205a) ,X,GRANT
> 68,5,2105058535,1,KEY,(7a0017dafadc) ,X,GRANT
> 68,5,2105058535,1,KEY,(6d0006dff610) ,X,GRANT
> 68,5,2105058535,26,KEY,(01045a0dd46f) ,X,GRANT
> 68,5,2105058535,26,KEY,(4904d6dcd528) ,X,GRANT
> 68,5,2105058535,1,KEY,(c20062bc3c04) ,X,GRANT
> 68,5,2105058535,1,KEY,(ce00da03ea4e) ,X,GRANT
> 68,5,2105058535,26,KEY,(a803ec69242c) ,X,GRANT
> 68,5,2105058535,1,KEY,(da0012c39191) ,X,GRANT
> 68,5,2105058535,26,KEY,(680414cf09fd) ,X,GRANT
> 68,5,2105058535,26,KEY,(d4036a95dc1f) ,X,GRANT
Subscribe to:
Posts (Atom)