AnswerBun.com

Why BCP creates such big files?

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.

Database Administrators Asked by Racer SQL on January 1, 2021

1 Answers

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

Correct answer by Max Vernon on January 1, 2021

Add your own answers!

Related Questions

Counting rows from a subquery

3  Asked on November 14, 2021 by rwcommand

     

How to get the registers in the highest time of period

1  Asked on November 14, 2021 by wilson-eduardo-millan-vargas

 

Mix ;WITH NAMESPACES and schema collections?

1  Asked on November 14, 2021 by user1664043

   

MariaDB tmpdir & table copy operations

1  Asked on November 11, 2021 by user120053

 

SAP Oracle Full Online Backup Failing

1  Asked on November 11, 2021 by plamenbv

     

SQL Server Replication

1  Asked on November 8, 2021 by derek-czarny

       

Performance MySQL query with for loop

1  Asked on November 8, 2021 by fabian-sierra

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.