Registry Dependencies

Many features in SQL Server depend on the Windows registry. If you uninstall SQL Server, the uninstall process doesn't remove all the registry entries. If you reinstall SQL Server, those remnants of the past configuration are still around. (Reinstalling the SQL Server tools is quite common, since developers are constantly changing their machines' environments and breaking components as they install new components.)

In the Trenches

Registry issues can appear in very unusual places. For example, a common problem that occurs when you insert data from Excel using DTS has a very obscure registry fix. The problem (which is discussed in Knowledge Base article Q281517) occurs if you try to load Excel spreadsheets that have columns larger than 255 characters. You receive the following error in DTS:

"Error at source for row number 9. Errors encountered  so far in this task :1" General Error: -2147217887(80040E21) Data for Source Column 3('Col3') is too large  for the specified buffer size.

The problem occurs because, by default, the Jet driver scans only the first 8 rows to determine the data types. If it finds a column with more than 255 characters in the first 8 rows, it recognizes the fact, and you won't experience the problem. However, if none of the first 8 rows contain more than 255 characters, it assumes that no column will contain this number of characters. When, after row 8, such a circumstance exists, an error message appears.

To fix the problem, you can shift one of your larger rows to the top of your file so it is scanned, or you can change the registry to avoid the problem. The registry change you can make is in the key HKEY_LOCAL_MACHINE\ Software\Microsoft\Jet\4.0\ Engines\Excel. The data item is TypeGuessRows. If you change its value to 16384, every row is scanned, which ensures that you won't see an error, but this slows the loading of the spreadsheet significantly. Enter a number you think provides the optimum number of rows to scan for the spreadsheets you use.

Rebuilding SQL Registry Entries After Reinstalling Windows

If you reinstall Windows without formatting the machine to repair a problem, a complete reinstall of SQL Server is not necessary. You can rebuild the SQL Server registry to return it to its original state. To do this, use the following steps:

1. Put the appropriate SQL Server 2000 installation CD back into the drive and rerun setup.

2. Go through the initial welcome screens, and on the Installation Selection screen choose the Advanced Options item, then click Next.

3. Select Rebuild Registry and click Next.

4. Step through the rest of the installation, selecting the exact same options you selected during the initial installation.

   Note 

   If you don't know the initial settings, you have to uninstall and reinstall SQL Server, and then reattach your databases.

5. The installation program rebuilds the registry.

You must reboot the server after the process completes.

Using Registry Server Listings

SQL Server stores the list of servers in the registry, and the data is displayed in the drop-down list in the SQL Server Utilities dialog box. You may want to preload a number of these servers on a new administrator's workstation to make it easier for users to select a server. You can also delete entries for servers that no longer exist.

The registry key in question is HKEY_CURRENT_USER\Software\Microsoft\ Microsoft SQL Server\80\Tools\Client\PrefServers. Two data items in that key are of interest:

• ServerN (where N represents the server number) contains the server's name, alias, or IP address.

• UserN (where N corresponds with the ServerN entry) contains the default login for the server when you select the server from the drop-down box in the SQL Server Utilities GUI.

To add a new server, create new data items for both server and user, and enter the appropriate string for their values. If you already have a Server5, your new entries are Server6 and User6. If you delete any data items, be sure the ServerN and UserN values are sequential. For example, if you delete Server0 and User0, rename the other values or else you won't be able to see them in the drop-down box.

Note 

When you connect to a new server in the SQL Server utilities, the server name and user name that you connected with are automatically added to the registry. This information is retained in the registry if you uninstall SQL Server 2000.