赞
踩
1、创建作业具体过程参考网上,据说sql server 2008 R2需要windows身份认证登录,这个我没验证过;
2、创建作业需要填发送者邮箱地址、密码认证和smtp服务器,起初是用qq邮箱作为发送者,qq邮箱开启smtp服务
器需要开启smtp服务,开启smtp服务需要“密码保护”,果断换163的smtp服务。
3、作业实例代码如下:
- DECLARE @l_preday VARCHAR(10) ,
- @l_day VARCHAR(10) ,
- @l_new INT ,
- @l_huoyue INT ,
- @l_chongzhi INT;
-
-
- SET @l_preday = CONVERT(VARCHAR(10), GETDATE() - 1, 121);
- SET @l_day = CONVERT(VARCHAR(10), GETDATE(), 121);
-
-
- SELECT @l_new = COUNT(userid)
- FROM database.dbo.tablename
- WHERE date >= @l_preday
- AND date < @l_day;
-
- SELECT @l_huoyue = COUNT(userid)
- FROM dbname.dbo.tablename
- WHERE logondate >= @l_preday
- AND logondate < @l_day;
-
- SELECT @l_chongzhi = COUNT(order_id)
- FROM dbname.dbo.tablename
- WHERE date >= @l_preday
- AND date < @l_day;
-
- DECLARE @v_content NVARCHAR(MAX) ,
- @v_body NVARCHAR(MAX);
- SET @v_content = '';
-
- SET @v_content = @v_content + '<tr><td>' + @l_preday + '</td><td>'
- + LTRIM(RTRIM(STR(@l_new, 12))) + '</td><td>' + LTRIM(RTRIM(STR(@l_huoyue,
- 12)))
- + '</td><td>' + LTRIM(RTRIM(STR(@l_chongzhi, 12))) + '</td></tr>';
- IF LEN(@v_content) > 0
- BEGIN
- SET @v_body = '<html><body bgcolor=white> <table border = 1><tr><th>标1</th> <th>标2</th> <th>标3</th> <th>标4</th> </tr>';
-
- SET @v_body = @v_body + @v_content + '</table></body></html>';
-
- EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMailConfig163',
- @recipients = 'xxxxxx@qq.com;xxxxx@163.com', @subject = '统计', @body = @v_body,
- @body_format = 'HTML';
- END;
4、上面作业一定要在sql server 2008 R2中先执行以下看有没有报错,在放入作业;profile_name名字替换成自己在创建job的时候填的,msdb.dbo.sp_send_dbmail是系统自带的。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。