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

TheBugSlayer · Aug 8, 2022

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:

Code:

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

which returns the following error

SSMS said:
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

Code:

c:\users\myuser\where bcp

that looks like this

MS-DOS said:
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

Code:

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 · Aug 8, 2022

This should work:

Code:

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 · Aug 8, 2022

@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... [ponder]

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 · Aug 8, 2022

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 · Aug 9, 2022

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

Code:

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

Code:

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 · Aug 9, 2022

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 · Aug 9, 2022

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

Chriss

Nick_Evans1 · Sep 5, 2022

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

TheBugSlayer

Programmer

Chris Miller

Programmer

TheBugSlayer

Programmer

Chris Miller

Programmer

TheBugSlayer

Programmer

TheBugSlayer

Programmer

Chris Miller

Programmer

Nick_Evans1

Programmer

Similar threads

Part and Inventory Search

Sponsor