Link to home
Create AccountLog in
Avatar of Kramarich
KramarichFlag for United States of America

asked on

BULK INSERT failure

We are trying to use the following code to do a bulk insert into a SQL server table:

cmd.CommandText = "BULK INSERT [TableName] FROM '\\server\path\filename.txt' WITH (DATAFILETYPE = 'Char', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n')"
       intResults = cmd.ExecuteNonQuery()

Whenever this command is executed, we get the following error:

Cannot bulk load because the file "\\server\path\filename.txt" could not be opened. Operating system error code 3 (The system cannot find the path specified.)

The only way we can get the Bulk insert command to work is to copy the filename.txt file to the server where the SQL Server instance is installed.

We would like to avoid having to copy the text files to the server every time we do a bulk load.  Is there any other solution that would prevent the above error?
Avatar of Rick
Rick

Your command text looks fine to me.
Check the file path.
Also, make sure you have permission to open the file on that server.

Right-click on the file and on Security tab, add appropriate permission.
Avatar of Kramarich

ASKER

The path is correct and we can open the file using wordpad so the permissions must be correct.  Is there some other obscure account permission that we need to set?
What account are you using?

You can either use domain account or create an account.

This of course if you are using local service account because if you are pointing to a remote server, or try using SQL Server Service Account.

I would also, just to be certain it isn't permission like you said, try the BULK INSERT by pointing to a local server.

if it works there, which I think it will, then it is permission issue.
When you ask what account are you using, are you referring the the account used on the SQL Server services?  If so, we're using the Local System account.

As for testing the BULK INSERT by pointing to a local server, I stated that it does work if we copy the text file to the server with the SQL Server instance.  That's another reason why I don't think it's a file permission problem.
If the same file works locally but doesn't work on a remote server, it is a permission issue.

Be sure that the sql server service account has access to server.

I can see you are usning UNC path and that's good.

Also try using Domain account or Sql server Service account and give it permissiom to the file.

If your file is on the remote server, I don't *think* you can access it using local account.
Sammy,

We've done more research online and the "solutions" are too complicated (named pipes vs TCP/IP only, using domain accounts vs sa account, delegation, etc), not to mention the security implications of some of these solutions.

For now we're going to stick with copying the files to the SQL Server Windows Temp folder (which we shared out) and doing the Bulk Insert using the copied file.

Thanks for your help,
Ken
ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Sammy.  There's just too much information to digest, not to mention coordinating with our IT security staff and LAN Support staff to make any changes.  IT Security follows NIST guidelines, whether they prevent us from getting our work done or not.  I'm tired of fighting with them so if there's a workaround I'm taking it.  Sad.
I've selected this as the best solution but I don't know if it would solve the problem or not.  Need another way to close a question without having it deleted.
Hi Kramarich,

Too bad you aren't fully satisfied with solutions provided.

Please, please next time, just ask for the question to closed and points refunded to you.

if you go through solutions I have provided, even when I feel that 100% solution has been provided for a question, I usually don't object to a poster having his or her points refunded to him/her.

Reason, for me personally, is I truly don't do this for points.

I do it to assist the user get the best solution for his or her questions and I do it until the poster is fully satisfied.

In the future, you can also ask for additional assistance. I will do my best to provide it.
Thanks Sammy.  I don't mind you getting the points.  People like you who are willing to help others should get some kind of recognition, whether their answer was right, partially right, or even wrong.  The fact that you hung in there with us says it all.

Thanks again,
Ken