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

bcp error

I am getting an error that I can't open the host file when running bcp
through a tsql statement. When I set the path to a local drive on the sql
server the utility runs. However, when I set the path to a unc path that th
e
server and service account both have permission to then it won't run.
Any ideas on what I should try to fix this problem?Who is the owner of the job? If it is not sa then you will use the account
setup for the Proxy. See this: http://www.support.microsoft.com/?id=269074
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>I am getting an error that I can't open the host file when running bcp
> through a tsql statement. When I set the path to a local drive on the sql
> server the utility runs. However, when I set the path to a unc path that
> the
> server and service account both have permission to then it won't run.
> Any ideas on what I should try to fix this problem?|||I don't know if this helps answer your question, but it does not run as a
job. It is running through a stored procedure. DBO is the ower of the
stored procedure.
"Andrew J. Kelly" wrote:

> Who is the owner of the job? If it is not sa then you will use the accoun
t
> setup for the Proxy. See this: [url]http://www.support.microsoft.com/?id=269074[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>
>|||Whether it is a job or not still has the requirement of who is trying to
execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
that user is a member of the sa role it will use the account that SQL Agent
is running under. If not it will attempt to use the account that is set for
the Proxy account of SQL Agent. The proxy account most likely does not have
the right permissions. From BOL under xp_cmdshell:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...[vbcol=seagreen]
>I don't know if this helps answer your question, but it does not run as a
> job. It is running through a stored procedure. DBO is the ower of the
> stored procedure.
> "Andrew J. Kelly" wrote:
>|||I executed the following:
xp_sqlagent_proxy_account n'get'
It returned an account. I checked the account and it was a member of the
sysadmin role.
To me, it seems like it should be working.
"Andrew J. Kelly" wrote:

> Whether it is a job or not still has the requirement of who is trying to
> execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
> that user is a member of the sa role it will use the account that SQL Agen
t
> is running under. If not it will attempt to use the account that is set f
or
> the Proxy account of SQL Agent. The proxy account most likely does not ha
ve
> the right permissions. From BOL under xp_cmdshell:
> When xp_cmdshell is invoked by a user who is a member of the sysadmin fixe
d
> server role, xp_cmdshell will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent prox
y
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail. This is true only for
> Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
> impersonation and xp_cmdshell is always executed under the security contex
t
> of the Windows 9.x user who started SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...
>
>|||That does not mean it has permissions to access the file share or even the
server. Is that account a domain account with permissions to that share?
The sa part has to do with who is running the command and not the proxy
account. Please read the entry in BOL for xp_cmdshell.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...[vbcol=seagreen]
>I executed the following:
> xp_sqlagent_proxy_account n'get'
> It returned an account. I checked the account and it was a member of the
> sysadmin role.
> To me, it seems like it should be working.
>
> "Andrew J. Kelly" wrote:
>|||The account is a domain account and does have permissions to the share.
"Andrew J. Kelly" wrote:

> That does not mean it has permissions to access the file share or even the
> server. Is that account a domain account with permissions to that share?
> The sa part has to do with who is running the command and not the proxy
> account. Please read the entry in BOL for xp_cmdshell.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...
>
>

Batch T-SQL Scripts

In Horacle (thanks to someone else for the apt pseudonym), I am able to run a PL/SQL script saved as a file by simply typing in @. plus the path and file name. So, if I have a script called E:\CreateTable.sql, I can simply enter @.CreateTable.sql from the SqlPlus command line and Oracle will attempt to run all the commands in the E:\CreateTable.sql file (there may be one or more commands).

Can I do the same thing in Query Analyzer (or even osql)? If so, what is the correct syntax?

Thanks,

hmscottI don't know of a way to do it from a script in either Query Analyzer or OSQL, but you can open a new file in Query Analyzer and execute it that way and you can use the ED command in OSQL to allow you to incorporate the file (via the editor) there too.

-PatP|||Not that I've seen...the closest is

File>Open>filename.sql

[CTRL]+E

And Whore-acle is so much more painful in SQL+...I guess each has it's own pluses...

Did you ever use mask.sql?

Oh, and osql could run in a command line pretty easily...just make sure you redirect the output...

But why bother, unless you're releasing a script to production...|||Thnx guys. Just wishful thinking...