Hitesh Joshi
3/22/2007 3:39:00 PM
Hi,
I know I could bcp out my query results in and attach using
xp_smtp_sendmail.
I could send very simple string @messagevariable inside body of email.
But I want to send the whole qury out put from my table variable
inside email body.
Here is something that I am doing for attachment.
DECLARE @filename char(50)
DECLARE @filePATh varchar (1024)
DECLARE @sql varchar (1024)
DECLARE @ret int
DECLARE @msg varchar (1000)
SET @filename = 'mydata.txt'
SET @filePATH = 'C:\Temp\' + @filename
print @filePATH
SET @ret = 0
SET @msg = N'My Data '
SET @sql = 'bcp "exec mydb..xprmydata" queryout c:\temp\mydata.txt -c
-T '
if @@error <> 0 begin
select @ret=@ret+1
end
EXEC master..xp_cmdshell @sql, no_output
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'myname@mycompany.com',
@FROM_NAME = N'My Name',
@TO = N'yourname@yourcompany.com',
@priority = N'NORMAL',
@subject = @msg,
@message = @msg,
@type = N'text/plain',
@attachments= @filePATH ,
@server = N'mail.mycompany.com'
select RC = @rc
go
Hitesh