bcp error through xp_cmdshell

Question

bcp error through xp_cmdshell

Ambuj Mathur

SSC Eights!

Points: 892
More actions
June 3, 2009 at 9:24 am

#222031

I need to extract a table data in SQL server into a file and store it at a place on Windows drive.
But when I run the below command,
master..xp_cmdshell 'bcp ..
out -U -P '
I get the following error message:
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
Establishing connection failed.
Can anyone help in understanding this and provide a solution?
Thanks! 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

master..xp_cmdshell 'bcp ..out -U -P '

i guess you need to read up on the syntax. If that is your real command, you are not specifying any of the required values;

here's a working example:

declare @sql varchar(4000),

@rowcount int

--sample query: you would do the same to your existing bcp

set @sql = 'bcp "SELECT TOP 5 * FROM SYSOBJECTS" queryout "c:\body.txt" -c -U"sa" -P"NotARealPassword"'

--export via bcp

insert into #results

EXEC master..xp_cmdshell @sql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ambuj Mathur SSC Eights! Points: 892 More actions · Answer 2

Thanks Lowell!

I did try the code sent by you but I'm still getting the same error message as before -

CTLIB Message: - L6/O8/S5/N3/5/0:

ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.

Establishing connection failed.

Can you interpret the message and advice? 😉

dragana kubura SSC Enthusiast Points: 174 More actions · Answer 3

If you have a sybase client installed on that server, check your path variable. This sounds to me like sybase error. If you need more information, please let me know.

JasonYousef SSCommitted Points: 1769 More actions · Answer 4

Hi,

I'm having the same problem and Ive sybase client installed, so what path variables you're talking about here and how to change it?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

huslayer (6/1/2011)
Hi,
I'm having the same problem and Ive sybase client installed, so what path variables you're talking about here and how to change it?
Thanks

the %PATH% allows you to do things like just type "notepad.exe" in the run command and let the operating system resolve the actual path to the executable for you behind the scenes....

but if you have the same executable in two or more folders that exist in the path, you are not guaranteed to call the one you were thinking about...it's just the first one the OS finds.

if you go to a command window and type echo %PATH%

you might see a path to a sybase folder as well as to SQL server..so which bcp is it going to decide to use?

So to fix that you might need to explicitly put the entire path to the SQL server bcp, ie like this: