The Ji Village News

季庄新闻–Haidong Ji’s Blog

If you are running Sql Server 2005 on Windows Server 2003 SP2, you may get this message from Sql Server native client connection:

An existing connection was forcibly closed by the remote host

This is most likely caused by a “feature” called TCP Chimney offloading. On the server, run:

Netsh int ip set chimney DISABLED

should fix your problem. It seems Visual Basic 6 (VB6) based applications and Heat Call Logging are particularly susceptible to this problem.

KB article here: http://support.microsoft.com/default.aspx/kb/945977

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.

I recently helped a client to get the WSS 3.0 documents out, as the client’s SharePoint is broken and we couldn’t get it fixed in short time, but they really want the Word, Excel, and pdf documents out of there.

WSS 3.0, Windows SharePoint Services, uses Sql Server for data storage. The Sql Server version WSS uses is the Embedded Edition. By default, microsoft##ssee is what you will see listed in services. Usually, Sql Server client tools are not installed on the server. Therefore, it is difficult to control those files. So we installed Sql Server Management Studio on this box.

Then we had a hard time connecting to the instance. It turned out we can only connect to the SharePoint WSS 3.0 databases via named pipe. I used Windows authentication and put in \\.\pipe\mssql$microsoft##ssee\sql\query as server name, and finally got in.

For SharePoint, there are at least 2 databases involved: the configuration database, and the content database. By default, they are located at c:\windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

I then moved the content database to a regular Sql Server 2005 server, and decided to write a little program to suck the documents outside of the database. It turns out that somebody has already written the program. Follow the link here to get the C# source code of the program. Note that the sql statement used should be the one listed below, as mention at comment No. 9 of that post:

com.CommandText = “select [AllDocs].[DirName], [AllDocs].[LeafName], [AllDocStreams].[Content] from [AllDocs],[AllDocStreams] where (LeafName like ‘%.doc’ or LeafName like ‘%.xls’ or LeafName like ‘%.pdf’ or LeafName like ‘%.ppt’) and [AllDocStreams].[Content] is not NULL and [AllDocs].[Id] = [AllDocStreams].[Id]“;

Enjoy!

A while ago I wrote an article on code generation using Sql. The script I gave in that article can generate BCP (in and out) scripts automatically.

I helped out a friend recently, and thought I would share the WMI/VBScript I cooked up with you. This script assumes that all files within a folder need to go to one table, and the layout of the comma delimited files match the structure of the table. It also uses trusted authentication. It can be easily customized for more advanced BCP tasks. Enjoy!

strComputer = "."
'Directory name. Don't put the back slash here
strDirectory = "C:\MyDirectoryWhereCsvFilesAre"
'Instance Name
strInstance = "MySqlServerInstance"
'Table Name
strTableName = "MyDb.dbo.MyTable"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set oShell = CreateObject("WScript.Shell")

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='" & strDirectory & "'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In colFiles
    strCommand = "bcp " & strTableName & " in " & chr(34) & strDirectory & "\" & objFile.FileName & "." & objFile.Extension & chr(34) & " -c -T -S " & strInstance & " -t " & chr(34) & "," & chr(34)
    'msgbox strCommand
    oShell.Run strCommand, 0, True
Next

I am playing with VMWare Converter and VMWare Server. Both are available for free. You do need to register to get the download link and serial numbers. Fake name, address, and email will do.

VMWare Converter is pretty neat. It can suck the gut out of a physical machine, while it is running, and create a virtual machine based on it. I’ve done that and was reasonably pleased with the result. You can also supply it with a virtual machine generated using Microsoft Virtual PC (7 and higher) or Microsoft Virtual Server, and it will convert that into a VMWare virtual machine, although I didn’t try that myself. I may do that in the future.

Note, Converter can only convert Windows boxes, no Linux, no BSD, and such, as far as I can tell.

Here is a note for myself and whoever finds it useful.

1. Downloaded Converter;
2. Upon install, Converter told me that I could use VMWare Converter boot CD, if I want to convert the machine I was installing Converter on, to a virtual machine. Some reading gave me the impression that the image converted using the Converter boot CD only works under VMWare Server Enterprise, so I didn’t take that route. I could be wrong on it though. Plus, VMWare didn’t make it very easy to get the Converter CD;
3. Installation was easy enough. When it was done, I fired it up, and clicked Import Machine button. I then picked the local machine as the source machine. The program then asked for destination, I picked a folder on the local machine. I said yes to the question below.

