File could not be opened

• chuck.forbes

  SSCarpal Tunnel

  Points: 4294

  More actions

  November 5, 2018 at 3:37 pm

  #378555

  So I am a bit baffled. We have a process that we're moving from one SQL Server instance to another, and it's failing to work in the new environment. I've looked around at similar issues, and I think that we've covered all of our bases, but still with no luck. Here's the code we're running:

  declare @loadfile varchar(60)
declare @fileexists int
declare @l_error varchar(200)

  select @loadfile = '\\fileserver\folder\filename.txt'

  EXEC master..xp_fileexist @loadfile, @fileexists OUTPUT

  if @fileexists = 0
begin
set @l_error = 'The file "'+ @loadfile +'" does not exist.'
RAISERROR (@l_error,16,1) WITH SETERROR
end
else 
begin
truncate table dbo.temp_load_table

  bulk insert dbo.temp_load_table 
from '\\fileserver\folder\filename.txt'
with
(
datafiletype = 'char',
rowterminator = '\n',
TABLOCK
)

  end

  And it errors with "Cannot bulk load because the file "\\fileserver\folder\filename.txt" could not be opened. Operating system error code 5(Access is denied.)."

  These are the items that I've checked:
  1) Is this a UNC path? Yes.
  2) Can I access this file via this path, using the AD account running the MSSQL Service? Yes.
  3) If I move this file to the C:\ of the server, will it process without error? Yes.

  It feels like a permissions issue, but, with 1) and 2) checking out, I am completely puzzled. Any help you could provide would be appreciated.

  Thanks,
  --=Chuck

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  November 5, 2018 at 6:17 pm

  #2012467

  chuck.forbes - Monday, November 5, 2018 3:37 PM

  So I am a bit baffled. We have a process that we're moving from one SQL Server instance to another, and it's failing to work in the new environment. I've looked around at similar issues, and I think that we've covered all of our bases, but still with no luck. Here's the code we're running:

  declare @loadfile varchar(60)
declare @fileexists int
declare @l_error varchar(200)

  select @loadfile = '\\fileserver\folder\filename.txt'

  EXEC master..xp_fileexist @loadfile, @fileexists OUTPUT

  if @fileexists = 0
begin
set @l_error = 'The file "'+ @loadfile +'" does not exist.'
RAISERROR (@l_error,16,1) WITH SETERROR
end
else 
begin
truncate table dbo.temp_load_table

  bulk insert dbo.temp_load_table 
from '\\fileserver\folder\filename.txt'
with
(
datafiletype = 'char',
rowterminator = '\n',
TABLOCK
)

  end

  And it errors with "Cannot bulk load because the file "\\fileserver\folder\filename.txt" could not be opened. Operating system error code 5(Access is denied.)."

  These are the items that I've checked:
  1) Is this a UNC path? Yes.
  2) Can I access this file via this path, using the AD account running the MSSQL Service? Yes.
  3) If I move this file to the C:\ of the server, will it process without error? Yes.

  It feels like a permissions issue, but, with 1) and 2) checking out, I am completely puzzled. Any help you could provide would be appreciated.

  Thanks,
  --=Chuck

  Try running process monitor, use the unc path for the path to filter on and check the access denied messages to verify the account having the errors.If the file is locked, in use or who is denied access would show up monitoring file activity.
  Process Monitor v3.50

  Sue

• Jeff Moden

  SSC Guru

  Points: 1003872

  More actions

  November 5, 2018 at 8:21 pm

  #2012477

  chuck.forbes - Monday, November 5, 2018 3:37 PM

  So I am a bit baffled. We have a process that we're moving from one SQL Server instance to another, and it's failing to work in the new environment. I've looked around at similar issues, and I think that we've covered all of our bases, but still with no luck. Here's the code we're running:

  declare @loadfile varchar(60)
declare @fileexists int
declare @l_error varchar(200)

  select @loadfile = '\\fileserver\folder\filename.txt'

  EXEC master..xp_fileexist @loadfile, @fileexists OUTPUT

  if @fileexists = 0
begin
set @l_error = 'The file "'+ @loadfile +'" does not exist.'
RAISERROR (@l_error,16,1) WITH SETERROR
end
else 
begin
truncate table dbo.temp_load_table

  bulk insert dbo.temp_load_table 
from '\\fileserver\folder\filename.txt'
with
(
datafiletype = 'char',
rowterminator = '\n',
TABLOCK
)

  end

  And it errors with "Cannot bulk load because the file "\\fileserver\folder\filename.txt" could not be opened. Operating system error code 5(Access is denied.)."

  These are the items that I've checked:
  1) Is this a UNC path? Yes.
  2) Can I access this file via this path, using the AD account running the MSSQL Service? Yes.
  3) If I move this file to the C:\ of the server, will it process without error? Yes.

  It feels like a permissions issue, but, with 1) and 2) checking out, I am completely puzzled. Any help you could provide would be appreciated.

  Thanks,
  --=Chuck

  BULK INSERT doesn't use the AD account running the MSSQL Service if the user signs into SQL Server using an AD account.  Instead, it uses the privs of the user.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• chuck.forbes

  SSCarpal Tunnel

  Points: 4294

  More actions

  November 6, 2018 at 8:32 am

  #2012548

  When I log in to run the process, I'm logging into the SQL Server instance with my AD account, and I have verified that I have access to the file.

  Something else I wanted to mention, which lends itself to the 'file is locked' theory, is that I could have sworn that I was able to read the file in at least once. I just can't replicate that no matter how many times I try. Even if I delete and recreate that file.

  --=Chuck