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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
Could you please post the code to:

turner1976Author Commented:

--  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
Melih SARICAOwnerCommented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

turner1976Author Commented:
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?
Duane LawrenceCommented:

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 http://www.sysinternals.com) 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.

Duane LawrenceCommented:
Duane LawrenceCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.