SQL SERVER – False Error – Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)

Yes, it’s possible that SQL Server can raise false and misleading error. I was trying to do some pretty straight forward stuff – import data from text file to SQL table using the BULK INSERT command of T-SQL. Let us learn about false errors from Operating system error code 5 (Access is denied.)

Here is the script to create some sample data.

Create Database ErrorDB
GO
USE ErrorDB
GO
CREATE TABLE [dbo].[SQLAuthotity](
	[Id] [int] NOT NULL,
	[Name] [char](200) NULL,
	[ModDate] [datetime] NULL
)
GO
INSERT INTO SQLAuthotity VALUES (1, 'MR. DAVE', GETDATE())
GO
INSERT INTO SQLAuthotity VALUES (2, 'MISS. DAVE', GETDATE())
GO
INSERT INTO SQLAuthotity VALUES (3, 'MRS. DAVE', GETDATE())
GO

Now, we can export this data to a text file using bcp command. Then, we would import the data back from the table. To export the data, we will use below bcp.exe command

bcp.exe ErrorDB..SQLAuthotity out “c:\Temp.txt” -c -T -S.\SQL2014

Once it was completed, I wanted to insert the data back into the table. So I ran this command

USE ErrorDB
GO
BULK INSERT SQLAuthotity
FROM 'C:\Temp'
WITH
(
  KEEPNULLS,
  FIRSTROW=2,
  FIELDTERMINATOR ='\t',
  ROWTERMINATOR ='\n'
)

SQL SERVER - False Error - Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.) bcp-err-01-800x257

To my surprise, it failed with below error

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “C:\Temp” could not be opened. Operating system error code 5(Access is denied.).

I have tried all the combination of permission (service account, local admin, everyone etc.) but this error was not getting away.

I was unable to fix it so I gave up for the day and decided to start fresh. I ran the command and it was working. Do you know how?

Solution / Workaround

In this special case, if you look at the command which was not working I have given “C:\Temp” instead of “C:\Temp.txt”. Since I had a folder by name Temp in C drive, it was failing with an error, which was not true error. Yes, access denied error was a fake error in this case. It should have told me that the name is not a file, but it’s a directory.

Have you seen similar incorrect or unhelpful error in SQL Server?

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
Database Sharding – How to Identify a Shard Key?
Next Post
SQL SERVER – Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error – (400) Bad Request

Related Posts

5 Comments. Leave new

  • I have almost the same problem, I have 2 servers, the Server SQL and the Server File. I execute the script in SQL Server Management Studio from my laptop with Windows authentication, but I got the error 4861: Cannot bulk load because the file… but, If I execute the script in the server SQL it works. I can access to the server file putting the path in the explorer in my lap and the server sql, just I can’t execute it in my laptop

    Any idea?

    Reply
  • Is there any solution for this ? I am having the same issue as described by James.

    Reply
  • Paul John Emmanuel
    March 19, 2020 9:31 am

    I was able to fix it by creating a new SQL Server account and grant bulk admin, dbcreator and public access. Make sure the newly created sql account has access/mapping to the DB. if not easy way is to make the account sysadmin.

    Then connect to the sql instance using the sql account and run the script. Bulk inserts go through successfully.

    Reply
  • Use UNC path like ‘\\servername\foldername\filename’

    Reply

Leave a Reply

Menu