Error With BCP

Question

Error With BCP

Matthew Silverthorn

Mr or Mrs. 500

Points: 509
More actions
February 11, 2005 at 7:57 am

#65442

Hi all,
I'm having a problem getting BCP to work correcty. I'm calling it from a stored procedure like so:
EXEC master..xp_cmdshell @bcpCommand
where @bcpCommand is set to:
set @bcpCommand = 'bcp "SELECT * FROM (database name)..(table name)" queryout "C:\test\test.txt" -U (user name) -P (password) -c -t , -r \n'
All items in parentheses have valid values, but for some reason I am getting the following error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
I have SET NOCOUNT ON at the top of the procedure. Can anyone offer any further insight?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

I have had similar problems in the past with two different causes.

In one case, the acocunt I was running as did not have priveleges on the target drive. Check with your sysadmin to see if this is the issue.

In the second case, I could see what I thought was a directory one level down from the root of the drive. However, the directory was actually shared from deeper in the directory tree. To solve this one, I had to use the UNC path rather than the physical path.

No guarantee that either of these is the problem you are seeing.

Good luck

Wayne

Matthew Silverthorn Mr or Mrs. 500 Points: 509 More actions · Answer 2

With your permissions problem, are you referring to the ability to create files on the drive, ability to access tables, or something entirely different? Thanks for your suggestions.

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 3

re: the permissions problem: The environments I work in tend to be fairly well locked down (not my doing - just good network folks in the ops team). As an example, one of the sql server userids can see and access the backup directory, but no other directories on that particular server. A different sql server userid is used by the applications for running stored procedures but cannot see the backup directory. My personal userid has no access to the server at all.

Wayne

Matthew Silverthorn Mr or Mrs. 500 Points: 509 More actions · Answer 4

Matthew Silverthorn

Mr or Mrs. 500

Points: 509

February 11, 2005 at 12:42 pm

#541880

Ok, thanks, I'll check with my DBA.

Matthew Silverthorn Mr or Mrs. 500 Points: 509 More actions · Answer 5

Ok, here is the weird thing. This statement works:

SET @bcpCommand = 'bcp "SELECT * FROM (database)..(table)" queryout "E:\CMS\color.txt" -U (user) -P (pw) -c'

This statement doesn't:

SET @bcpCommand = 'bcp "SELECT * FROM (database)..(different table)" queryout "E:\CMS\color.txt" -U (user) -P (pw) -c'

Any ideas as to why that would be? Would the content of the data table make a difference here?

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 6

The content of the table should not make a difference. However, the owner of the table certainly could. Check the spelling of the table name that fails (seems to obvious, I know), and check the owner of the table that fails. for example, one table may be owned by dbo and the other table may be owned by your userid.

Wayne