December 13, 2005 at 2:50 pm
I am trying to run a bcp command using xp_cmdshell in a stored procedure. I have setup the proxy account and it works and all the setup looks OK. Whenever I run it in the procedure, I get the following error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL
I checked for typos, permissions, and everything else. Finally, I put a print in my procedure, copied the exact code from the print statement, then ran the bcp command from the command line and it ran fine.
Anybody got any ideas other than permissions?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
December 13, 2005 at 3:24 pm
Login as the proxy user and try to run it from the cmdline, bet you find a permission error...
Your friendly High-Tech Janitor... 🙂
December 13, 2005 at 3:26 pm
When I ran the command from the command line, I was logged in as the proxy user. (I had to log out and back in just to make sure.)
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
December 14, 2005 at 6:47 am
When you say you ran it manually does that mean you ran it manually from your box or you ran it manually from the SQL Server box? I strongly suspect that the issue is that the SQL Server box cannot see the file due to permissions.
Remember: If it looks like a duck and it quacks like a duck, it probably is a duck. (feel free to substitute "permissions problem" for "duck")
December 14, 2005 at 7:07 am
First, I was logged into the server as the proxy user that I have setup to run as when a non-sysadmin runs xp_cmdshell. I ran it from a command line there and it worked.
Second, an update: I had been trying to run my procedure in QA as a set of statements but not the whole thing as an actual stored procedure.
Just to get something tested, I went ahead and ran the actual stored procedure, with the BCP in it, using the EXEC command, and it worked. I didn't make any changes, it just worked.
Right now I am under the gun to get this whole thing finished so I don't have time to figure out exactly what is happening but if I ever do figure it out, I'll post my findings in this thread.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
December 14, 2005 at 6:01 pm
This is what I will do, very basic testing:
(all from xp_cmdshell)
-Check if you can "dir" the directory
-Creating a file like "dir > path/test.txt"
-do a bcpout and then bcp in
Very basic but it will give you an idea of what can be happening, if you can do all this then you should do the bcp!!
December 16, 2005 at 10:10 am
You may want to include full drive/directory or UNC naming on all files in your scripts that are executed via xp_cmdshell. I believe the 'default' directory for xp_cmdshell execution is: C:\WINDOWS\system32.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply