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' )
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)
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
You need to use UNC path like ‘\\servername\foldername\filename’
Is there any solution for this ? I am having the same issue as described by James.
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.
Use UNC path like ‘\\servername\foldername\filename’