Cannot do a simple BULK INSERT!
-
Hi All,
First post, so apologies if this is in the wrong area.
The team I work for recently got a VM Server with SQL Server 2005 on it, we have admin rights to this machine.
I've some T-SQL that runs fine when run on the server, it's a simple BULK INSERT of a tab delimited file, works great! (5+mins upload time in Access, ~5secs in SQL!)
The SQL Server is running under a domain account, lets call it A, during the various things I've tried (can't quite remember everything) I've made this account a member of the admins group on the Server.
I've also made sure this account has access to the text file on the server, to confirm this I've used xp_cmdshell to perform a dir on the file path.
Then on a local PC, under account B (again another domain account, with sysadmin rights on SQL Server) I run exactly the same code and get this message.
Msg 4861, Level 16, State 1, Procedure sp_Test_Bulk, Line 9Cannot bulk load because the file "<server path to file>" could not be opened. Operating system error code 5(error not found).
I've done soooo much searching the last couple of days, I've muddled my brain up.
One suggestion was to create a new SQL Server account, and connect using that, I've tried that and not even that works (though this solution fixed an issue using ACE 12.0, but that's another problem to solve another day!).
I really hope someone can help me, I would be very grateful for any help provided.
Many thanks,
Neil
-
I don't know off hand which account SQL will use to access the file, probably the SQL service account, but it may not hurt to make sure account B has access to file.
Are you specifying a full path to the file or a relative path? Can you provide the command that's causing a problem?
-
OS error code 5 means access denied. This is a permissions error.
Make sure the account you are using has access to the directory and to the file itself
---------------------------------------------------------------------
-
also, there's two places to look;
I believe if you have set up a proxy account, if you are sysadmin, it will use that account no matter what, and that account might not be the same as the one that runs the service.
the account in services:
so you might need to make sure the proxy account has permissions to teh file share you are trying to BCP into, even though the account running the service might have permissions .
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi,
Many thanks for the swift replies, and many apologies for the slow reply. I got pulled away from this issue and had to find a quick work around at the time. Now I need to go back and make sure I can get this working "properly".
My work around, which is horrible, is to use xp_cmdshell to copy the source file locally to the server, run the bulk insert, then delete the local copy!
This is highly annoying, as the server can clearly "see" the file to be able to copy and paste it. It can do a bulk insert from a local directory.
I'm now also trying to use OPENROWSET to connect to an ACCDB database on the network, and am having the same issue.
SQL Server is running under a domain account not a local account and that account has all the same privelages as my personal domain account. We are a member of the same group.
I've also, for testing purposes, given the file and folder the database is located in "Full Control" to "Everyone" and still it refuses to work!
It's infuriating that something so simple is causing so many issues.
I'd like to try the solution posted above by Lowell, but that will presumably require a restart of SQL server, which is awakward to do during the working week, and I'm not sure it would help having given "Everyone" Full Control and have it still fail.
Does anyone have any further suggestions?
I'm hugely grateful for any help.
[Edit]
As an example, running the following in SSMS on my machine, fails, running the exact some thing from SSMS on the server itself, works!
SELECT *FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'\\Network Fil Path\db.accdb; 'admin';'',tbl_Data)
-
How funny, I randomly run into this thread and I had the same exact problem last Friday. Running the BULK INSERT command that pointed to a file on the local server worked, as soon as I pointed to a remote network share it failed with the same error message.
This is what I found. Check out the link below, about halfway down the page under the "Security Considerations" section.
http://msdn.microsoft.com/en-us/library/ms175915(v=SQL.90).aspx
You may want to try this link as well. Let me know if you find a solution that works!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply