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.
Asked by Racer SQL
(7546 rep)
Oct 7, 2020, 12:03 PM
Last activity: Jun 26, 2022, 12:45 PM
Last activity: Jun 26, 2022, 12:45 PM