July 10, 2008 at 6:06 am
HI,
I know d that we can send e-mails but never work.need a help.
I have to send e-mails to my site registered users on every hourly,weekly and monthly basis.
conceptual wise i know how to do but technically I have very little idea.
I have to create multipe jobs that will initiated on different time.from each job I have to send mails.
but the content of the e-mails must be drived from the Query.Can i customize the result of the query.
any code example or good Link will be very much appreciate
1.Can we Initiate any job from asp.net without blocking.means it initiate the job and countinue without waiting for completion.
2.In the Job i have to check which are the latest record by taking out difference between current time -last job time and select all records in between and loop through this ,and get the user email i d for that and send a mail.
Thanks for any help.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply