SQL 2000 Management from Windows 8.1

I maintain a legacy application running on SQL 2000 under Server 2003. I’m migrating my VB6 development environment from XP to 64-bin Windows 8.1. I wanted to continue using SQL Management Utilities from the new dev machine.

Actually, SQL 2000 isn’t supported on Vista or above (http://blogs.msdn.com/b/sqlreleaseservices/archive/2009/10/23/sql-server-on-windows-7-and-windows-server-2008-r2.aspx), but why should that stop me?

Management Utilities Setup

Install Client Tools

We want a drive mapped to the root of the installer folder.  This simulates having a CD inserted where autorun.exe is at the root of the CD.  The examples below assume the "SQL 2000 Developer Edition" install files (e.g. from MSDN) have been copied to \\FILESERVER\S.

From an administrative command prompt:

  net use X: "\\FILESERVER\S\SQL 2000 Developer Edition\Main"
  X:
  setup.bat

In the Program Compatibility Assistant, Choose "Run the program without getting help" (twice).

Wizard options:

– Computer Name:  Local Computer
– Installation Selection:  Create a new instance of SQL Server, or install Client Tools.
– User Information:  name, company
– Software License Agreement:  Yes
– CD-Key:  [your key]
– Installation Definition:  Client Tools Only
– Select Components:  no change
– Start Copying Files:  Next

"Setup is installing Microsoft Data Access components (MDAC)".  Hopefully this doesn’t clobber the later version that should be in Windows 8.1.

Enterprise Manager starts okay at this point.

Unmap the drive:

  C:
  net use X: /delete

Install SP4

Assuming SP4 is in the following folder, from an administrative command prompt:

  net use X: "\\FILESERVER\S\SQL 2000 Developer Edition\Updates\SQL 2000 SP4"
  X:
  setup.bat

In the Program Compatibility Assistant, Choose "Run the program without getting help" (twice).

Installs MDAC again.

Unmap the drive:

  C:
  net use X: /delete

Install Books Online update

If you have the January 2004 update to SQL 2000 books online, run that MSI installer, e.g.

SQL2000.BooksOnline.Jan2004Update.sqlbolsetup.msi

Choose the option to upgrade the existing Books Online installation.

Install Hotfix Corresponding to Servers – Failed

The latest SQL 2000 build is 8.00.2305 dated 8/14/2012.  This is installed on my server.

Not sure if a hotfix is needed on the client tools but thought I’d try it.

Long story short:  the installation failed after several attempts:

  "Setup Error:  Not enough storage is available to process this command."
  "Setup Error:  installation did not complete."

I will ignore this for now since I don’t think the hotfix has to be installed when only the management tools are in use.

Using SQL 2000 under Windows 8.1

SQL Enterprise Manager starts itself as administrator, no doubt for UAC reasons. This means you will not, by default, have access to your mapped network drives from within Enterprise Manager (e.g. for exporting a database script).

To get access to the P: drive from within Enterprise Manager , from an administrative command prompt:

  net use P: \\FILESERVER\Projects /PERSISTENT:YES

This does not persist after logoff.  It might be easier to just use UNC paths to \\FILESERVER\Projects from inside Enterprise Manager. Enterprise Manager does remember the previous UNC path used, even after logooff.

Those Pesky Aliases

I use aliases to connect to servers whose names have changed or that have alternate ports. I set up the aliases in Client Network Utility exactly as they were under XP, but SQL refused to pick them up. I finally realized the problem: The shortcut to the Client Network Utility that that is installed with SQL 2000 Client Tools points to C:\Windows\system32\cliconfg.exe. That’s right, the Client Network Utility is actually part of the operating system. See this MSDN blog post.

The “gotcha” is that on a 64-bit version of Windows 8.1, that is the 64-bit version of the Client Network Utility, which updates this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

But SQL 2000 is running as a 32-bit app. The solution? Run the 32-bit Client Network Utility here:

C:\Windows\SysWOW64\cliconfg.exe

which will update this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

You can also copy values under that key from one registry branch to the other. Once I did that, aliases worked fine. You’ll probably want to modify the default shortcut (in C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server) to point it to the 32-bit cliconfg.exe.

Thanks to this post for pointing out the locations of the two registry keys, and this post for identifying the two executables.