With SQLSERVER database operations to achieve mail reminder
Implementation effect:
To send mail to information on a regular basis, to remind them to have X days without a sign. (X is the definition of their own)
The realization principle:
Use the sqlserver “ job ” “ Database Mail, ” and “ stored procedure”
The data used in the:
Use the user table, the table name: tb_Admin. The following display field main be used:
The field name |
Interpretation |
Id |
Primary key |
RealName |
User real name |
LoginTime |
Display user login time |
UserEmail |
The user's mailbox |
The stored procedure:
Just learning sqlserver, write is not very good. But the final effect is possible. If you have any better idea, want to give me a message, let me learn more things.
The stored procedure function: to achieve pass parameters, thus the output data from the mailbox, and user real name:
The following code:
Create PROCEDURE [dbo].[SendEmail] @count int,@email nvarchar(50) output,@name nvarchar(20) output AS BEGIN select @email= UserEmail,@name=RealName from (select *,ROW_NUMBER() over(order by Id) rownum from tb_Admin where DATEDIFF(DD,LoginTime,GETDATE())>30) t where t.rownum =@count END
Database Mail Service “ ” establishment:
As shown in Fig.:
We can use this query to check my mail service is available
use msdb go exec dbo.sp_send_dbmail @profile_name='The name of the configuration file', @recipients='Email address', @subject='This is a test message', @body = 'This is the test content'
Just a specific example above mail service.:
use msdb go exec dbo.sp_send_dbmail @profile_name='Administrator', @recipients='', @subject='This is a test message', @body = 'This is the test content'
Results as shown:
The mail content as follows:
The establishment of Sqlserver operation:
Put the box in the T-SQL script.:
declare @counttest int; declare @email nvarchar(50); declare @content nvarchar(100) declare @name nvarchar(20); select @counttest = COUNT(*) from KJCXB.dbo.tb_Admin where DATEDIFF(DD,LoginTime,GETDATE())>30; while @counttest >0 begin exec KJCXB.dbo.SendEmail @counttest,@email output,@name output; set @counttest = @counttest-1; if @email is not null and LEN(@email)>1 begin set @content = @name+',Hello, you have more days without technology innovation to login, innovation growth together you and I need to focus on, welcome back to have a look: kjcx.heuu.edu.cn'; exec msdb.dbo.sp_send_dbmail @profile_name='Administrator', @recipients=@email, @subject='Go home have a look', @body = @content end end
We will see the assignments below have our own set homework.
The following test:
Then our corresponding mailbox will receive the following message:
This is successful, the database will follow us up activity to detect the unknown for more than thirty days of the user, and then send them a prompt message.
Posted by Sheila at November 20, 2013 - 10:57 PM