SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Bulk Insert Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 12/14/2012 :  13:54:19  Show Profile  Reply with Quote
Hi I am trying to execute .tbl file to bulk insert in the server ServerA. The .tbl file was saved in Server ServerB. So I used the query and path as follows.

I tried in two different ways.

BULK INSERT tablename
FROM '\\servernameB\F$\Upload Data\David\test.tbl'
WITH
(
--DATAFILETYPE = 'widechar',
FIELDTERMINATOR = '^',
ROWTERMINATOR = '\n'
)

BULK INSERT tablename
FROM '\\servernameB\Upload Data\David\test.tbl'
WITH
(
--DATAFILETYPE = 'widechar',
FIELDTERMINATOR = '^',
ROWTERMINATOR = '\n'
)

I gave the full control/permissions to the folder in serverB but still I am getting the below error.

Cannot bulk load because the file \\servernameB\Upload Data\David\test.tbl could not be opened. Operating system error code 3(The system cannot find the path specified.).


Please Please help me in this. We using serverA first time and getting this issue.Also I got stuck with this from past 3 days and not able to do any stuff.

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/14/2012 :  14:32:47  Show Profile  Reply with Quote
read this

http://blogs.msdn.com/b/jay_akhawri/archive/2009/02/16/resolving-operating-system-error-code-5-with-bulk-insert-a-different-perspective.aspx
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/14/2012 :  14:48:55  Show Profile  Reply with Quote
I hadn't seen sodeep's post when I was typing it - but here it is anyway.

Are you using SQL Authentication or Windows Authentication?

If you are using SQL authentication, permissions of the account under which SQL Server process runs will be used to access the network share. So make sure that that account has access to the network share.

If you are using Windows Authentication, the security profile of the user is used ONLY for files on the local computer. Even if the user has permissions on a remote share, it would not use the security profile of the user. To make it happen, you will need to do a Security Delegation/Impersonation. You need to do this from Active Directory. This blog and this blog might help.

I have never done what they described in those articles using Windows Authentication. Even on servers where all the logins are Windows Authenticated logins, I have created a special SQL authenticated login just for this purpose. (I know, I know - laziness, short-cut etc. etc., but that was easier for me - SQL Sever was under my control, AD was not. So it was easier to convince myself than convince the network admin Gods.)
Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 12/14/2012 :  15:23:22  Show Profile  Reply with Quote
I tried in Sql server authentocation mode still getting the same error.

Can you tell me the solution
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/15/2012 :  12:40:50  Show Profile  Reply with Quote
how are you running this query? from your own login or from an automated job or process?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

aswindba1
Yak Posting Veteran

USA
62 Posts

Posted - 12/17/2012 :  12:25:36  Show Profile  Reply with Quote
Hi iam running on my sql server accoount login. Can you help me??
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  09:13:54  Show Profile  Reply with Quote
First, see if you can successfully do a bulk insert from the local machine. That would eliminate the possibility that the problem is caused by something other than the impersonation issues that I had described in my earlier post.

If that works successfully, assuming you are using SQL Authentication, do Start->Run->Services.msc and look at the properties of the SQL Server process. The logon tab should tell you the user the service is running under. If it is a specific account, make sure that that account has access to the remote fileshare. If it is Network Service, speak to your network administrator to see what needs to be done for the network service account to access the fileshare.
Go to Top of Page

ovc
Starting Member

Romania
35 Posts

Posted - 01/02/2013 :  17:19:53  Show Profile  Reply with Quote
guys, that is Operating system error code 3(The system cannot find the path specified.) and not operating system error 5 (ACCESS DENIED). These are 2 different things.
The right approach would be to:
1. try to open from server A in windows explorer the path \\servernameB\Upload Data\David\test.tbl
2. eventually test with a path which does not contain white spaces
3. test \\servernameB.domain.com\Upload Data\David\test.tbl
4. test \\ip\Upload Data\David\test.tbl

There is an issue with the path rather than with permissions.


For access denied (OS ERROR 5) for bulk inserts (it is not he case in this situation) the requirements are the following:
1. The SPNs should be set correctly for the sql server instance
2. the sql server service account should have permissions to delegate the credentials in a double hop environment (clientserver1->serverA->serverB)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000