What's in SQL Server 7.0 Service Pack 1

If you, like many of my colleagues, have waited until Microsoft released its first service pack for SQL Server 7.0 to implement the new version of your corporate database, your wait is over. Although Service Pack 1 (SP1) is a huge download, you can order it on CD-ROM and easily implement it at your site. You can find more information about how to get SP1 in the Microsoft article Q232570 "INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0." Also, you can obtain Knowledge Base (KB) articles by writing to [email protected] with up to three article numbers in the subject line.

Related: SQL Server 7.0 Service Pack 2

A New Feature?

The only new feature I see in SP1 is the ability to run SQL Server 7.0 on Windows NT Server Terminal Edition. This option lets you run both SQL Server and Terminal Server on one physical server. Although this arrangement can be useful, imagine how huge the server would have to be to support both applications! Most of you will install only client components of SQL Server to support the connection from applications that run on Terminal Server to SQL Server; SP1 gives you that option. To install SQL Server 7.0 components on top of Terminal Server, you must first install SP4 for Terminal Server. This service pack is specific to Terminal Server and is separate from the Windows NT Server 4.0 Service Pack 4. The exact steps to install SQL Server 7.0 on Terminal Server are in the readme file.

But SP1's purpose is not to introduce new features; it's mainly a bug-fix set. Article Q225019 lists the bugs that SP1 fixes. Fortunately, most of the bugs occur only in specialized areas and don't affect most users. Let's look at some of the bugs in SQL Server 7.0 that SP1 eliminates.

Bug Fixes in SP1

Some of you have seen a problem with Internet Explorer (IE) 5.0 installed on top of SQL Server running on Windows 9x. After installing IE 5.0, you can no longer use tabbed dialog boxes and some wizards. A bug in comctl32.dll causes this problem. Also, you sometimes see unexpected behavior or glitches in some wizards and the query designer. SP1 fixes many bugs of this type. (See articles Q225084, Q207451, and Q198515 for more information.)

Other bugs resulted when Microsoft moved to Unicode. SQL Server 7.0 fully supports Unicode; you can even have tables with Unicode names. A problem arises when you run the Index Tuning Wizard over a table that has a name defined with something other than the Latin alphabet (e.g., Russian or Chinese characters). SP1 fixes this problem, too. (See Q203777 for a full description.)

The SQL Server Profiler is a new kid on the SQL Server block, so it has more bugs than many other tools. For example, a bug described in article Q198521 can make sqlservr.exe stop with the divide-by-zero exception. Additionally, article Q219756 describes a bug that can cause SQL Server to stop responding when you use the SQL Server Profiler or the xp_trace_setqueryhistory extended stored procedure. Microsoft immediately issued a hotfix for the sqltrace.dll file, but a fully tested version of this fix now comes with SP1. In some cases, the Profiler can cause corruption in system areas of memory with sqlservr .exe and sqltrace.dll. (Read the full description in article Q219865.)

Other bugs can cause trouble while SQL Server is running certain transactions. For example, failure to prepare a statement by means of either ODBC or OLE DB incorrectly results in the failure of the entire transaction. SQL Server closes the whole transaction instead of stopping execution of the statement only. (Article Q198265 gives the full details.)

Also, SP1 fixes some bugs in OLAP Services. Some bugs are fairly serious, which causes problems when you add a sum of new values after an incremental update. This bug can cause incorrect results in OLAP.

Subsystem Bugs

The new security subsystem of SQL Server 7.0 also has a few bugs. First, although no known bugs let intruders gain access to sensitive information in your data tables, some existing bugs make database access harder for users with limited permissions. For example, a user without the system administrator role can't run the Full Indexing Wizard for SQL Server without first selecting a database. (See article Q198163 for full details on this bug.) Another problem with the security subsystem prevents an NT group and users of this group from viewing a list of tables for which they have permissions. To solve this problem, you can apply SP1 or use a workaround described in article Q221243.

A problem also exists in the subsystem that provides support for compatibility with older releases of SQL Server. The DTS Export Wizard can cause errors when you try to export a database from SQL Server 7.0 to SQL Server 6.5 (see article Q219425 for an explanation). Also, when you upgrade from SQL Server 6.5, logreader tasks are skipped. This problem is not big, but it can affect Replication Monitoring in SQL Server 7.0.

I've described here only a few of the bugs that the new service pack fixes. I strongly recommend you install it on your servers. (Don't forget to install it on a test server first to ensure that nothing will interrupt work on your production servers.)