[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1610
  • Last Modified:

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
0
turner1976
Asked:
turner1976
  • 4
  • 2
1 Solution
 
Duane LawrenceCommented:
Could you please post the code to:
proc_import_datafile

0
 
turner1976Author Commented:
SET NOCOUNT ON


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

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

--  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
0
 
Melih SARICACommented:
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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?
0
 
Duane LawrenceCommented:
http://www.winnetmag.com/Article/ArticleID/23300/23300.html

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.

DuaneLawrence
the
databasedoctor
0
 
Duane LawrenceCommented:
0
 
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
http://www.computerperformance.co.uk/ezine/tools.htm#OH%20(Open%20Handles)

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.

Duane
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now