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 3, 2009 9:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:53 AM
Points: 26, Visits: 279
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 3, 2009 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 12,951, Visits: 32,471
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 4, 2009 12:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:53 AM
Points: 26, Visits: 279
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 2, 2010 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 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 1, 2011 10:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 70, Visits: 630
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 1, 2011 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 12,951, Visits: 32,471
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