Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

bcp error through xp_cmdshell Expand / Collapse
Author
Message
Posted Wednesday, June 03, 2009 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 18, 2013 5:12 AM
Points: 26, Visits: 278
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! :)
Post #728314
Posted Wednesday, June 03, 2009 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #728523
Posted Thursday, June 04, 2009 12:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 18, 2013 5:12 AM
Points: 26, Visits: 278
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? ;)
Post #728689
Posted Tuesday, February 02, 2010 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 12:01 PM
Points: 24, Visits: 45
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.
Post #858234
Posted Wednesday, June 01, 2011 10:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 70, Visits: 589
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
Post #1118183
Posted Wednesday, June 01, 2011 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
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:

declare @sql        varchar(4000),
@rowcount int
--sample query: you would do the same to your existing bcp

--due to %path% issues, identify the full path to bcp
set @sql = 'C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\bcp.exe "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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1118199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse