Bulk Insert Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Bulk Insert Error

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-12-14 : 13:54:19

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-14 : 14:32:47

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 14:48:55

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.)

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-12-14 : 15:23:22

I tried in Sql server authentocation mode still getting the same error.

Can you tell me the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-15 : 12:40:50

how are you running this query? from your own login or from an automated job or process?

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

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2012-12-17 : 12:25:36

Hi iam running on my sql server accoount login. Can you help me??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-18 : 09:13:54

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.

ovc
Starting Member

35 Posts

Posted - 2013-01-02 : 17:19:53

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources