| Author |
Topic |
|
RM
Yak Posting Veteran
65 Posts |
 Posted - 2008-07-03 : 05:59:05
|
| Hi,I have created a login using windows authentication in sql server 2005. I have given it sysadmin server role. When I'm trying to run BULK INSERT through this login, it gives me an error:"Msg 4861, Level 16, State 1, Line 1Cannot bulk load because the file "Z:\Folder1\file1.dat" could not be opened. Operating system error code 3(The system cannot find the path specified.)."Using UNC it gives me this error:"Msg 4861, Level 16, State 1, Line 1Cannot bulk load because the file "\\Server1\MainFolder\Folder1\file1.dat" could not be opened. Operating system error code 5(Access is denied.)."BCP is running fine though. BULK INSERT is running fine using the sa login. Could you please help me out here in solving this problem.Thanks,RM |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-03 : 06:08:02
|
| http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1140586&SiteID=17 |
 |
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2008-07-03 : 06:30:57
|
| Thanks for your reply visakh16, but this did not work. Settings for remote connections was already "using TCP/IP only". I also tried changing it to "Using both TCP/IP and named pipes" but it did not work. |
|
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2008-07-03 : 07:27:30
|
| When I'm adding a user in SQL Server 2005 who is a part of windows admin, BULK INSERT works well for that user but not for other users. Is it necessary for a login to be a part of admin for getting BULK INSERT run? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-03 : 08:27:39
|
quote:
Originally posted by RM
When I'm adding a user in SQL Server 2005 who is a part of windows admin, BULK INSERT works well for that user but not for other users. Is it necessary for a login to be a part of admin for getting BULK INSERT run?
Yes sir. You have to have Bulk admin. |
|
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2008-07-03 : 08:32:39
|
| Hi Sodeep, I was talking about the windows admin role. As I said I have already granted sysadmin role to the user but it is not working. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-03 : 08:37:46
|
| But the error shows it can't open the file. |
|
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2008-07-03 : 09:11:40
|
| Yes that is where the problem is ... !! BULK INSERT is working well with sa login as well as anyother sql server authenticated login with sysadmin server role. It is also working with the windows authenticated logins for the windows users with admin level of rights. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-03 : 13:07:24
|
| Logins should have access to that folder as well. It doesn't require sysadmin just to bulk insert. |
|
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2008-07-04 : 02:10:34
|
| The logins have read/write access to that folder. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-04 : 20:12:23
|
| Ensure sql service account has permission to access that file. |
|
|
|
|
Problem in using Bulk Insert