Click here to Skip to main content
13,352,408 members (52,507 online)
Rate this:
Please Sign up or sign in to vote.
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.
Posted 18-Aug-11 5:27am
Updated 18-Aug-11 5:53am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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
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 :)
djj55 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.180111.1 | Last Updated 18 Aug 2011
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100