Hi,

I am trying to run this bcp from a stored procedure and it wudn't work.
It works fine on command prompt

exec master..xp_cmdshell 'bcp Lisa_Test.dbo.Notfoundtable out C:\Lisa_Test\TelephoneNoNotFound.csv -S office1 -T -o C:\Lisa_Test\NotFoundoutput -e C:\Lisa_Test\NotFounderrfile -t , -c'

It shows this error.

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

Is there any server setting to be changed for this ??

Any help please!

Cheers!!

Anyways, don't matter. I figured out.

wht was it???

Please let me know the solution you figured out!

Thanks A Lot!

Regards
Anubhav

one of two things likely going on.

1) the userid that the sql server is running on does not have rights to the file your looking for. Remember that when you connect to the SQL server you use your sql or domain account depending on how you connect. But when you use XPcommand shell you use the domain user that the SQL service is running under. Try logging into the domain on the SQL server box under the id that the SQL server is running under and see if you can see the file.

2) you specify c: But which C: are you looking for. Typically it is safer to specify full paths like \\someserver\someshare\myfile.txt. When you specify c: you need to realize it is not the c: of the client computer, it is the C: of the server, accessed by the user id that the SQL engine is running under.

Hope this helps what you described is a common problem.

Please post if this fixes your problem and if so which issue, 1 or 2.

Also as a troubleshooting step try this..

exec master..xp_cmdshell 'dir C:\Lisa_Test\*.*'

This will tell you what the BCP program can see under the user id that your running under. Make sure you can see the file you want to BCP.

You may be surprised in what you get back.