Sql-server – How to avoid delimiter character in BCP export

bcpsql server

In my project, I need to use SQL 2008 BCP tool to export the result of a stored procedure into a fixed column width file. I used different approaches, but still couldn't get expected result. Things I tried are explained below.

(The database table from where the SP is fetching values is designed with all columns as varchar with width as needed by the specification of the requirement.)

First I tried (as per this site)

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0 -a 10000 -L 100

This gave me the result with a single space between all columns as delimiter.
Unexpected. Dont need any delimiter.

Second, I tried

bcp "my_sample_sp" queryout "C:\table_output.txt" -S <servername> -d <dbname> -U <username> -P <password> -c -t \0x90 -a 10000 -L 100

This gave me the result with a hidden character between all columns as delimiter.
Unexpected. Most editors and the end application consuming this file read that invisible value as one character.

Third, I tried with format file in bcp tool. (followed this site)

Created native format file using below command

bcp "my_sample_Table" format nul -n -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

Created the flat file using this format file as below.

bcp "my_sample_sp" queryout C:\table_output.txt -f C:\format_file.fmt -S <servername> -d <dbname> -U <username> -P <password>

File is created with all unreadable characters. I understand that native option is to only create files readable by SQL servers. I tried almost all options around this which are mentioned in the referred site.

Using SQLCMD tool, I was able to create the required output. As the count of records I need to export would easily cross 5 million records, I was told that SQLCMD tool is not suggested for that much count.

I tried all possible approaches before posting here. Any help which can guide me in the correct direction to create a fixed width flat file from a SP output is much appreciated.

Best Answer

I managed to make this work by combining all columns into one single string from SQL and return it to BCP tool.