April 4, 2006 at 2:53 pm
I have a security issue. There are a few people that connect to our SQL server from MS Access. I have made up a SQL username and given them 'denywrite' on our database and given them 'Select' access to certain tables within the database.
If they have the table open in MS Access, it seems to put a lock on the table. SO when my DTS package goes to open the table ‘deletes records first, then insert'. It hangs.
What am I doing wrong with my security? Can't MS Access people open (read) a table without any locks going against the table?
April 5, 2006 at 4:03 am
Basically no. You can't 'open' a table without generating some kind of locks (even if you have used just a SELECT). You should investigate WHICH type of locks have been put on the table and then try to work with those locks. I.e maybe it helps to put a clustered index that clearly states where your data to delete exists physically. In other words: in the best of circumstances you are able to DELETE from a table at the same time someone is doing a SELECT as long as you are working on disparate data.
Hanslindgren
April 5, 2006 at 6:55 am
Here is what the object looks like.
LockType_______Mode_____Status______Owner______Index______Resource
TAB____________IS_______Grant________Xact________________________________
PAG____________S________Grant________Xact______PK_WhseMatrix_____1:710296
The table has 4 columns, CustomerNumber,ShipTo,ProductCode,Discount
I have a clustered index on CustomerNumber,ShipTo,ProductCode.
I'm not sure when you mean "maybe it helps to put a clustered index that clearly states where your data to delete exists physically"
So when MS Access opens the table, then my DTS runs on the hour to delete the table and re-insert data, MS Access locks the table, which blocks the DTS.
April 5, 2006 at 7:07 am
Okay. For it to work you need to delete data other then the data currently locked by Access. I.e if you are doing a full delete (consider TRUNCATE instead) you cannot avoid being locked by access unless you would manage to get access to use Disconnected data. If you are not doing a full delete you SHOULD be able to delete the data that has not been selected in access.
It is quite hard to explain.
What is the data you are deleting with your DTS?
What if you change your clustered index to be on some other column? Have a clustered index that covers multiple columns is not always the most effective way and requires quite much overhead when doing inserts.
Remember that clustered index != primary key, especially in datawarehousing.
April 5, 2006 at 8:29 am
i had the same issue too, sometime access locks table if user were running a big query on it. to avoid be blocked, i use a stored procedure to check if table is locked.
====================================================
create procedure spGetTableLockInd @databasename varchar(10), @tablename varchar(50), @lockind bit output
as
create table #lock(num_lock int)
exec( ' SET QUOTED_IDENTIFIER OFF ' +
' insert #lock ' +
' select num_lock = count(*) ' +
' from master.dbo.syslockinfo l, master.dbo.sysdatabases d, ' + @databasename + '.dbo.sysobjects o ' +
' where l.rsc_dbid = d.dbid ' +
' and l.rsc_objid = o.id ' +
' and d.name = "' + @databasename + '"' +
' and o.name = "' + @tablename + '"' +
' SET QUOTED_IDENTIFIER ON ' )
select @lockind = case when num_lock <> 0 then 1 else 0 end from #lock
return
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply