How to make xp_cmdshell use SQL Server's bcp utility instead of Sysbase's 1

[TheBugSlayer]

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:

exec master..xp_cmdshell 'bcp "SELECT Col1 FROM MyTable" out E:\Projects\Reports\mytable.txt -c -T -SMyServer'

which returns the following error

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

which suggests that the bcp.exe being used is the Sybase's one as confirmed by the output of the MS-DOS command

c:\users\myuser\where bcp

that looks like this

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

D:\Program Files\Microsoft SQL Server\110\Tools\Binn

to the environment variables and now "where bcp" returns

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

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).

 

[Chris Miller]

This should work:

exec master..xp_cmdshell '"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" "SELECT Col1 FROM MyTable" out E:\Projects\Reports\mytable.txt -c -T -SMyServer'

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

 

[TheBugSlayer]

@Chris Miller I get
[quote='D:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL][/quote]
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).

 

[Chris Miller]

Makes me wonder, if you did copy&paste or not. Notice, it's single quoting of the whole command for xp_cmdshell and double quotes of the bcp.exe full path within.

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

 

[TheBugSlayer]

So I made a copy of bcp.exe in the MS folder and renamed it to bcp_sql. Then if I use it as

exec master..xp_cmdshell 'bcp_sql MyTable out E:\Projects\Reports\mytable.txt -c -T -SMyServer'

it works. However, I need to list columns but

exec master..xp_cmdshell 'bcp_sql "SELECT Col1, Col2 FROM MyTable" out E:\Projects\Reports\mytable.txt -c -T -SMyServer'

fails with message

A valid table name is required for in, out, or format options.
NULL

I have a feeling I am close...Still working on it.

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).

 

[TheBugSlayer]

Got it! "queryout" should be used instead of "out" when a query is used. 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).

 

[Chris Miller]

You're welcome. Glad you got it working, finally.

Chriss

 

[Nick_Evans1]

You can try salesforce odbc driver free for better work with the database.