November 25, 2009 at 12:38 pm
I keep getting an error when running this script....
BULK INSERT CCHReturnFile --CCHReturnFile
FROM '\\dellserver\Business office\CCHReturnFile.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
The error I'm getting is...
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\dellserver\Business office\CCHReturnFile.txt" could not be opened. Operating system error code 5(Access is denied.).
This script quit working when we upgraded from SQL Server 2000 to 2005 2-3 months ago.
Here's what I've tried from various posts I've read in other forums...
1. The SQL Server services are running with a domain login that is domain admin equivalent.
2. The server can see and has (admin) access to that directory (on another server) that contains the text file. I'm running the script from my desktop and I have access to that file....and no that file is not in use by another process. No other process would be using this file and after each unsuccessful attempt I move that file to another directory and then back again just in case the attempt put some sort of lock on it. Trying to move the file doesn't give me any errors either.
3. Another solution some people found to work that didn't work for me was to give the login on the domain extra access....putting a check mark in the "Account is trusted for delegation".
I'm running this script in SSMS on my desktop manually but would eventually like to create an SSIS package to have it run automatically. I just can't figure out why 2005 can't see that file but 2000 could.
November 25, 2009 at 4:20 pm
You may want to check both the Share and NTFS permissions for the account on the server the remote file is located.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply