1

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.

Improve this question
7
  • Why not try fixed length delimiters. See answer at social.msdn.microsoft.com/forums/sqlserver/en-US/…
    – RLF
    Apr 28, 2015 at 3:15
  • You can use no space 0x90 or 0x8F as described in the blog post
    – Kin Shah
    Apr 28, 2015 at 3:41
  • Create a view that concatenates all columns together. That way you are exporting a single column, so no delimiters are required. This might not be ideal if you have a large number of columns.
    – garthmillar
    Apr 28, 2015 at 10:34
  • Hi kin, I already tried with 0x90 and 0x8F and they are giving hidden character delimiter. But when you open the file in Notepad++, they count as 3 characters. so, its not working for my need.
    – Deepak
    Apr 28, 2015 at 20:16
  • Hi garthmillar, you are right, we have more than 135 columns which are returned and the stored procedure is doing some casting also on most columns. Concatinating was a difficult option for us.
    – Deepak
    Apr 28, 2015 at 20:18

1 Answer 1

Reset to default
1

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

Improve this answer
1
  • You can accept your answer (once you have enough reputation) or if you dont want this question, then you can delete it as well.
    – Kin Shah
    May 18, 2015 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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