February 11, 2005 at 7:57 am
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?
February 11, 2005 at 12:05 pm
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
February 11, 2005 at 12:19 pm
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.
February 11, 2005 at 12:39 pm
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
February 11, 2005 at 12:42 pm
Ok, thanks, I'll check with my DBA.
February 11, 2005 at 1:00 pm
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?
February 11, 2005 at 3:06 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply