May 14, 2009 at 3:19 pm
What I'm trying to do is bulk upload from a barcode scanner (.txt comma separated) into SQL. Not a problem when the file is local to the SQL Sever, however it's through an ASP.net app running on a different server. I got the Sql Query working without a hitch however.......
It only works when the file is local to SQL.
I can't get ASP.net to upload to it as it requires granting another computers built in account write permissions on the SQL server(???)
I can't get it to use the remote file!
The file I'm using is on \\APPSVR\AppDir$\Admin\ScanData.txt
I've read about granting sqlservice permissions but it hasn't worked so far! The reason for this being in the newbies section though is I could really do with the "Big letters and colourful pictures" version. I just can't understand in a plain english way some of the solutions that people have said worked for them.
Many thanks
Tel
May 14, 2009 at 4:34 pm
An OS error 5 is "access denied".
When you run a bulk insert statement you're running it in the context of the service account under which SQL Server is running. If your SQL Server service is using the default local system account (naughty, naughty) then it's very difficult to give it access to resources on different servers.
The best practices recommend to run the SQL Server services under a domain account. If that is the case then you can give that service account whatever rights it needs to the files and directories on other servers. In this case it sounds like it only needs read rights to the directory where the file resides (giving it rights only to the file might be dangerous because if the file is dropped and recreated then the service account will lose rights to the file when it is dropped and the new file will have the rights on the directory).
May 14, 2009 at 11:38 pm
Ahhhhh right! That makes a whole load more sense.
I shall look into it.
Cheers!
May 15, 2009 at 8:04 am
I've tried giving the Sql Service account rights on the remote folder (yep, we use a domain account) which failed. So I made a new share so it was at top level, not share\subdir\file which also failed.
The error is subtly different though from access denied....
Cannot bulk load because the file "\\APPSVR\AppDir$\Admin\ScanData.txt" could not be opened. Operating system error code 5 (error not found)
The file is definately there, and the sql service is definately a domain account.... I honestly don't know what I'm doing wrong! :crying:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply