I am using a stored procedure to populate a table with the data that I need. I am then using BCP to copy the data to a
This process works, the one thing it is missing is that BCP is not copying the field names into row1 and I do not see a way to set
How can this syntax be amended to add the header row?
declare @sql varchar(8000) select @sql = 'bcp Test12.dbo.test out C:\testfolder\123.csv -c -t, -T -S '+ @@servername exec master..xp_cmdshell @sql
Using the Stack Overflow Q & A provided by Serg, I updated my syntax to:
Select @sql = 'bcp "Select ''abc1'', ''abc2'', ''abc3'', ''abc4'', ''abc5'' UNION ALL Select [abc1], [abc2], [abc3], [abc4],[abc5] from test" queryout "' Set @sql = @sql + 'C:\testfolder\123.csv' + '" -c -t, -T -S '+@@servername exec master..xp_cmdshell @sql
However, this produces an error:
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]
Conversion failed when converting date and/or time from character string.
abc3 is a datetime column. Should I convert it differently? I did not have to provide any cast/convert when I used the initial syntax.
(I also tried to use
CAST([abc3] As Date) but that threw the same error).