×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread183-1818401

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:

CODE --> T-SQL

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

Quote (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
which suggests that the bcp.exe being used is the Sybase's one as confirmed by the output of the MS-DOS command

CODE --> MS-DOS

c:\users\myuser\where bcp
that looks like this

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

Quote:

D:\Program Files\Microsoft SQL Server\110\Tools\Binn
to the environment variables and now "where bcp" returns

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

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

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

(OP)
@Chris Miller I get

Quote ('D:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL)

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

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

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

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

(OP)
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 --> T-SQL

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 --> T-SQL

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

Quote:

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

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

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

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

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

Chriss

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close