BCP export to csv

Question

Post reply

BCP export to csv

romanoplescia

SSCommitted

Points: 1787
More actions
December 11, 2009 at 2:34 am

#207345

Hi guys,
I'm having a problem exporting data to a csv document using bcp. I would loved to have used SSIS but as my new company only has SQL 2008 Web edition this is not an option. The problem I have is that when I try and run the bcp I only get the advanced option in the results window and nothing is copied to the file. I have attached the T-SQL I wrote + the results, any help will be helpful.
DECLARE @cmdstr varchar(100)
SET @cmdstr = 'bcp "select sku,auction_type_id
from auctions
where status_id = 2" queryout "D:\ppc_auctions.txt" -t -c "," -T'
print @cmdstr
EXEC master..xp_cmdshell @CMDstr
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]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hariprasad.M SSC Veteran Points: 236 More actions · Answer 1

Use the below query

---------------------

DECLARE @cmdstr varchar(100)

SET @cmdstr = 'bcp "select sku,auction_type_id

from auctions

where status_id = 2" queryout "D:\ppc_auctions.txt" -t "," -c -T'

print @cmdstr

EXEC master..xp_cmdshell @CMDstr

romanoplescia SSCommitted Points: 1787 More actions · Answer 2

Thanks, but still the same result. I've also tried the code below but still the same result

DECLARE @cmdstr varchar(200)

Declare @path varchar (100)

set @path = 'D:\ppc_auctions.txt'

SET @cmdstr = 'bcp "select sku,auction_type_id

from auctions

where status_id = 2" queryout ' + @path + ' -t"," -c -T'

print @cmdstr

EXEC master..xp_cmdshell @cmdstr

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Hi,

Try the following example

DECLARE @cmdstr varchar(100)

SET @cmdstr = 'bcp "select name,id from sys.sysobjects where id >200" queryout "D:\ppc_auctions.txt" -t"," -c -T'

print @cmdstr

EXEC master..xp_cmdshell @CMDstr

--Don't break the select statement (Write in a single line)

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

DECLARE @cmdstr varchar(200)
Declare @path varchar (100)
set @path = 'D:\ppc_auctions.txt'
SET @cmdstr = 'bcp "select sku,auction_type_id from auctions where status_id = 2" queryout ' + @path + ' -t"," -c -T'
print @cmdstr
EXEC master..xp_cmdshell @cmdstr

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

romanoplescia SSCommitted Points: 1787 More actions · Answer 5

Dude, you're a genius! I Can't believe that just moving the statement onto one line made all the difference!

Thanks 😀