Click here to Skip to main content
15,391,989 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi friends,

I am trying to export the contents of a table to a csv file. I have tried the following code but cannot find out what I'm doing wrong.

SQL
declare @sql varchar(8000)
select @sql = 'bcp travelcatdata.dbo.communicator out 
''c:\datafiles\comm.txt'' -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql


The file is not created and when I run the query I get the following in the results pane:

VB
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
NULL



Can anyone help please?

Preferably I would like to export the results of a stored procedure accepting parameters but a table would also do fine.
Posted
Updated 18-Aug-11 4:53am
v2

1 solution

BCP examples[^]


taking resource from the above link I would suggest that you try removing the quotes from the destination file name

e.g.

SQL
declare @sql varchar(8000)
select @sql = 'bcp travelcatdata.dbo.communicator out c:\datafiles\comm.txt -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql
   
Comments
milenalukic 18-Aug-11 11:19am
   
Done that but it's exactly the same. Any other ideas?
Simon_Whale 18-Aug-11 11:37am
   
This worked for me

declare @sql varchar(2000)
declare @server varchar(100)
set @server = 'SIMONWHALE\SQL_2005'

set @sql = 'bcp oak_underwriting.dbo.broker out "c:\tests.csv" -c -t -T -S"' + @server + '"'
exec master..xp_cmdshell @sql
milenalukic 18-Aug-11 11:45am
   
Worked here too thanks. The problem I had is because I had the code over 2 lines. As soon as I put then on the same line it worked.
Simon_Whale 18-Aug-11 11:50am
   
anytime :)
Corporal Agarn 19-Aug-11 8:00am
   
That is because when defining a string line breaks are inserted into the character string.
RaviRanjanKr 18-Aug-11 15:10pm
   
Nice, My 5+

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900