Why BCP creates such big files?

Question

I'm creating some reports via BCP to be sent via emails.
create TABLE ##tempsss
    (
        create TABLE ##JOB_DataAssociacao_verContrato
(
     F1        VARCHAR(6)  
    ,F2        VARCHAR(200)  
    ,F3        VARCHAR(22)  
    ,F4        char(1)    
    ,F5         varchar(10) 
    ,F6         varchar(15)
    ,F7         varchar(30)  
    ,F8          varchar(10)      
    ,F9          VARCHAR(18)  
)  --it needs to be varchar to be able to use header
    )
    
insert into  ##tempsss
SELECT 
...fields...     
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepathfilename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='pathfilename.xls'
-------------------------------------------------------------

drop table ##tempsss

with a 9k rows, I have a 2mb file. I just copy the content of this file, and paste inside an empty excel file and I have 160KB.
how can I use bcp but creating smaller files?
I'm havin gproblems to send files over 1mb via dbmail.
I set dbmail to send files with 10mb+. smtp is ok too.

Max Vernon · Accepted Answer

Excel uses compression internally when saving a file, that is the most likely reason why you're seeing a much smaller Excel file.
I would recommend compressing the .bcp file prior to sending it via email.  Use a command-line compressor such as 7-Zip.
Also, I would recommend using the BCPs Native (-n) or Unicode Native format, via the -N option. Be careful using -c mode if you have Unicode (nvarchar/nchar) data types in your export.

Native format maintains the native data types of a database. Native format is intended for high-speed data transfer of data between SQL Server tables. If you use a format file, the source and target tables do not need to be identical. The data transfer involves two steps:

Bulk exporting the data from a source table into a data file
Bulk importing the data from the data file into the target table.

The use of native format between identical tables avoids unnecessary conversion of data types to and from character format, saving time and space. To achieve the optimum transfer rate, however, few checks are performed regarding data formatting. To prevent problems with the loaded data, see the following restrictions list.

From the Books Online for bcp.exe command-line utility:

-n
Performs the bulk-copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.

For more information, see Use Native Format to Import or Export Data (SQL Server).

-N
Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode.

For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).

Why BCP creates such big files?

One Answer

Add your own answers!

Ask a Question