How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
(OP)
Hello all,
It's been a while.
I am trying to export a table using the bcp utility. Below is the command I am running in SSMS:
which returns the following error that looks like this
Checking the environment variables, I found that the path to the Sybase bcp.exe executable was recorded but SQL Server's one is not. So I went ahead and added the path
However, the xp_cmdshell execution is still failing. Adding the full path to bcp.exe in the xp_cmdshell statement complains about it.
What change do I need to make for this command to run?
PS: I have run this statement just to make sure it is not a permission issue and it ran just fine
THANK YOU!
It's been a while.
I am trying to export a table using the bcp utility. Below is the command I am running in SSMS:
CODE --> T-SQL
exec master..xp_cmdshell 'bcp "SELECT Col1 FROM MyTable" out E:\Projects\Reports\mytable.txt -c -T -SMyServer'
which suggests that the bcp.exe being used is the Sybase's one as confirmed by the output of the MS-DOS commandQuote (SSMS)
Password:
CTLIB Message: - L1/O1/S1/N138/1/0:
: user api layer: external error: A data length of 255 exceeds the maximum length allowed for password data.
Setting connection properties failed.
NULL
CODE --> MS-DOS
c:\users\myuser\where bcp
Quote (MS-DOS)
c:\sybse\ocs-15_0\bin\bcp.exe
Checking the environment variables, I found that the path to the Sybase bcp.exe executable was recorded but SQL Server's one is not. So I went ahead and added the path
to the environment variables and now "where bcp" returnsQuote:
D:\Program Files\Microsoft SQL Server\110\Tools\Binn
Quote:
D:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe
C:\Sybase\OCS-15_0\bin\bcp.exe
However, the xp_cmdshell execution is still failing. Adding the full path to bcp.exe in the xp_cmdshell statement complains about it.
What change do I need to make for this command to run?
PS: I have run this statement just to make sure it is not a permission issue and it ran just fine
CODE --> T-SQL
exec master..xp_cmdshell 'dir "\\dbrepository\reports" /s/b > E:\Projects\Reports\test_path.txt';
THANK YOU!
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
CODE
Remember paths with spaces need to be quoted, not only for parameters, also to specify the EXE or CMD or whatever should execute.
You have no solution if two bcp EXEs are in the PATH, but change the PATH to exclude the non-wanted bcp, create several environments to switch, rename one bcp.exe, implement a bat/cmd file that calls the MSSQL bcp.exe for indirect calls. Multiple options.
Chriss
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
when I quote the path...
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
Well, also refer to the docs: A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
So you could look for the MSDOS path that does not include spaces. Or use a single quoted bcp.exe or not quote the SELECT.
If it still doesn't work, how about one of the three other options I talked about?
Chriss
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
CODE --> T-SQL
CODE --> T-SQL
Thank you.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's
Chriss
RE: How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's