—————————
VMware Converter Import Wizard
—————————
The selected destination is the same as the source machine. Are you sure you want to clone the machine onto itself?
—————————
Yes No
—————————
4. Somewhere along the way, I was asked if I want to give the virtual machine a name and supply sysprep files. I intended to run this virtual machine on the host on which it was based, so I gave it a different name. I think it also asked for organization name and such. Anything will do here;
5. Locating the deploy.cab files for sysprep created a little hassle. The converter wizard provided some hint via a message box, telling me to search for “sysprep Windows XP SP2″ and the like in Microsoft’s site. In my case, I searched for “sysprep Windows 2003 Enterprise R2 SP2″, which led me to this site. I downloaded and installed it, but it didn’t seem to do anything, and didn’t provide deploy.cab that I needed;
6. After checking around for a while, I decided to download the SP2 ISO image from Microsoft, and used 7zip to get deploy.cab out of the image. I then expanded deploy.cab into a folder;
7. Clicking through the wizard, it proposed 3 NIC cards for the machine. I took that, and they were all bridged Ethernet cards. Finally it started importing. On my machine of Windows 2003 Server Enterprise R2 SP2 with Sql Server 2005 and Visual Studio 2005 installed, the process took 29 minutes;
8. Afterwards, I clicked the Configure Machine button. It seemed that this process gave the virtual machine its name, and somehow hooked up with sysprep files, or not. It took less than 30 seconds to finish;
9. Downloaded and installed VMWare Server. Part of it was the web management interface, or something like that. It needs IIS, the host didn’t have it and I didn’t intend to install it, at least for now. So I skipped that component;
10. Double clicked the vmx file to open it. Click the Run button to run the machine. The virtual machine booted up, but then rebooted, at this stage I saw the sysprep thing running. A few reboots later, it became stable and was functional. It was a bit sluggish, especially inside the virtual machine, but it worked;
11. I then downloaded a LAMP virtual appliance from here. It is light, and it runs pretty well. To use the words of International Man of Mystery: Groovy, baby!

I have a home network, which consists of a Linksys Wireless-G broadband router. This router gives out dynamic IP addresses to laptops and workstations. I remember that when I had Windows 2003 standard installed on one workstation, I could remote into it using Terminal Service.

I’ve since installed Windows 2003 Enterprise R2 SP2, and my remote desktop stopped working. I could not even ping this workstation’s IP address from my laptop, which was really frustrating. After some research, here is what I did to make it work, feel free to make suggestions, as my way may not follow security best practices. Someday I need to take a computer networking class.

Go to services on the machine, find Windows Firewall/Internet Connection Sharing (ICS), disable it.

Now onto some fun with virtuization, MySQL, Oracle, Sql Server 2008, and full text search stuff.

I purchased a laptop soon after this post. It was a HP Pavilion dv6253cl Notebook, pre-loaded with Windows Vista Home Premium Edition. This machine has 2 gig of RAM, 120 gig of hard disk space, and AMD Turion Dual Core processor. I bought it at Costco, 1000 bucks plus tax. It has a built-in card reader and webcam.

Vista is a huge disappointment. Here are a few problems I had:
1. A lot of device drivers are either not available, or do not work;
2. I am logged in as administrator, yet Vista repeatedly stops me from doing things I need to do, unless I confirm and click a Yes button. I heard it can be turned off, but I do not have time or too lazy to google it. It is a total waste of my time;
3. USB thumb drive does not work right. Sometimes I couldn’t even copy files from Vista to the USB drive;
4. I had trouble even moving files around. Granted, the files I move around are in the C: root folder or Program Files folders, but I AM AN ADMIN, let me do what needs to be done;
5. Little annoyances like I couldn’t sort files by type in my pictures folder. I am sure there are others, I just don’t remember now.

I’ve been thinking about moving off of Vista for the last couple of weeks. Today, I finally bit the bullet.

The first thing I tried is to load Ubuntu. Yes, currently I work primarily on the Windows platform, but I have strong interest in Linux. I reasoned I could use Linux virturalization software and create Windows virtual machines. I know there are people who use Mac with Parallel virturalization to run Windows on top, but Mac is too expensive for me.

I tried the latest Ubuntu, version 7.04. It actually didn’t start on the laptop, complaining some BIOS bug and firmware issues. Next I tried Ubuntu 6.01, an older version I happened to have. It didn’t start either. I was pretty disappointed.

Finally, I had to settle for Windows XP SP2. That cost more than half day. Now I almost have my system back. One big bummer is the built-in webcam is not working on XP. I do use the webcam with Skype, primarily for chatting with my family while on the road, so they can see me. It is a big plus, especially for my son. The webcam worked on Vista, but I would hate to go back to Vista just for that. Looking around for webcam drivers cost me at least 3 hours, to no avail!

The webcam driver issue seems to be a common one for HP Notebooks. HP doesn’t have the driver or the one it has don’t work. In fact, this afternoon I read tons of complaints with HP’s existing webcam drivers. I’d appreciate it if my readers have any leads on this.

Here is a note for myself, in case for whatever reason I need to rebuild XP SP2 on this laptop again. All links were obtained through HP’s web chat support.

Windows XP device driver links for HP Pavilion dv6253cl Notebook:

Broadcom Wireless LAN Driver:
ftp://ftp.hp.com/pub/softpaq/sp33001-33500/sp33008.exe

HP Quick Launch Buttons:
ftp://ftp.hp.com/pub/softpaq/sp33001-33500/sp33258.exe

Ricoh 5-in-1 Card Reader Driver:
ftp://ftp.hp.com/pub/softpaq/sp33001-33500/sp33413.exe

NVIDIA GeForce Series Video Driver:
ftp://ftp.hp.com/pub/softpaq/sp33501-34000/sp33537.exe

NVIDIA nForce Chipset Driver and Network Controller:
ftp://ftp.hp.com/pub/softpaq/sp33001-33500/sp33411.exe

link for Microsoft UAA driver.
ftp://ftp.hp.com/pub/softpaq/sp33501-34000/sp33867.exe

Here is the web link for audio drivers.
ftp://ftp.hp.com/pub/softpaq/sp34001-34500/sp34200.exe

Here is the link for Webcam driver, but it did NOT work
ftp://ftp.hp.com/pub/softpaq/sp35001-35500/sp35414.exe

Below is a piece I wrote for last week’s Sql Server PASS newsletter. The editor adjusted the title to The Times, They Are Changin’. I thought that change lost the humor I try to convey Also, it is amazing how close Brian and I think on this one. We didn’t talk to each other, but wrote very similar posts for this subject around the same time.

In the US, Energy Policy Act of 2005 was passed and signed into law in 2005. One provision of the law expanded daylight saving time period by 4 weeks, effective in 2007. As a result, computer operating system in the US needs to be patched to reflect this change. The patch is fairly simple to apply for servers, mostly in the form of registry changes. To get help on how to adjust this setting for your Windows servers, you can visit the site here.

Fortunately for Sql Server, once the underlying Windows time setting has been adjusted, there is almost no additional work to do on Sql Server. The exception is Sql Server Notification Services. Notification Services for both Sql Server 2000 and 2005 need updates to accommodate this time change. You can follow this link here for instructions.

For non-US readers out there, this change may not affect you, so let me introduce you to MSDN’s video series for Sql Server 2005 Express Edition. These are database courses delivered in video format. It looks very promising. You can get it here.

I talked about one issue when setting up Sql cluster here. Recently I came across another problem while setting up a Sql Server 2000 cluster on a 2-node Windows 2003 cluster.

The error occurred at the step where you were asked to provide a login that can get into the remote node(s), server(s) where install is not originated from. Below is the error message:

The specified account cannot be validated to have administrator rights on Node2.
 An error occurred: (1203)

No network provider accepted the given network path.

This is most likely caused by the fact that File and Printer Sharing for Microsoft Networks is not enabled. You can verify that by trying to open up \\Node2\c$. You will most likely encounter the “No network provider accepted the given network path”.

To enable that feature, go to your network connection, right click on Properties, and check the corresponding box. Note that for clusters, most likely you will have 2 networks: one public and one private. You need to enable this feature on the public network in order to continue. Reboot is not required.

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