Rank: Advanced Member
Groups: Member
Joined: 8/27/2008
Posts: 41
Points: 123
Location: Bangalore, India
|
how store procedure send email to users in MS SQL Server 2005 hi ! i have to send mail with link to the user who are all registered. this process is completed and i stored the information in sqlserver. but if the user didnt accept the link for three days from the sent date then have to send a reminder message as a mail to the user ! how to send the reminder message as a mail...? am working in vb.net backend sqlserver 2005 if anyone have a idea of solving this pls give me some idea !! the reply for this is 1. You will first need to setup SQL Server so that it can send emails. Search the web for some tutorials on how to do this. 2. The stored procedure to send emails is master..xp_send_mail. 3. Create a stored procedure that accesses your tables and, if the todays date is right, creates and sends the emails. 4. Finally create a Job that runs the stored procedure at the required times. (In SQL 2000 this is located in Management -> SQL Server Agent -> Jobs) first step completed ! what have to do for second step...?
|
Rank: Advanced Member
Groups: Member
Joined: 4/3/2008
Posts: 183
Points: 555
Location: London
|
how store procedure send email to users in MS SQL Server 2005Hi Use the following stored proc to send email from SQL server 2000 but since u r using SQL server 2005 u will have to rub the following script to enable OLE Automation Procedures since by default they r disabled in sql server 2005 Code:sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Then create the stored proc
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output
AS
DECLARE @imsg int DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)
EXEC @hr = sp_oacreate 'cdo.message', @imsg out
--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
--SMTP Server
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'smtp.xxx.com'
--UserName
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
'abc@xxx.com'
--Password
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
'xxxxxx'
--UseSSL True/False
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'True'
--PORT
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'Port No''
--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
'1'
EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject
-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null
SET @output_mesg = 'Success'
-- sample error handling.
IF @hr <>0 SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN
--set @output_desc = ' source: ' + @source
set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg
To execute the stored procedure do the following
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)
EXEC sp_send_cdosysmail @From,@To,@Subject,@Body,@BodyType, @output_desc = @out_desc output, @output_mesg = @out_mesg output
PRINT @out_mesg Depending on your mail settings you can use the above stored procedure to send email using smtp server And u can schedule this sp in SQL server schedular to run on daily basis Thats it
|
Rank: Advanced Member
Groups: Member
Joined: 11/9/2007
Posts: 559
Points: 1,008
Location: GB
|
how store procedure send email to users in MS SQL Server 2005To send Database Mail using stored procedures, you must be a member of the DatabaseMailUserRole database role in the msdb database. Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. Go to Surface Area configuration for features and expand Database engine option and select DatabaseMail option and checked Enable Database mail Stored procedure and click OK or Apply button. The code sample below sends the e-mail through stored procedures: Code:-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'KadetEmailAlertAccount',
@description = 'Mail account for sending email alert for Kadet.',
@email_address = 'prachi_purwar@keane.com',
@replyto_address = 'prachi_purwar@keane.com',
@display_name = 'Prachi Purwar',
@mailserver_name = 'MAIL101.KDS.KEANE.COM'
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'KadetEmailAlertProfile',
@description = 'Profile used for sending email alert for Kadet.'
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'KadetEmailAlertProfile',
@account_name = 'KadetEmailAlertAccount',
@sequence_number = 1
-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'KadetEmailAlertProfile',
@principal_name = 'public',
@is_default = 1
-- Send the mail to recipients
-- @body_format can have any one value TEXT or HTML
-- @copy_recipients as CC and @blind_copy_recipients as BCC are optional
parameters
-- @file_attachments as file attachement is optional parameter
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'KadetEmailAlertProfile',
@recipients='emailid1@yahoo.com;emailid2@yahoo.com',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Wishing'
|
