BCP path problem

[Karin]

Why can't I include the path to the bcp utility when I use xp_cmdshell?

This works fine in the command window:
"C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
"ABData.dbo.tCMD_OutputToFile" out "c:\temp\abdata.txt" -c -U"chagus" -P"c"

but this don't work from within SQL Query Analyzer (or a stored procedure):
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
"c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output

Thanks in advance

/Karin

[Ryan]

Is the command window on your client PC or the SQL Server ...?

Default SQL Server install should add the path for BCP.EXE to the server
%PATH%. You shouldn't need to include it eg:-

EXEc master..xp_cmdshell "bcp ...." should suffice

--
HTH. Ryan
"Karin" <Ka...@discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C...@microsoft.com...

[Rick Sawtell]

"Karin" <Ka...@discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C...@microsoft.com...

What error are you getting?

When you xp_cmdshell, you are executing in the security context of either
the MSSQLServer service account (if you are an sa in the db), or in the
security account associated with the SQLExecutiveCmdExec if you are a
non-sa. Ensure that whichever account has the rights needed to execute in
that directory.

Rick Sawtell
MCT, MCSD, MCDBA

[Karin]

I have both the client and server on my developer-PC so I run the usual
command window (Run cmd). The problem is that when I specify the path in
xp_cmdshell no file will be created and no error message appears anywhere,
I've looked in SQL Server's Log and Event Log. This is the only thing I get:
The command(s) completed successfully.

I know that I don't need to specify the path, but I have a customer which
probably have some other bcp installed (perhaps sybase I don't know) beause
she gets the following 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.

I read that It should works even if you specify the path, so why doesn't it?

[Karin]

I don't get any error message, only: The command(s) completed successfully.
But no file creates in C:\temp

I am sa and administrator and SQL Server runs under local system account.

[Ryan]

Having read the error message and looked at your BCP cmd line i can't see
the -Sservername switch.

eg :- bcp ABData.dbo.tCMD_OutputToFile out
c:\temp\abdata.txt -c -Uchagus -Pc -Sservername

But if that were the case it wouldn't work on your dev machine either..

--
HTH. Ryan
"Karin" <Ka...@discussions.microsoft.com> wrote in message

news:32355673-659B-4BBA...@microsoft.com...

[Karin]

No, the problem is that no file will be created when I include the path to
bcp.exe in xp_cmdshell command-string... All works fine until I include the
path.

[Karin]

I found the solution in SQL Server Books Online:

command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.

This doesn't work:

exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out

"c:\temp\authors.txt" -c -U"chagus" -P"c"', no_output

but this will:

exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" ABData.dbo.tCMD_OutputToFile out

c:\temp\authors.txt -c -Uchagus -Pc', no_output

:o)

So that means that the output file cannot be created in a path with spaces
in it.

:o(

/Karin

[Ryan]

I'll make a note of that for future..

Thanks for posting your solution

--
HTH. Ryan
"Karin" <Ka...@discussions.microsoft.com> wrote in message

news:C0474E2C-9FAF-4E32...@microsoft.com...

[Erland Sommarskog]

Karin (Ka...@discussions.microsoft.com) writes:
> I know that I don't need to specify the path, but I have a customer
> which probably have some other bcp installed (perhaps sybase I don't
> know) beause she gets the following 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.

Yes, CTLIB is Sybase.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx