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

In this blog post, we are going to discuss how to solve the following error “Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)”. Below you will find the reason why this error happened and the way to solve it.

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

From the very beginning, it is necessary to mention that this error message can appear as a false and misleading error. This error message as a false error can appear when you using the T-SQL command “BULK INSERT” to import data from the text file to SQL Server table. Let’s reproduce Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.) error message using the next script to create a sample data:

Create Database Adventureworks
GO
USE Adventureworks
GO
CREATE TABLE [dbo].[MyData](
 [Id] [int] NOT NULL,
 [Name] [char](200) NULL,
 [ModDate] [datetime] NULL
)
GO
INSERT INTO MyData VALUES (1, 'Data.1', GETDATE())
GO
INSERT INTO MyData VALUES (2, 'Data.2', GETDATE())
GO
INSERT INTO MyData VALUES (3, 'Data.3', GETDATE())
GO

That was the first part of our test script. It’s time to export this data to the text file. We can do it using bcp (bulk copy program utility) command. And then, we will import the data back from the table. Use the following bcp.exe command to export the data.

bcp.exe Adventureworks..MyData out “c:\Temp.txt” -c -T -S.SQL2014

After we have executed this command let’s insert the data back into the table using the next script:

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

After you will receive the following error message:

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.).

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

Solution

The reason this error occurred was that we specified “C:\Temp” without “.txt” extension.  It was the false and misleading error. So to all you need to do to solve it just specify the correct name “C:\Temp.txt”.

But what to do if you specify the correct name and still receive this error? The reason that you receive this error message is that you are using SQL Server Authentication and SQL server hasn’t accessed the bulk load folder. All you need to do is to give SQL Server access to the folder.

Here is how you can grant all necessary permissions. Go to the folder and right click, select “Properties” and open “Security” tab. Click “Edit” then “Add” and press “Advanced” button on the new window. After click “Find Now”. The last step you need to do is to find something like SQLServerMSSQLUser$UserName$SQLExpress and click ok, to all the dialogs opened.

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

Press “OK” tp apply all new permission settings.

That is all. That was two the most popular cases how to solve this issue and the reasons why it happens.

[Total: 10    Average: 4.3/5]

One thought on “Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)

  1. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *