I'm trying to export a table and I get it right but without the headers. Can you help me? This is what I have done so far
declare @sql varchar(8000)
select @sql = 'bcp "select * from Ana.dbo.detalle_incidencias" queryout C:\prueba.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
As you said @Pikoh bcp has no way to export the names of the fields, however there is a "trick" that may serve you and goes through the idea of exporting on the one hand the names of the fields and the other the data itself, then a "merge" of both files, in your case it would be something like this:
DECLARE @SQL NVARCHAR(MAX)
-- Cabecera
SELECT @SQL = 'BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + '','', '''') + column_name from Ana.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''detalle_incidencias''; select @colnames;" queryout c:\cabecera.csv -c -T -S' + @@servername
exec master..xp_cmdshell @sql
-- Datos
SELECT @SQL = 'bcp "select * from Ana.dbo.detalle_incidencias" queryout C:\datos.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
-- Merge de ambos archivos
exec master..xp_cmdshell 'copy /b "d:\cabecera.csv"+"d:\datos.csv" "d:\prueba.csv"'
-- Borramos archivos intermedios
exec master..xp_cmdshell 'del "c:\cabecera.csv"'
exec master..xp_cmdshell 'del "c:\datos.csv"'
The counter that has this method is the unnecessary additional processing to copy the two files in one, in some cases it can be important.