Using sp_send_dmail to send sql to users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Using sp_send_dmail to send sql to users

Author

Topic

bobrayran
Starting Member

1 Post

Posted - 2007-10-11 : 16:34:04

I'm trying to send users some results but the results are different
per user so I was trying to use a table with emails to drive the results from the SQL in the @query parameter of the sp_send_dmail but I also wanted to use the same variable in the @recipient parameter to control the right recipient receiving the right data.
I'm just learning this so if the code looks messy...then help me out...

Here is the code I have....

--email variables
set nocount on
set transaction isolation level read committed
DECLARE
@AttachmentName varchar(100),
@Body varchar(1000),
@Query varchar(1000),
@RetCode bit,
@To varchar(200),
@ReportName varchar(255),
@Subject varchar (60),
@EmailCode varchar (1000)

SET @EmailCode ='
--This will return nothing but emails with a semicolon at the end
set nocount on
SELECT DISTINCT Member_Master.Email_addr+";"
FROM Member_Master
WHERE Member_Master.company_tax_id = "TEST"'

SET @Query = '
set nocount on
SELECT Member_Benefits.PlanName
FROM Member_Benefits
WHERE Member_Benefits.Email_address = @EmailCode;'

SET @ReportName = 'Test Report'
SET @Subject = 'Automated SQL Reporting : '+@ReportName

EXEC @Retcode = msdb.dbo.sp_send_dbmail
@profile_name = 'Medcom',
@subject = @Subject,
@recipients = @EmailCode,
@body = @Body,
@body_format = 'Text',
@query = @Query,
@query_result_header = 0,
@attach_query_result_as_file = 0;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources