The Ji Village News

季庄新闻–Haidong Ji’s Blog

In this blog post, I gave out script to set up database mail for Sql Server 2005, if you already have smtp server up and running.

One thing you need to pay attention to is that Sql Server 2005 logs all those mail messages into msdb database, including mail attachments, if any. You probably can see where I am going now. If the logged mail messages and attachments are not archived or removed properly, you could get space issues in msdb, especially if you have large attachment files.

Here is a possible message you will get in Sql Server 2005 event log:

Could not allocate space for object ‘dbo.sysmail_attachments_transfer’.'PK__sysmail_attachme__7AF13DF7′ in database ‘msdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

This message is somewhat misleading. The reason is that if you go look at sysmail_attachments_transfer, this table alone may not be very big. However, sysmail_attachments, a different table in msdb that holds all mail attachments, can be pretty big.

This is a sample script you can use to schedule a job, that can remove all mail messages (including attachments) that are more than 1 month old. It uses a system stored procedure called sysmail_delete_mailitems_sp in msdb:

declare @CutoffDate datetime
set @CutoffDate = DATEADD(month, -1, GetDate())

exec msdb..sysmail_delete_mailitems_sp @sent_before = @CutoffDate

MSDN has a little more complex way of archiving those messages here.

小孩娘最近买了一些画画用的刷子和涂料。有一个刷子是毛笔，并且有墨。这正是我运笔挥毫的大好时机，也是我儿子第一次接触书法。

我和儿子。照相机的电池不足，所以曝光不是太好：

这张里的“季”是我写的。其余都是儿子的大作。右上角的两个字是日语。据他说是“赤子”(Red baby)的意思。第二个“妈”字没地方写了，用箭头标上去：

Ji, Benjamin, Daddy, Mommy, and Red Baby

绕口令。前两行除第一行的“是”是我写的，其余都是儿子的作品。最后两行是我写的：

Tongue Twister

这张全是我写的。个人认为“爱”字写得最好。因为好久不手写汉字，所以写“喜”的时候望了下面的“口”字。后来越看越不顺眼，回过头来之后给添上了。想来读者可以看出来。还有那个真字也忘了到底是几横，返过想来之后也添上了。

Love, happiness, truthfulness, and kindness

下次到中文书店时一定会买一些真正的文房四宝来，再次让家里人感受到我书法的魅力。

I am taking my time to fix our home PC, but I think I am stuck. So I wonder if my readers could help me out here.

This machine is around 6 years old. It is a HP Pavilion 513x:

It died around a month ago. On startup, it would go into the “cannot boot” page, where you could pick the “last good”, “minimal boot”, “boot without network”, “boot into command line”, etc. None worked. I then used Knoppix to get important files out.

Then it was time to rebuild the system. I got hold of XP SP2 install CD and started the process. I asked the installer to completely reformat the hard drive. After that was done, the install process started copying files. When that was over, it was time to reboot. Normally, after the reboot, the rest of the install process would continue to finish. No big deal, I’ve done that many times before.

This time it was different, however. Upon reboot, nothing would happen. All I saw was a blank screen. I then powered on and off a few more times, still no joy.

The odd thing was, when I powered the machine by pressing down the switch for 10 seconds or so, the machine would go off, but the Num Lock indicator on the keyboard would still be on.

I’d still like to keep using this computer. In fact, I’ve got some Rosetta Stone language software that I’d like to use on this box. If you have some ideas, dear reader, I am all ears.

Service Pack 4 for Sql Server 2000 introduced a bug. After it is installed, if Address Windowing Extentions (AWE) support is enabled, a single instance of SQL Server 2000 can only use a maximum of 50 percent of the physical memory that is on the server.

Microsoft has a hot fix for that problem here. I am happy to report that this hot fix is cluster-aware, based on my experience. You can run it on one node of your cluster, and the changes will be propagated to other node(s) on the cluster.

In my experience so far, all service packs and hot fixes, both for Sql Server 2000 and 2005, are cluster-aware.

In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

This is a little note for myself, when overhead projector is involved. Your milage may vary.

Query Analyzer, options window:

General tab: change Query File Directory to c:\MyPreferredFolder
Fonts tab: Editor -> Arial Black, size 16
Fonts tab: Results Text -> Courier New 18
Fonts tab: Selected Text -> Foreground black, Background yellow

Windows display appearance tab:

Windows and buttons: Windows Classic Style
Color Schema: Windows Standard
Font Size: Extra Large

A couple of things learned while rebuilding my home pc:

1. If you have Windows XP and a CD or DVD rw drive, you do not need Nero, EasyCD Creator, Roxio, or any other third-party software to write stuff to your CD;

2. A free utility for Windows XP, called ISO Recorder, allows you to create a CD image file. It also allows you to expand an ISO image file and write them to a CD.

Here is the long story.

As I mentioned before, my home computer died a few weeks ago. I salvaged important files using Knoppix. Now it is time to put Windows XP SP2 on it so the family can keep using it.

Here is the problem: I have Windows XP SP2 from one expired MSDN subscription I had before, it is on DVD. However, I cannot or don’t know how to change the bios setting so the machine can boot from my secondary DVD-RW drive (let me know if you know how to do it). Originally, this machine came with a CD-RW drive. (I’ve changed the booting sequence so CD-RW is ahead of the hard disk.) I bought a DVD-RW a few years back so we can use DVDs.

Fortunately, my laptop has a DVD-RW/CD-RW drive. I first copied the Windows XP SP2 iso image file from the DVD to the laptop’s hard drive. I then used Windows’ built-in CD writing ability to copy it to a blank CD, hoping that it somehow allows me to expand the image file. It cannot. That leads me to ISO Recorder, which worked beautifully for me.

It appeared that Windows XP SP2 does not have the ability to write to a blank DVD, even if you have a DVD-RW drive. Again, let me know if I am wrong on this. I found the whole DVD-RW business to be pretty confusing, with so many different formats, media, and acronyms flying around.

For reasons not clear to me, our Windows XP machine at home died recently. It just wouldn’t boot, after trying safe mode, last known good, etc.

This machine is an HP Pavilion 513x, around 6 years old. We had a bunch of children’s game software on it, along with other stuff, such as picture and video files, and my better half’s data and documents. Some were backed up, some were not. So it is important to retrieve important files out of it before a total rebuild.

Enter Knoppix. I got a Knoppix 3.6 CD from a Linux class I attended more than 2 years ago. I was able to boot the machine using it. The salvage process then started.

I first used a CD burning software called K3b that came with my copy of Knoppix. It worked pretty well with all my picture and video files. K3b was less successful when I started copying pdf, Word documents, and some other data files. These files spread around under My Documents folder, desktop, and various other folders. I suspect irregular or long file and folder names may have played a role here.

Next I mounted my usb drive. Here is what I did:

su
mount -t vfat /dev/sda1 /mnt/sda1

I then can copy files to my usb drive. When it was full, I did:

umount /mnt/sda1

and then freed up the usb content to other machine and start over. One thing to note is Knoppix can detect the usb drive and will mount it for you, but you cannot write to it when it is mounted automatically for you. You will have to use the command above to make it writable.

This operation does require some functional knowledge of *nix environment. By the way, I also tried Ubuntu 6.0 for file salvage, but Knoppix worked much better.

Lesson learned: back things up periodically, especially when they are important.

我从儿子的周末中文学校里了解到海外中国教育基金会。我参观了他们的一个展览，并且读了他们的网站。我感觉这是一个很有意义的义工团体，特推荐。

我从朋友文心的照片里看到了多背一公斤的照片。网上搜后阅读，我也很喜欢他们的做法。

前天读到刘昕的V2EX网站被粗暴地终止的新闻，感到很不舒服，特在此提供道义的支持。我对V2EX了解地不多，但我读他的博客，也很欣赏他的专一、敬业和高效率。我是他的Lividict粉丝，经常使用，所以要一并感谢他的服务。

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050;

If the statement returns “one row affected”, then the person would know John Doe’s salary. This person can then issue

rollback

If “no row affected” is returned, the person can continue until s/he can find it out.

I did a quick search on ANSI SQL 92 standard, but didn’t find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.