BCP & file access error



I try to do a BCP of a text file into a table in a SQL database using:



BCP MyDb..MyTable in N:MyDataFile.txt -fC:BCP.fmt -SMyServer -T



where 'N:' is a network directory



When I run this command on the DOS prompt in the SQL server 'MyServer', bcp works OK. But when I run this command USING xp_cmdshell in a Query Analyser i.e.



DECLARE @CMD_RET_VAL integer

EXEC @CMD_RET_VAL = master..xp_cmdshell 'BCP MyDb..MyTable in N:MyDataFile.txt -fC:BCP.fmt -SMyServer -T'



It results in the following file access error (I presume this is what it is):



SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file.



If I move MyDataFile.txt from N: to C: and change the BCP command line accordingly to:



DECLARE @CMD_RET_VAL integer

EXEC @CMD_RET_VAL = master..xp_cmdshell 'BCP MyDb..MyTable in C:MyFile.txt -fC:BCP.fmt -SMyServer -T'



Everything is working fine again.



This becomes clear that when I run the BCP WITHOUT xp_cmdshell, it has no network file access error. If I run it WITHIN xp_cmdshell, it gives me this error. The question is WHY???!!! And how could I work around this?



As I must run this BCP in a stored procedure, I need to run it INSIDE a command shell. Is there an alternative?



The SQL Server in question is version 7.0 Standard Edition under Windows NT4.0 Standard Edition.



In addition to trying BCP, I also tried using BULK INSERT, I got Operating System error 3 (which is a 'file specified not found' error)



I could do a BULK INSERT if the data file is on the hard drive of MyServer. If the data file resides on the network, it doesn't work even though I have network access to the file. I could see and open that file with Windows Explorer.



All advice appreciated.



Regards,



Francis




Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories