2017年3月23日 星期四

SQL server 2000邮件配置方法

SQL server 2000邮件配置方法
--========================================
--SQL 邮件配置
--author:Boyi55 date:2006-11-30
--outlook 2003,sql server 2000,163邮局
--========================================
首先要说的一点是,这里要用到的是outlook而不是系统自带的outlook express.我用outlook express试过好多次都没有成功。这次我做的有些乱,所以就打乱顺序来说吧。先说sql server 2000.这里面要配置的主要是sql server 2000的启动帐号和sql agent的启动帐号。不管是sql server 2000还是sqlagent都要用系统管理号帐号。这个可以在sql server 2000里面设,也可以在系统服务里面设。我是在服务里面做的。在运行里打开services.msc找到mssqlserver和sqlserveragent这两个服务。右击属性,在登录选项卡上用“此帐户”点浏览,找到一个管理员帐号,然后输入两次管理员密码。这里我用的是administrator.然后点确定。再打开cmd重启一下sql server 2000。
由于我一直用的outlook express所以这次我新安了outlook 2003.在outlook 2003里新建一个邮件帐户。这里我选用的是163的信箱:boyi55@163.com。对应的pop3和smtp服务器分别为:pop3.163.com和smtp.163.com。这时如果点测试帐户设置,则最后一步会出错,也就是可以收信而不可以发信。我在163的帮助里找到原因是smtp服务器要求验证。所以。可以点“其他设置”在发送服务器选项卡上选上“我的发送服务器(SMTP)要求验证”下面选择“使用与接收邮件服务器相同的设置”点确定。这样再点“测试帐户设置”就会成功了。然后到控制面板里,找到邮件。点显示“配置文件”这里你会看到里面有一个outlook.如果用的是outlook express则在控制面板里是不会出现邮件这个项目的。然后点确定,打开sql server 2000的企业管理器,展开支持服务,右击“sql 邮件”点属性。这时里面就会出现有outlook了。选择它就可以了。如果要建操作员的话,还得在sqlserveragent属性的邮件配置文件这里也选择outlook。这样,去建一个操作员,输入一个e-mail地址。点测试,过一会到你的信箱里就可以收到信了。呵呵。
要注意的几个问题:
1.sql server 2000的启动帐户要和sqlagent的启动帐户相同。并且不能用系统帐户。
2.smtp服务器是否需要验证。hotmail的免费信箱好像不可以在outlook里设置。
3.在sql邮件运行过程中不能关闭outlook,否则不能发送邮件。
sql 邮件(续)
--==============================
--sql邮件发送
--author:boyi55,date:2006-12-03
--==============================
在电脑前整整坐了一天。下午的时候想到上次只是简单的可以让sql server 2000发送邮件。至于邮件的内容,却只是简单的点了下那个测试按钮所发的一封测试邮件。至于sql 邮件到底能做什么还一点也不了解!刚开始试着用作业,当作业执行完成里发信到指定的信箱里。可是这样,只能发送一个人,并且所发信的内容也只是简单的说明作业成功失败。想起有xp_sendmail这一个存储过程,然后可以在作业里加一个步骤,成功时,用sendmail发一段话到信箱里。失败时,用sendmail发另一段话到信箱里。看了下帮助。还好行容易理解。也很简单的就实现了!那能不能把一个结果集发到我的信箱里呢?这样的话,我就可以把一些常用的统计建成一些作业,当用到的时候就直接执行一下不就可以了吗?这个看上去也很容易实现,可结果往往没有想像的那么好!不过最后还是搞出来了。当时正好在看webcasts,所以顺手拿了一个cube的查询过来,呵呵。下面贴些代码出来吧!
--=============================================
--将hello,this e-mail sent from sql server !
--发送给:fengjicai@hotmail.com,h11h99@126.com
--副本发送给:queeny_fei@yahoo.com.cn
--邮件主题是:alter from you SQL server 2000
--author:boyi55,date:2006-12-03
--=============================================
xp_sendmail @recipients='fengjicai@hotmail.com;h11h99@126.com',
@message='hello,this e-mail sent from sql server !',
@copy_recipients='queeny_fei@yahoo.com.cn',
@subject='Alter from you SQL Server 2000'
--=======================================
--将存储过程的结果发送给指定联系人
--author:boyi55,date:2006-12-03
--=======================================
exec xp_sendmail [url=mailto:'fengjicai@hotmail.com',@query='sp_configure']'fengjicai@hotmail.com',@query='sp_configure'[/url]
--============================================
--统计northwind数据库中相关信息
--并将结果发送到指定的信箱
--author:boyi55,date:2006-12-03
--原理:northwind数据库中没有xp-sendmail
--存储过程,所以采用方式为:
--将查询结果写入一个全局临时表
--然后在删除全局临时表
--===========================================
set nocount on
select case when(grouping(od.productid)=1)then '定单总计:'
else isnull(od.productid,'空值') end as productid,
case when (grouping(od.orderid)=1)then '产品总计:'
else isnull(od.orderid,'空值')end as orderid,sum(od.quantity) as'总计'
into ##boyi55
from (select convert(nvarchar(10),productid) as productid,convert(nvarchar(10),orderid) as orderid,quantity from [order details]) as od
group by od.productid,od.orderid
with cube
exec master..xp_sendmail @recipients='h11h99@126.com;fengjicai@hotmail.com;queeny_fei@yahoo.com.cn',
@subject='northwind数据库中order details相关信息汇总结果',
@message='此信息由sql server 2000数据库作业自动发送,如果输出错误,请直接回复e-mail到:[url=mailto:fengjicai@hotmail.com]fengjicai@hotmail.com[/url]或者电话联系侯向博:13633830414!',
@query='select * from ##boyi55',
@width=100--设置查询的输出文本行宽
drop table ##boyi55
set nocount off
--===========================================
--将结果作为附件文件发送
--author:boyi55,date:2006-12-03
--===========================================
exec xp_sendmail @recipients='fengjicai@hotmail.com',
@query='select * from information_schema.tables',
@subject='SQL Server report',
@message='The contents of information_schema.tables:',
@attach_results='true',
@width= 250
======================
这里我想到两个问题。记得好久以前,长风兄问我outlook怎样开机就可以最小化。当然只记得快捷方式属性里有一个运行方式中有一个属性“最小化”,可试了一下还是老样子一点反应都没有。这里可以有几种方法解决。outlook 2003启动的时候在任务栏和通知区域都有显示图标。只要在通知区域右击outlook图标选择最小化时隐藏就可以了。还有可以用任务计划,定义一个任务,当计算机开机时自动运行outlook,并且要选择登录前运行。另外还可以用启动和@命令,但比较一下还是用任务计划比较好一点。
还有一点就是outlook和sqlserveragent这两个,如果sqlserveragent已经启动,这时候再启动outlook会报错,说是文件正在被使用。这时必须先停掉sqlserveragent才可以把outlook启动开。所以我在我的服务器上设置的是outlook在登录前启动,而sqlserveragent则手动启动。

