Click here to Skip to main content
15,438,095 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
 
Share this answer
 
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     CRLF
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.

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