BCP syntax to create CSV file

Question

When I try to run this procedure, it just give gives me the switches available to BCP. This is my first time with BCP. I'm trying to write a stored procedure to export one day of data to a CSV.

declare @startdate as datetime = '2017-01-24'
declare @enddate as datetime = dateadd(day,1,@startdate)

declare @sql varchar(8000)

set @sql =
'bcp "select * from tblBOJEOJ
where system = ''MKEV03''
and [date] between ''' + cast(@startdate as nvarchar(11)) + ''' and ''' + cast(@enddate as nvarchar(11)) + ''' " 
queryout D:\Temp\Galaxy\BOJEOJ_.csv
-c -t, -T -S ' + @@SERVERNAME 

print @sql

exec master..xp_cmdshell @sql

This is what returns

Thanks

Can you include the result from your PRINT statement in the question? — Gareth Lyons
– Gareth Lyons, Commented Feb 8, 2017 at 16:49

Pரதீப் · Accepted Answer · 2017-02-08 16:51:05Z

1

BCP is quite strange. Keep the BCP command in single line

declare @sql varchar(8000)

set @sql =
'bcp "select * from tblBOJEOJ where system = ''MKEV03'' and [date] between ''' + cast(@startdate as nvarchar(11)) + ''' and ''' + cast(@enddate as nvarchar(11)) + ''' " queryout D:\Temp\Galaxy\BOJEOJ_.csv -c -t, -T -S ' + @@SERVERNAME 

print @sql

exec master..xp_cmdshell @sql

answered Feb 8, 2017 at 16:51

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jwhitf4770 Over a year ago

Thanks this fixed it! I did have to add dbname.dbo to my table in the select.

Pரதீப் Over a year ago

@jwhitf4770 - Instead you can use -d parameter

jwhitf4770 Over a year ago

Is there a switch to put column names on the file?

Collectives™ on Stack Overflow

BCP syntax to create CSV file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related