BCP path problem

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
/KarinIs 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" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> 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|||"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> 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
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|||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?
"Ryan" wrote:

> 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" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
>
>|||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.
"Rick Sawtell" wrote:

> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.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 i
n
> that directory.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||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" <Karin@.discussions.microsoft.com> wrote in message
news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>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?
> "Ryan" wrote:
>|||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.
"Ryan" wrote:

> 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" <Karin@.discussions.microsoft.com> wrote in message
> news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>
>|||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 troubl
e
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
"Karin" wrote:
> 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 th
e
> path.
> "Ryan" wrote:
>|||I'll make a note of that for future..
Thanks for posting your solution
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:C0474E2C-9FAF-4E32-BB83-CF5DF46AF391@.microsoft.com...
>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
>
> "Karin" wrote:
>|||Karin (Karin@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx