BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

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,
Avi
Hi
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

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 troubl
e
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

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