Friday 16 April 2010

send a CSV attachment by Email (ANSI attachment)

Declare @filename varchar(100), @cmd varchar(max)
set @filename = 'output-' + convert(varchar(10), getdate(), 121) + '.csv'

set nocount off
set @cmd = 'exec xp_cmdShell ''bcp "query" queryout ' + @fileName + ' -S server -U user -P pass -f"bcp.fmt"'', no_output'
Exec (@cmd)

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email@domain.com',
@body='Hi,
this is email body
',
@subject ='this is email sub',
@profile_name ='profile',
@file_attachments= @filename

No comments:

Post a Comment