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.
= 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:
= 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.