Does Access lock source table during import?

  • 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

  • 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

  • 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

  • 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.

     

     

  • Thanks very much, appreciate the quick and helpful response.

  • 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.

  • 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.

     

  • 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.

  • 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