Click here to Skip to main content
15,901,426 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.

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:

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]

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.
Updated 18-Aug-11 4:53am

1 solution

BCP examples[^]

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


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
Share this answer
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 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.

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