本文出自 “老侯的菜园子” 博客,请务必保留此出处http://boyi55.blog.51cto.com/4345/26946

2017年3月21日 星期二

增加C槽的可用空間方式

How to Free C:\ Drive Disk Space in Windows 7 and Reduce System Size?

來源:http://www.kavoir.com/2012/07/how-to-free-up-c-drive-disk-space-in-windows-7-easy.html

Windows 7 is a beast in consuming hard drive disk spaces, especially the system drive, C, which is constantly being filled up. After a while it will keep annoying you with low disk space warnings because C drive is running out of space and almost full. So how can I safely free up all the disk spaces in C drive in Windows 7 and reduce its disk storage?

The methodology is to find the fattest folders in C drive, move as many of them to another drive (such as D, E, etc.) as possible, and make symbolic links (by mklink) from C drive to the folders in other drives.

Step 1 – Find culprit folders that occupy the most disk space!

There are so many ways to do this but what I did was to look at the size stats for each of the major folders in my C drive. After 10 minutes, I was able to pinpoint these folders that are consuming large chunks of the disk volume:

  • C:\Windows\winsxs – very high-profile system folder that are not easily reduced / moved, which I’ll leave alone.
  • C:\Windows\Installer – Over 3GB, program installers that can be moved safely and easily.
  • C:\Users\Administrator\AppData\Google – About 0.8GB, Google products data such as those by Chrome.
  • C:\Users\Administrator\AppData\Mozilla – About 1.6GB, Firefox profiles and browser data.
  • Some folders in C:\Program Files – I wish I could move this entire folder but I couldn’t. I could only move some of the folders such as “Microsoft Games”.
  • Some folders in C:\Program Files (x86) – Same as above.

This is just my case. You may as well find totally different folders that need to be moved. After you have found something, proceed to step 2 to duplicate them elsewhere.

Step 2 – Copy these folders to another drive that is much more spacious!

This one is easy. Just perform the omnipotent Ctrl+C and Ctrl+V combination to copy these folders to another drive. In this example, we’ll copy these folders to their new destination – D:\C_DRIVE:

  • C:\Windows\Installer –> D:\C_DRIVE\Windows\Installer
  • C:\Users\Administrator\AppData\Google –> D:\C_DRIVE\Users\Administrator\AppData\Google
  • C:\Users\Administrator\AppData\Mozilla –> D:\C_DRIVE\Users\Administrator\AppData\Mozilla

After the duplications, proceed to step 3.

Step 3 – Delete original folders and create the symbolic links

Now we need to delete the original folders in drive C. Some files and folders in Windows 7 are owned by TrustedInstaller while some by SYSTEM that cannot be easily deleted by Administrator. To delete them, you have to acquire SYSTEM privileges to do this, because the most privileged account in a Windows 7 computer is SYSTEM, not Administrator.

To do this, just create a file named syscmd.bat and put in the following commands:

sc Create SysCMD binPath="cmd /K start" type=own type=interact
sc start SysCMD

Double click to run the file syscmd.bat and you will be prompted by a dialog with 2 buttons, click the top one to view the interactive message.

And you will enter a command line interactive mode with SYSTEM privileges which basically grant you full rights to the computer – you can do whatever you want now.

Run these commands one by one – line by line, you type in the command and hit enter:

rmdir /s /q C:\Windows\Installer
rmdir /s /q C:\Users\Administrator\AppData\Google
rmdir /s /q C:\Users\Administrator\AppData\Mozilla

Now that the original folders are deleted, we’ll make symbolic links in the same name so that any requests for these folders are correctly diverted to those on the D drive:

mklink /D C:\Windows\Installer D:\C_DRIVE\Windows\Installer
mklink /D C:\Users\Administrator\AppData\Google D:\C_DRIVE\Users\Administrator\AppData\Google
mklink /D C:\Users\Administrator\AppData\Mozilla D:\C_DRIVE\Users\Administrator\AppData\Mozilla

That’s it.

After these 3 steps, my C drive is successfully reduced in size by 5.4GB. I can move more folders to further enlarge / extend the free space but for now, it’s good enough for me.

Feel free to leave any comment about how you are doing with this approach.

Loading...