October 25, 2006 at 9:58 am
I have a ASP application that randomly throws errors selecting and updating a SQL server DB. The application throws errors for a few seconds and a couple minutes. I'm trying to track down the issues but I've had little success.
Today someone told me that the error happened when they were import one of the SQL server tables into an Access database. I then found out that about 20 users use a read-only account to import various tables into Access from time to time. One of the tables the users import often contains over 2 million records and is often updated by the asp app. So I'm wondering if Access is locking the SQL Server table during the import process. I did a quick search on Google and found nothing.
Does anyone know if Access locks the source table during a table import?
Does anyone have any other ideas on how to determine what is happening?
Any info you can provide would be appreciated.
Thanks,
Scott
October 25, 2006 at 11:41 am
almost positive it does
we have a bunch of access apps that are always running and an automated process to kill their connections if they block another process
October 26, 2006 at 4:14 am
Would you have that automated process code to share, as I have similar issues with database table updates getting locked when Access queries run.
Many thanks
October 26, 2006 at 7:07 am
step 1 - create this sp
CREATE procedure usp_blocking_MSaccess as set nocount on
declare msaccess_cursor cursor
for select spid from master.dbo.sysprocesses with (nolock) where Program_Name like 'Microsoft® Access%'
or Program_Name like 'Microsoft® Office%'
open msaccess_cursor
declare @spid int, @killstate varchar(64), @msg varchar(100), @sql varchar(50)
fetch next from msaccess_cursor into @spid
while @@fetch_status = 0
--begin
if @spid is not null
begin
print convert(varchar(4), @spid) + ' spid killed'
set @killstate = 'kill ' + convert(varchar(4), @spid)
exec (@killstate)
close msaccess_cursor
deallocate msaccess_cursor
end
step 2 create this sp
CREATE procedure check_blocking
as
EXEC usp_blocking_MSaccess_orig
If EXISTS (select * from master.dbo.sysprocesses where blocked > 0 and blocked<>spid and (waittime > 60000 or datediff(minute, last_batch, getdate()) > 2 ))
begin
declare spid_cursor cursor
for select distinct blocked from master.dbo.sysprocesses (nolock)
open spid_cursor
declare @query varchar(255), @spid int, @sql varchar(50)
fetch next from spid_cursor into @spid
while @@fetch_status = 0
begin
create table #dbcc (EventType varchar(30), Parameters int, EventInfo varchar(600))
set @sql = 'dbcc inputbuffer(' + convert(varchar(4),@spid) + ')'
insert into #dbcc exec (@sql)
set @query = (select EventInfo from #dbcc)
drop table #dbcc
INSERT into dbname..blocking
SELECT spid, status, blocked, substring(NT_username,1,25) NT_Login,
substring(hostname,1,30) hostname, substring(loginame,1,30) SQLlogin ,login_time , last_batch, @query, program_name, getdate()
FROM master.dbo.sysprocesses where spid = @spid
fetch next from spid_cursor into @spid
end
close spid_cursor
deallocate spid_cursor
exec master..xp_sendmail --your email message--
end
You will also need to create a blocking table with the proper columns to keep the data for historic purposes
if you want a kill MS Access lite, use this sp in place of the first one
CREATE procedure usp_blocking_MSaccess as
set nocount on
declare @spid int, @killstate varchar(64), @msg varchar(100)
select @spid = (select spid from master.dbo.sysprocesses where Program_Name like 'Microsoft® Access%'
and spid in (select blocked from master.dbo.sysprocesses where blocked > 0) and status = 'sleeping')
if @spid is not null
begin
print convert(varchar(4), @spid) + ' spid killed'
set @killstate = 'kill ' + convert(varchar(4), @spid)
exec (@killstate)
set @msg = 'The following Microsoft® Access process has been killed ' + convert(varchar(4), @spid)
exec master..xp_sendmail
end
GO
this second one was our original one until a week ago. It doesn't kill every process and one Access app put a lock on the main db and locked out 2 customer care centers for 15 minutes. We didn't catch it because we were busy with something else.
We tried to run the top one on a regular basis separately to kill all MS Access but we had problems because we have a few hundred running at any one time. We almost killed the Exchange server and almost started a revolution.
The bottom two were probably taken from the script repository here a few years ago by my predecessor and modified. The top one I rewrote myself, but some similar version is probably in sqlservercentral.
October 26, 2006 at 7:23 am
Thanks very much, appreciate the quick and helpful response.
October 26, 2006 at 8:50 am
just be carefull and test with Exchange if you send the emails
we tried the first one by itself on Monday or Tuesday and it caused a battle royale between SQL and Exchange. SQL won.
October 27, 2006 at 10:45 am
You may want to consider a different strategy:
1. Create views for all tables and views in your db along the lines of:
CREATE VIEW RO_YourTableName
AS
SELECT *
FROM YourTableName WITH (NOLOCK)
2. For your Access users, remove all rights to the db and then GRANT them SELECT rights to only the views starting RO_.
This means:
1. When the Access users attach to the db they will only be able to use the RO_ views.
2. The NOLOCK hint will stop the Access users putting shared locks on your tables.
3. The main downside is that the Access readers will pick up dirty reads, but this probably does not matter.
Another alternative is to create a copy of the live db each night and only give the Access users access to the copy. This means that the Access users may be getting data that is up to one day old.
October 27, 2006 at 11:46 am
officially our policy is for Access to use our R/O copies, but we have an unknown number of MS Access apps floating around that departments created themselves that update the db.
November 16, 2006 at 12:08 pm
I am using Access 2003 and ODBC to link with the tables and am now having numerous table locking issues.
A big problem is when creating reports. Although only reading the data, is SQL Server interpreting these things as possible updates and locking the tables?
Anyway around this way or should I just create views for all of these items?
Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply