Bulk Insert cannot open File File is in use by another user

Posted on 2004-11-08
Last Modified: 2011-10-03
I am writing an automated procedure to import files as they show up in a particular directory running as a job.  Everything seems to be working fine until I try to import a file that is currently being copied.  I then get:

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '<file_name>' could not be opened. Operating system error code 32(The process cannot access the file because it is being used by another process.).

which is genereated by the following code:

exec @res = sp_executesql N'exec proc_import_datafile @path, @imp_format_file, @f_reccount output',
      N'@path varchar(255), @imp_format_file varchar(255), @f_reccount int',
      @path, @imp_format_file, @f_reccount

Is it possible to check for a lock on the file before doing the bulk insert?

Thanks in advance
Question by:turner1976
    LVL 6

    Expert Comment

    by:Duane Lawrence
    Could you please post the code to:


    Author Comment


    --  Check if the files exist
    EXEC master..xp_fileexist @datafile, @file_exists OUTPUT
    IF @file_exists = 0

    EXEC master..xp_fileexist @formatfile, @file_exists OUTPUT
    IF @file_exists = 0

    --  Construct BULK INSERT statement
    SELECT @sql = 'BULK INSERT dbo.fdt_buffer FROM '''
    SELECT @sql = @sql + @datafile + ''' WITH ('
    SELECT @sql = @sql + ' FORMATFILE = '''+ @formatfile + ''''
    SELECT @sql = @sql + ', MAXERRORS = 1 '
    SELECT @sql = @sql + ', TABLOCK '
    SELECT @sql = @sql + ' )'

    --  Execute statement
    EXEC (@sql)

    SELECT @recordcount = @@ROWCOUNT
    LVL 19

    Expert Comment

    by:Melih SARICA
    there r 2 reasons.
    U dont ave read access to the file..  Check File Access..
    or someone is really using this file..
    U can Copy ur file using a new name..

    Bulk insert ur new file and then Delete new file.. it ll help u

    Melih SARICA

    Author Comment

    yes.  The probelm is however, that the file is in use by another system that is pushing the file.  So the file isn't complete.  I need to be able to check to see if the file is complete.  No other user will be using the file, only the source system as it is delivering it.  Is it possible to check for a file lock?
    LVL 6

    Expert Comment

    by:Duane Lawrence

    I found something called oh for open handles.

    The second tool, OH (for "open handles"), helps when you try to delete a directory only to have the OS tell you that a file in the directory is "in use by another process." In the past, I've used Sysinternals' great Filemon utility (available at or an old copy of Windows File Manager from Windows NT 3.51 to determine which process was using the file. But I recently discovered a resource kit tool called OH that does the job. (To my embarrassment, OH apparently has been around for quite a while.) OH has many options. I'll give you the short version for finding out what program is preventing you from deleting a file.

    So if you install oh and make sure it is in the system path and run it once, it will tell you the names of the processes that are using it.

    LVL 6

    Expert Comment

    by:Duane Lawrence
    LVL 6

    Accepted Solution

    To my own embarasment, I only scanned the articles in the 2 postes above.  I went back and read them and found they did not have the total answer.

    Get the tool from here

    Then the command to find out if a file is being shared would be something like:
    sp_executesql( oh -t file <filename> )

    This will then tell you if the process that is writing the file is done or not.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now