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 "filepath\filename.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='path\filename.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.
Best 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.From the Books Online for bcp.exe command-line utility: