July 10, 2008 at 10:45 am
I use database mail and attach the results of the query to the email. Though I suppose you could parse the query results into the body of the message. Once you've got Database Mail configured you'd use the
msdb..sp_send_dbmail stored procedure and specify the appropriate parameters (i.e. @attach_query_result_as_file = 1 @query and @query_attachment_filename to attach the query results). you can run this in any stored proc or query.
July 10, 2008 at 12:09 pm
I have used the following query from one site.I also configur the local machinge to use 127.0.0.1 as IP for my SMTP server. and after running the query checking the status in sysmail_allitems .it is showing sent but still i did not recieve any mail.In the below query i replace recipents and sender email id to some dummy.but in actuall i m using my own gmail and hotmail id respectively for recipient and sender.
-------------------------------------
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = '127.0.0.1';
SET @EmailAddress = 'dummy@gmail.com';
SET @DisplayUser = 'The Mail Man';
--------------------------------------
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = @ProfileName AND a.name = @AccountName)
BEGIN PRINT 'Deleting Profile Account' EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName
END
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName
)
BEGIN PRINT 'Deleting Profile.' EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName
END
IF EXISTS
(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName
)
BEGIN PRINT 'Deleting Account.' EXECUTE sysmail_delete_account_sp @account_name = @AccountName
END
--------------------------------------
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
ExEC msdb.dbo.sp_send_dbmail
@recipients=N'dummy@hotmail.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName
select *
FROM sysmail_allitems
-------------------------------------
July 10, 2008 at 12:45 pm
See if the below link helps..I have been using this extended stored procedure for a long time and it has been working great. It uses SMTP too..
July 10, 2008 at 1:22 pm
We do that here, but basically use a 2 step process.
We have a set of tables where we can store email info and then recipient info and link them. If some process needs to send a mail, it drops a set of rows in the tables and goes on its way.
We have a separate process that runs every minute and picks up xx number of emails. xx is based on the capacity we've found can be sent in a minute.
It then marks the emails as in process, loops through and sends each one, marking it as sent in the database.
We actually have multiple machines doing the sending, but it's very scalable if we need to add more machines.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply