HI!
how do i make the store procudere only exec when new data inserted in the other table? recently i hav use sp_procoption to make an autoexecution.... but it will insert the new data together with the old data
any suggestion?
CREATE PROCEDURE [P1] AS
INSERT INTO TABLE1
SELECT sa.NoMatriks, sa.SesiSem,
SUM(Nilai * KreditSubjek) / SUM(KreditSubjek) AS CPA,
SUM(KreditSubjek) AS JumKredit,
p.Agama, p.Aliran, p.Negeri, p.Kaum
FROM TblSubjekAmbil sa, TblSubjek s, TblGred g, TblPelajar p
WHERE sa.NoMatriks = p.NoMatriks AND
sa.KodSubjek = s.KodSubjek AND
sa.Gred = g.Gred
GROUP BY sa.NoMatriks, sa.SesiSem,
p.Agama, p.Aliran, p.Negeri,
p.Kaum (SELECT sa.NoMatriks, t1.NoMatriks
FROM TblSubjAmbil sa, TABLE1 t1
WHERE t1.NoMatriks <> sa.NoMatriks)
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'
exec sp_makestartup N'P1'Databases have a great concept, called trigger. Just define an INSERT trigger for your table, and let that trigger execute your code or procedure.sql
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
EXEC stored procedure for every line of SELECT result table - how?
Hello,
Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.
I guess it's possible with cursors, but maybe it's possible to make it
easier.
Give an example, please.
Thank you in advance.
Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert
A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:
http://www.sommarskog.se/share_data.html
After that, you can do something like this:
declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()
open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end
close cur
deallocate cur
Simon|||Thanks. It was quite fast and easy to create such procedure.
Tuesday, March 27, 2012
EXEC statement
Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:
EXEC(SELECT QRY FROM temp_Update)
Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:
Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 123
Incorrect syntax near ')'.
Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.
This sounds quite 'unwieldy' to me. And smells of a bad data design.
But sometimes you have to live with what you inherit.
Consider having the TRIGGER execute a Stored Procedure.
The TRIGGER never changes.
Then you only have to Alter the Stored Procedure.
And to get past the 8k limit, you can do this:
EXECUTE (@.Variable1 + @.Variable2 + etc.)
|||
I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).
I noticed your example,
Exec(Select Qry From temp_Update)
It is not correct, On exec you have to pass the varchar string or variable as follow as
Exec('Select Qry From temp_Update')
Or
Declare @.SQL as Varchar(max)
Set @.SQL = 'Select Qry From temp_Update'
Exec(@.SQL)
|||Hi Arnie,
Thanks for the quick reply. This is not the case of bad data design. See, we can not predict if in the future we have to add another column to the table. What this stored procedure do is to create a trigger whenever a new column is introduced. The newly created trigger is supposed to detect any changes/update made to that table, i.e finding which column has changed, and then insert the "before update" value and "after update" value to a log table. That way we can have a full history of the table in the log table. It will be a disaster if every time a new column added to the table we have to write the "after update" trigger to incorporate the newly added column. If this can be done then all we need to do is call the stored procedure, pass the name of the table and the trigger will be created automatically.|||
Manivannan.D.Sekaran wrote:
I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).
I noticed your example,
Exec(Select Qry From temp_Update)
It is not correct, On exec you have to pass the varchar string or variable as follow as
Exec('Select Qry From temp_Update')
Or
Declare @.SQL as Varchar(max)
Set @.SQL = 'Select Qry From temp_Update'
Exec(@.SQL)
Hi Manivannan,
If you do it that way, the result will be the content of column Qry. What I want to do is to execute the content of Qry. Can it be done?|||
Yes you can,
Code Snippet
Declare @.SQL as Varchar(max)
Declare @.Qry as Varchar(max)
Set @.Qry = 'Col1, Col2, Col3'
Set @.SQL = 'Select ' + @.Qry + ' From temp_Update'
Exec(@.SQL)
|||
Perhaps a better explication:
DECLARE @.SQL nvarchar(max)
SELECT @.SQL = Qry FROM Temp_Update WHERE {criteria}
EXECUTE( @.SQL )
|||Hey,
Thanks again for the quick reply. I have finally found the answer and now I can make the trigger automatically just by calling the stored procedure. And indeed it is using varchar(max) as the solution. That is why I will give the credit to Manivannan. Thanks again to you and Arnie.sql
EXEC SQL TASK to FLAT FILE - questions
Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do?
Thanks
Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?
You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.
|||Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do?
Thanks
Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.
Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?
Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.
You may have more than one option to get this done; but we need more info to help you
|||Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:
TITLE: Microsoft Visual Studio
Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
BUTTONS:
OK
Any help would be greatly appreciated!
Kevin
|||You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.
Thanks.
EXEC SQL TASK to FLAT FILE - questions
Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do?
Thanks
Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?
You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.
|||Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do?
Thanks
Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.
Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?
Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.
You may have more than one option to get this done; but we need more info to help you
|||
Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:
TITLE: Microsoft Visual Studio
Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
BUTTONS:
OK
Any help would be greatly appreciated!
Kevin
|||You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.
Thanks.
exec sp_primarykeys and exec sp_foreignkeys
Hi,
I need to check the primary and the foreign keys of
existing user tables in one table to create the
corresponding Data Model.
I execute the following procedure, i see the BOL and i
need to enter one linked server for the procedure execute
fine. Im not doing this because im doing this in the local
machine.
exec sp_primarykeys
How can i generate this Data Model or how can i get this
relations with the minimum effort?
Best RegardsCC&JM
Try put your local sever name and remember you have to enable access data
exec sp_serveroption 'Server','data access','true'
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bded01c47a14$cec4e000$a601280a@.phx.gbl...
> Hi,
> I need to check the primary and the foreign keys of
> existing user tables in one table to create the
> corresponding Data Model.
> I execute the following procedure, i see the BOL and i
> need to enter one linked server for the procedure execute
> fine. Im not doing this because im doing this in the local
> machine.
> exec sp_primarykeys
> How can i generate this Data Model or how can i get this
> relations with the minimum effort?
> Best Regards
>
I need to check the primary and the foreign keys of
existing user tables in one table to create the
corresponding Data Model.
I execute the following procedure, i see the BOL and i
need to enter one linked server for the procedure execute
fine. Im not doing this because im doing this in the local
machine.
exec sp_primarykeys
How can i generate this Data Model or how can i get this
relations with the minimum effort?
Best RegardsCC&JM
Try put your local sever name and remember you have to enable access data
exec sp_serveroption 'Server','data access','true'
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bded01c47a14$cec4e000$a601280a@.phx.gbl...
> Hi,
> I need to check the primary and the foreign keys of
> existing user tables in one table to create the
> corresponding Data Model.
> I execute the following procedure, i see the BOL and i
> need to enter one linked server for the procedure execute
> fine. Im not doing this because im doing this in the local
> machine.
> exec sp_primarykeys
> How can i generate this Data Model or how can i get this
> relations with the minimum effort?
> Best Regards
>
Monday, March 26, 2012
EXEC permission on a SCHEMA COLLECTION?
I have a permissions problem with a table/procedure that I hope someone can help me with.
To set the scene .......
All my procs/tables/functions etc are owned by dbo.
I have a windows security group that is granted permissions to EXEC all procs. No one has permissions to tables.
I have a table that has an XML column and the column has a schema collection bound to it.
The table has a computed column that relies on a function to extract a datetime element from the XML in the XML column and I have an index on this computed column.
I have a proc that selects from this table and uses the computed date column for filtering. However, the select statement is build dynamically and uses sp_execute to perform the SELECT. This of course breaks the ownership chain.
To fix the above I have a user that was created from a certificate and the above proc is signed with the certificate. The user is granted select privileges on the table. This fixes the problem. (In fact, all procs/functions/triggers are signed in this way).
Now (finally) the problem ....
When I run the above proc as admin, it works fine.
When I run it as a member of the security group (mentioned earlier) I receive ...
EXECUTE permission is denied on object 'my_schema_collection', database 'mydb', schema 'dbo'
The 'my_schema_collection' mentioned above is the schema collection to which my xml column is bound.
What? How can I grant EXEC permission to a schema collection?
Anyone have any ideas?
Thanks,
~swg
http://msdn2.microsoft.com/en-us/library/ms179867.aspx fyi.
|||Hi,
Many thanks for this. Of course it solved my problem. (I hate it when people just respond with a link .... it just highlights my foolishness 
Seriously though, as a follow-up question and for my own information, from a security standpoint what is the purpose of granting exec to a schema collection? I'm trying to figure out what the advantage is of having to apply an additional exec permission on a schema collection given that permissions are already granted to tables/procs etc. that use it.
I guess I should start by asking what actually IS exec permission i.e. what does is permit in this context?
Thanks,
~swg
|||You should check on the SQL Server XML forum for details on xml schema collections and their security.
Thanks
Laurentiu
EXEC in SQL Functions
Hi,
I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function
alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)
set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring
RETURN @.RowCnt
END
while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "
does anyone have any ideas of this ?
Thanks.
vidhya
You can't use sp_executesql inside functions.
Why would you want to do this? Perhaps you can change the calling mechanism?
|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
instead of using sp_execute, write another function and pass that variable value into that function.
u can call function into another function.
|||You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.
I think you should do it in a stored procedure
sqlEXEC in SQL Functions
Hi,
I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function
alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)
set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring
RETURN @.RowCnt
END
while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "
does anyone have any ideas of this ?
Thanks.
vidhya
You can't use sp_executesql inside functions.
Why would you want to do this? Perhaps you can change the calling mechanism?
|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
instead of using sp_execute, write another function and pass that variable value into that function.
u can call function into another function.
|||You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.
I think you should do it in a stored procedure
EXEC Command in all existing connections
If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>
Friday, March 23, 2012
EXEC
Hi,
I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...
declare @.strsql varchar(500)
set @.strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'
exec (@.strsql)
When I execute the above script, I immeaditely see the results in the query analyser like below:
Pages Scanned Pages Moved Pages Removed
- -- -
3 0 0
Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....
Just wondering whats goin on..
Thanks.
Ranga
Please take a look at the whitepaper below. That should answer most of your questions.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
|||Thanks Umachandar...the article was enlightening...Excuting distributed queries in parallel
Hi,
Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:
SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')
I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:
SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..
Any suggestions?
Rgds
Bob
TSQL statement execution is always serial in a batch or module. You can run the statements in parallel by doing one of the following:
1. Use on-demand SQL Agent jobs which contain TSQL task with each distributed query. You can then start each job in your TSQL code and wait for their completion
2. In SQL Server 2005, you can use service broker messaging infrastructure to activate multiple procs in parallel with each one executing a specific query
However, it is not clear if you are returning the results to client or processing on the server side itself. If you are returning the results to client then these techniques will not be efficient because you will have to dump the results into a table and then query it back. So you might as well run 3 different commands from the client code.
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 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,GRANTLocks 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,GRANTsql
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,GRANTLocks 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,GRANTsql
Subscribe to:
Posts (Atom)