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