bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
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.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:
> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
--
Thanks
Yogish