SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BULK INSERT failing.


BULK INSERT failing.

Author
Message
Jamie Ashton
Jamie Ashton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 147
Hello,

I have a situation where I am trying to do a bulk insert of a file that's on a network share. It's failing with:

Cannot bulk load because the file "\\Server\Folder\File" could not be opened. Operating system error code 5 (failed to retrieve text for this error. Reason: 15105).

What I think is odd though is that use of xp_FileExist confirms that that the file exists and the SQL Server can see it.

The remote folder has permissions for "Everyone". Is there an internal setting in SQL Server that has been set to prevent me doing this? It's a new server and so this has never been done before. But it worked fine in pre-production, on a different server.

Hope you can help.

J
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3880 Visits: 8472
Jamie Ashton (2/3/2012)
Hello,

I have a situation where I am trying to do a bulk insert of a file that's on a network share. It's failing with:

Cannot bulk load because the file "\\Server\Folder\File" could not be opened. Operating system error code 5 (failed to retrieve text for this error. Reason: 15105).

What I think is odd though is that use of xp_FileExist confirms that that the file exists and the SQL Server can see it.

The remote folder has permissions for "Everyone". Is there an internal setting in SQL Server that has been set to prevent me doing this? It's a new server and so this has never been done before. But it worked fine in pre-production, on a different server.

Hope you can help.

J


Error code 5 is permissions, so it might be worth checking that whatever account your SQL Server runs under has permission. Check the account used by the pre-production server and see if it's using the same.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
derek.colley
derek.colley
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 603
Hi Jamie

I faced a similar situation not too long ago trying to do the exact same thing. I don't have a verifiable link but from experience, I've found SQL Server in general and SSIS packages in particular DO NOT get on with network shares at all.

You'll need to put the input file on a local drive for this to work. Don't forget about your BULK INSERT options, too - make sure they're set for the type of file you're using.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Jamie Ashton
Jamie Ashton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 147
Thanks both for your speedy answers!

Firstly the permissions... surely "Everyone" would encompass the SQL Server account?

And yes - I'd love to have the file locally - but it's impossible in this situation.

Don't you think it's odd that XP_FileExist can see it yet BULK INSERT fails?

Or are separate accounts involved?

J
derek.colley
derek.colley
SSC Eights!
SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)SSC Eights! (868 reputation)

Group: General Forum Members
Points: 868 Visits: 603
I think it's got to be something to do with accounts. Maybe grant the SQL Server accounts explicit permissions on the network share. In terms of the XP call working, I suspect that internally it's using something like the Windows COM objects or some other Windows API which is returning a result (as it would do if you e.g. browsed a normal window).

A quick bit of Googling has found this which might be of help:


1) The SQL Server services, on the Server containing the instance of SQL Server, must be running under a domain-level account (e.g. A Domain Administrator account). This is accomplished by changing the "Log On" properties for the services named "MSSQLSERVER" and "SQLSERVERAGENT" on the server running SQL Server (not your local instance). When you have completed changing the log on information for these 2 services, you will need to restart the "MSSQLSERVER" service on that server. Note that this will ask if you want to restart the "SQLSERVERAGENT" as well - Answer: Yes.

2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share. That means you need to have a shared location in which the logon account you specified in 1 (above) has full control rights.

3) The file share should only be accessed via UNC name. Mapped drives may not be consistently visible to the SQL Service.

4) You cannot specify the path by using the browse ellipses (...). You must type the fully qualified path into the text box as:

file://ComputerName/ShareDirectoryWithRightsForDomain-levelAccountUsed/NameofFile.Extension



Additional Notes:
When creating or restoring backups on a network share, a significant increase in network traffic may result. You should ensure that your network can handle this without causing poor performance for other systems or end users. It may also benefit you to establish a dedicated physical network connection between your database server and your backup file host. Once the SQL Server instance is configured, you can use your local Enterprise Manager to create or restore a database backup that resides on a network device other than the device your instance of SQL Server is running on.



(from http://www.fmsinc.com/free/NewTips/SQL/SQLtip9.asp)

I know this relates to SQL Server 2000 but I suspect it's right.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Jamie Ashton
Jamie Ashton
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 147
Thanks all for your help on this one.

My research indicates numerous possible causes, but in this instance, it looks to be a permissions issue going across domains.

J
timexist
timexist
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 60
I just solved this issue.

two ways,

1, remote desktop to sql server do bulk insert there.

2, or you can use sql authentication to login.

have fun
FortyEightK
FortyEightK
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 437
Bump!

Just thought I'd add my two-penneth as I was getting this exact error today and this was the first post I found on a search.

The BULK INSERT is performed from a stored procedure, specifically looking for a file in a local path. My problem wasn't permissions, some f**kwit decided it was a good idea to rename the folder!
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
FortyEightK (5/23/2013)
Bump!

Just thought I'd add my two-penneth as I was getting this exact error today and this was the first post I found on a search.

The BULK INSERT is performed from a stored procedure, specifically looking for a file in a local path. My problem wasn't permissions, some f**kwit decided it was a good idea to rename the folder!


ROTFL! Sounds like the solution wasn't that technical after all! :-D

I don't understand why XP_FILELIST didn't pick up on the change though....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search