Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone help me with these?

BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
    FIELDTERMINATOR = ' ',
    rowterminator = '\n',
    tablock
)

The error shows, couldn't open:

Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.)

The path is on another computer on the network.

share|improve this question
6  
Does the account that SQL Server is running under have permission to access '\\COMPUTER01\FOLDER01\TextFile.txt'? Note that the account will generally be different from your account. –  Damien_The_Unbeliever Jun 13 '13 at 8:55
3  
Can you also add the exact error you get? –  ypercube Jun 14 '13 at 11:22
    
in addition to @Damien_The_Unbeliever: "To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename" –  user16484 Jun 14 '13 at 11:52
    
@ypercube - Could not bulk insert because file '\\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.). thank you sir –  Imperial Jonas Jun 15 '13 at 2:43
add comment

migrated from stackoverflow.com Jun 14 '13 at 11:20

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

I'm going to make a leap of faith and assume you connect to SQL Server using Windows Authentication. In such a case you have a Kerberos double hop: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote \\COMPUTER01. Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To fix the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to 'enable unconstrained delegation' is just a typo as unconstrained delegation is just plain evil.

In the case I jumped the gun and you are logging into SQL Server using SQL Authentication you will need to create a credential for your SQL login and use that to access network resources. See CREATE CREDENTIAL.

share|improve this answer
add comment

Sql Server tried to open file, file server told it "Access denied" because the account SQL Server runs under has no permission to open the file. Change security settings on the file accordingly and it will work.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.