November 19, 2008 at 10:44 pm
Dear friends
i m new to sqljob and database email.
i want to send the reminder email to the multiple users.
i searched frm net that i could do this through sqljob but can anyone explain me the exact steps for that so can i configure it in my server.
m confused abt it so help me.
thanks
shweta
November 19, 2008 at 10:57 pm
Hi,
Scripts to create Database mail is available at http://www.sqlserverdba.co.cc/2008/11/script-to-create-database-mail-in-sql.html
Once DB Mail is configured and tested, now Create Jobs (SSMS --> SQL Server Agent --> Jobs --> New Job) and in the Job Steps use below T-SQL to send email to users about your reminder
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' is sending reminder to you! '
EXEC msdb.dbo.sp_send_dbmail @recipients='your users@yourcompany.com',
@subject = 'Reminder Mail',
@body = @body1,
@body_format = 'HTML' ;
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 19, 2008 at 11:18 pm
thanks for your reply.
but i already configured database email through wizard and create a new sqljob but somehow i dont get the emails so as u described i send a test email through database email nd i m waiting for the mail.
shweta
November 19, 2008 at 11:23 pm
Hi,
No need to wait.. You should receive it instantly... Are you having proper account to authenticate to SMTP Server? Take help of your network admin to check the status of deleivery!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 19, 2008 at 11:36 pm
well thanks agin for your concern ya i check it through
SELECT * FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 9 ;
and i got the status fail ane the error is like
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-11-20T11:44:39). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first. 9sm730173wfc.36). )
so when i run the above query i found the same error in all mails so thats why i coudnt receive mails.
i tried r&d for this error but couldnt find anything:P should i delete this account nd try to make the new one?
one more thing as a server m using smtp.gmail.com
thanks
shweta
November 19, 2008 at 11:56 pm
I see you did discover the dbmail helper objects 😉
- Does it succeed if you use a vbs to send mail using that smtp server ?
- double check the port number
- is your firewall opened for this portnumber/ipaddress ?
These are the dbmail helper objects I use:
use msdb
go
exec sysmail_help_queue_sp -- @queue_type = 'Mail'
go
select *
-- delete
from sysmail_event_log
order by log_date
select *
from sysmail_mailattachments
select *
from sysmail_allitems
/*
**** Put this batch in a sqlagent job****** cleanup old messages **********
Declare @sent_before datetime
Select @sent_before = dateadd(MM,1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec sysmail_delete_mailitems_sp @sent_before = @sent_before
, [ @sent_status = ] 'sent_status' ]
delete from sysmail_event_log where log_date < @sent_before
*/
/*
SELECT [account_id]
,[name]
,[description]
,[email_address]
,[display_name]
,[replyto_address]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_account]
SELECT [attachment_id]
,[mailitem_id]
,[filename]
,[filesize]
,[attachment]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_attachments]
SELECT [transfer_id]
,[uid]
,[filename]
,[filesize]
,[attachment]
,[create_date]
FROM [msdb].[dbo].[sysmail_attachments_transfer]
SELECT [mailitem_id]
,[profile_id]
,[recipients]
,[copy_recipients]
,[blind_copy_recipients]
,[subject]
,[body]
,[body_format]
,[importance]
,[sensitivity]
,[file_attachments]
,[attachment_encoding]
,[query]
,[execute_query_database]
,[attach_query_result_as_file]
,[query_result_header]
,[query_result_width]
,[query_result_separator]
,[exclude_query_output]
,[append_query_error]
,[send_request_date]
,[send_request_user]
,[sent_account_id]
,[sent_status]
,[sent_date]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_mailitems]
SELECT [profile_id]
,[principal_sid]
,[is_default]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_principalprofile]
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]
SELECT [profile_id]
,[account_id]
,[sequence_number]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profileaccount]
SELECT [uid]
,[text_data]
,[create_date]
FROM [msdb].[dbo].[sysmail_query_transfer]
SELECT [conversation_handle]
,[mailitem_id]
,[send_attempts]
,[last_send_attempt_date]
FROM [msdb].[dbo].[sysmail_send_retries]
SELECT [account_id]
,[servertype]
,[servername]
,[port]
,[username]
,[credential_id]
,[use_default_credentials]
,[enable_ssl]
,[flags]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_server]
SELECT [servertype]
,[is_incoming]
,[is_outgoing]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_servertype]
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 20, 2008 at 2:46 am
Hi,
When you are setting up the Database mail, there is a option to send a test mail I guess.. Were you able to succeed there?
I guess gmail requires ssl authentication and port numbers are different from normal SMTP port... Incase you need help on gmail, PM me... I will help you
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 20, 2008 at 3:40 am
hi yes i tried to send mail through that but when i check it by the query
select * from sysmail_allitems
i got the status failed or undent
and yes as per u described i set the port no 587 instead of 25
bcaz i guess gmail is using 587(i described it in my web.config)
i m quite confused abt it well i want to admit that i login with user sa so do i need to create that user in msdb database or it is under sys.
bcaz after my previous posts i delete the privious database mail accounts and confifure it again and also created a new job but when i start that job i got an error
the job was invoked by user sa the last step to run was step 1
i m working on it from last 4 days but i dont know where the pr is may be i have a leck of knowledge abt it.
help me if u have any idea.
thanks
shweta
November 20, 2008 at 4:54 am
Hi Shwetha,
There are some workarounds which need to be done in order to get database mail work using smtp.gmail.com in SQL 2005 database mail.
Please check http://www.sqlserverdba.co.cc/2008/11/send-mail-using-smtpgmailcom-from.html for complete information on the same!
Don't forget to check "Check Status:" section in that page on how to test firewall block between your SQL Server and Gmail!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 20, 2008 at 5:09 am
very good info.
Thanks
Johan
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 20, 2008 at 10:27 pm
Thank you very much
its very helpful nd finally it is done.
shweta
November 21, 2008 at 12:15 am
oh! Thatz Gr8... So when is the party? Whenever your come down to Bangalore from Vadodara, Don't forget to give us a lunch treat to all our SQLServerCentral Members at Bangalore! 🙂
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply