4

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 .csv file.

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 Header=Yes.

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).

  • 2
    You can automate column list generation. Extract it from INFORMATION_SCHEMA, or merge bcp- produced template file with data. No single step solution unfortunately. – Serg Jun 1 '16 at 19:07
3

The problem with using UNION to include the column names is that column names are strings but the fields in the unioned query are not necessarily strings (as you discovered with that error posted in the Question). And you cannot convert names into other types such as INT, DECIMAL, MONEY, DATETIME, etc.

I did not have to provide any cast/convert when I used the initial syntax.

That is because BCP.exe was converting each non-string field of the table (or result set) into a string so that it could be placed into the output file. But when you UNION two or more queries together, you need to ensure that each position within each SELECT matches that same position in all the other SELECTs so that a result set can be built.

Hence you are left with a few options:

  1. Use UNION (similar to what you attempted), but then use CONVERT on the columns in the table that are not strings. For example (assuming that abc1, abc3, and abc4 are VARCHAR / NVARCHAR while acb2 is DATETIME, and abc5 is INT):

    UNION ALL SELECT [abc1], CONVERT(NVARCHAR(50), [abc2], 121), [abc3],
    [abc4], CONVERT(NVARCHAR(200), [abc5]) FROM test
    

    The downside here is that you cannot use the simple syntax of just specifying the table name.

  2. Create a text file of the header row as a separate step, then execute BCP, then concatenate the two files together. I show two examples of doing that in the following DBA.StackExchange answer: Insert custom header row in BCP output

  3. Use .NET (SQLCLR or Console App; or PowerShell might even work) to execute the query and write the results to a text file. When using SqlCommand.ExecuteDataReaderto run the query, the returnedSqlDataReader` object will have the result set fields (names, datatypes, etc). You can use GetSchemaTable to get the result set field info.

    If this seems like a good option but you either can't code this or don't have the time and/or desire to, I created a SQLCLR Stored Procedure that does exactly this, called DB_BulkExport, and is included in the Full version (this is not available in the Free version) of SQL#. Sometime later this year there will be a stand-alone Console App version of this export, with additional features :-).

  • 1
    Great explanation. I was going to recommend PowerShell as an easy end 'round. – Steve Mangiameli Jun 2 '16 at 20:43
  • @SteveMangiameli Thanks. Do you happen to know if it can also do the SqlDataReader.GetSchemaTable method? Does it have access to .NET stuff? Or just many of the same things named the same? – Solomon Rutzky Jun 2 '16 at 20:45
  • I haven't tried, but it has access to .NET methods and properties. I'm not going to pretend to be a developer, not even on the hobbyist level. So I'm not going to be able get much more detailed than that. Another user that frequents these forums Shawn Melton, could probably answer that question in great detail. – Steve Mangiameli Jun 6 '16 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.