Search code examples
sql-serverbcp

BCP Export - Empty String

I have a view that I need to keep as is since it is called all over the place. So it cannot be modified. The view returns some columns with an empty string.

select col1, col2, col3, '' as col4, '' as col5 from myTable

This is how I initiate my BCP command to export that view to a csv file. @FileLocation and @FileName are set beforehand.

declare @SQL varchar(8000)
set @SQL = 'select col1, col2, col3, '''' as col4, '''' as col5 from myTable'
set @SQL = 'bcp "' + @SQL + '" queryout "' + @FileLocation + @FileName + '" -c -t; -q -T"'
exec master..xp_cmdshell @SQL

The file gets created without problems but col4 and col5 are shown as "NUL" in Notepad++. How do I need to modify my BCP command to export empty strings?

Solution

  • Reading all the comments, it seems that there is no way to accomplish what I want. I ended up duplicating the view and populated the empty fields in a way that worked for bcp. Very annoying but apparently the only doable option.