Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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]
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 18-Aug-11 5:27am
Edited 18-Aug-11 5:53am
v2

1 solution

Rate this: bad
good
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
 
e.g.
 
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
  Permalink  
Comments
milenalukic at 18-Aug-11 11:19am
   
Done that but it's exactly the same. Any other ideas?
Simon_Whale at 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 at 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 at 18-Aug-11 11:50am
   
anytime :)
djj55 at 19-Aug-11 8:00am
   
That is because when defining a string line breaks are inserted into the character string.
RaviRanjankr at 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
0 OriginalGriff 7,215
1 DamithSL 5,114
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,747
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 18 Aug 2011
Copyright © CodeProject, 1999-2014
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