| Author |
Topic |
|
medtech26
Posting Yak Master
169 Posts |
 Posted - 2009-11-03 : 20:29:51
|
Hello,- I have two servers, one runs the application (AKA server I) and the other runs SQL Server (2005, AKA server II).
- Both servers running on the same network.
- The text file for the bulk insert is located on server I.
- When running the bulk insert from the QA (on server II) all works fine.
- When running the same query from the application (server I) I get an error (described below).
- Found the same error here: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118645[/url] with no working solution.
1. I'm pretty sure this is a permission problem, any idea what user should get access? I did try to play with permissions and even gave full access to all users with no success.2. On a different approach, can I run the bulk insert with a string instead of the text file? I'm creating the file just for the bulk insert.Error message:Microsoft OLE DB Provider for SQL Server error '80040e14'Cannot bulk load because the file "\\server I\file.txt" could not be opened. Operating system error code 5(Access is denied.). |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-04 : 04:37:25
|
| Sorry for failing to mention this but I did share the folder on server I, otherwise it probably would not execute the query successfully from QA. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-04 : 13:22:27
|
I did  quote:
Originally posted by medtech26Error message:Microsoft OLE DB Provider for SQL Server error '80040e14'Cannot bulk load because the file "\\server I\file.txt" could not be opened. Operating system error code 5(Access is denied.).
I'll try to play with the permissions again ... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-04 : 13:33:40
|
| It sounds like a double-hop issue. I *think* you need to grant access to the account that the SQL Service runs under. |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-04 : 14:34:30
|
| I can't seems to get along with the permissions so I'll focus on my other question: Is there any way to run the bulk insert with a string instead of text file? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-04 : 14:56:24
|
quote:
Originally posted by medtech26
I did quote:
Originally posted by medtech26Error message:Microsoft OLE DB Provider for SQL Server error '80040e14'Cannot bulk load because the file "\\server I\file.txt" could not be opened. Operating system error code 5(Access is denied.).
I'll try to play with the permissions again ...
\\server I\file.txt is not a valid path. It needs to be \\serverName\shareName\fileName. You can have directories in between shareName and fileName though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-04 : 15:11:03
|
quote:
Originally posted by tkizer\\server I\file.txt is not a valid path. It needs to be \\serverName\shareName\fileName. You can have directories in between shareName and fileName though.
Changed it according to the sample (server I etc.), it's more like the format you posted. Again, the exact same query works fine if I run it from the QA (so path, file, permission for SQL, whatever ... seems to be okay), but when running from the application I get the above mentioned error. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
medtech26
Posting Yak Master
169 Posts |
Posted - 2009-11-04 : 16:08:51
|
| I use Windows Authentication to log-in the Management Studio (and run the query from the QA), but the app. uses a different user. |
|
|
|
|
Remote Bulk Insert