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 ( http://blog.sqlauthority.com )