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