June 19, 2004 at 3:08 am
hi
bulk insert statement fails when used with unc paths for the format file and the delimited text file. the statement is as follows
BULK INSERT ITEM_TEMP FROM
'\\BISMILLAH\bulk\item.txt'
WITH
(
FORMATFILE='\\BISMILLAH\bulk\format.txt'
)
both the format file and delimited files are in BISMILLAH
database is on another machine (BISMILLAHSERVER)
'bulk' directory is shared and full control for everyone
i can access (using windows explorer or start->run)the '\\BISMILLAH\bulk\format.txt' file from all other machines on the network.
when i access the file using windows explorer or start->run, windows 2000 audting on BISMILLAH shows success.
but when when i run the script auditing is not showing either success or failure. i think that means sqlserver is not even requesting for the file.
the error message i got is like follows:
Server: Msg 4861, Level 16, State 1, Line 41
Could not bulk insert because file '\\BISMILLAH\bulk\format.txt' could not be opened.
Operating system error code 5(Access is denied.).
even when i run the script with a non existing file on BISMILLAH the same error message comes.
think u got an idea about the situation
any ideas will be appreciated.
renjith
June 19, 2004 at 8:23 am
BCP is an old utility. I'm not surrprised it doeesn't work. Can you not map a drive to this locaiton.?
June 19, 2004 at 11:44 am
thanks for the reply
actually the bulk insert statement is part of a stored procedure (called from an asp.net application), which takes both the format filename and delimited filename as input parameters. inside the proc i create a dynamic tsql statement and execute it with the sp_executesql statement. both the web server and database server are internet servers. i don't have much rights on those machines. i did auditing and all those things on the development server.
one more thing when both the database and the files are on the same machine, even if i use unc path (pointing to the same machine), it works.
please help.
June 20, 2004 at 12:41 am
i tried drive mapping (full control to everyone) on my local network. but that too failed with the same error message.
June 22, 2004 at 9:12 am
Is the MSSQLServer service running under a domain account that has access to this share?
SQL Server will access the filesystem and network shares in the security context of this user. If you are using localsystem, then you can't access remote shares.
JM
June 22, 2004 at 9:35 am
thankx jmaurais
both the webserver and database server are internet servers. i think both the machines are independent ones and not under a domain. is there any way to authenticate the request for the remote files.
renjith
June 22, 2004 at 9:47 am
In the case of independent servers, as long as the username/password combination used by SQL Server matches a username/password combination on the other machine, and that second login has the appropriate access to the share, that should work.
In other words, as long as you have logins that match, then it should authenticate.
JM
June 22, 2004 at 8:32 pm
thanx JM
i solved the problem by changing the user under which the services (SQL Server and SQL Server Agent) are running, and gave that user read access to the remote share.
regards
renjith
April 21, 2005 at 3:20 am
Hello,
I find the same problem.
Both the database and the files are on the same machine.
The path is like 'D:/datas/myprog/myimportfiles/myfile.txt'
The user connecting to SQL Server has total control on the folder and the files in.
What can I do ?
Regards
Laure
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply