Upgrading to SQL Server 7.0

Answers from Microsoft

Welcome to the SQL Server Q & A column. I'm Richard Waymire, a Microsoft program manager in SQL Server development. I'm in charge of the technical accuracy of Microsoft's documentation and white papers, security, and setup for SQL Server 7.0. I'm looking forward to your SQL Server questions (please send them to [email protected]). Although I spend most of my time working on SQL Server 7.0, I'll be happy to take your questions about previous releases. In my life before Microsoft, I was a trainer for SQL Server 4.21a, SQL Server 6.0, and SQL Server 6.5.

Can I upgrade to SQL Server 7.0 from SQL Server 4.21?

SQL Server 4.21 and earlier releases will not support upgrading to SQL Server 7.0. You must first upgrade to SQL Server 6.0 or SQL Server 6.5. I recommend SQL Server 6.5 with Service Pack 4 (SP4) or later. After you are running SQL Server 6.x, upgrading is straightforward. You have two choices for upgrade; you can upgrade immediately after setting up SQL Server 7.0, or at any time after you install SQL Server 7.0. In the Microsoft SQL Server Switch program group in your Start menu, you will find a shortcut to the SQL Server Upgrade Wizard. Simply run the wizard to begin your upgrade. This shortcut won't exist on Windows 9x platforms because SQL Server 6.x doesn't run on those platforms.

How can I prepare my SQL Server 6.x installation for the upgrade to SQL Server 7.0?

Read the SQL Server 7.0 Books Online (BOL) and review the "SQL Server Backwards Compatibility Details" section, which rates specific changes by their impact. Using this guide, you can change your SQL Server 6.x databases to make them fully compatible with SQL Server 7.0. You will need to change most of your administrative database scripts, particularly if they create devices or databases or if they manage integrated security. Most database scripts won't be affected; however, if you create objects in a database, and you aren't the systems administrator, you need to script your objects as dbo.object_name. For example, if I log in as rwaymire as a member of the db_owner database role, and I type

create table mytable (c1 int not null)

I'm telling the system to create table richard.mytable (c1 int not null). In previous SQL Server releases, if I am aliased to the dbo user and I type this statement, the system creates dbo.mytable. In SQL Server 7.0, you must code the object with the owner if you want dbo to own the object. Thus, the correct SQL Server 7.0 create statement reads

create table dbo.mytable (c1 int not null)

Why didn't the stored procedures that access system tables migrate during my upgrade?

The inability to migrate stored procedures occurs because the SQL Server 6.x system tables have changed significantly in SQL Server 7.0. Many system tables are no longer tables; they are views of new system tables. The SQL Server team felt a safer tactic than automatically upgrading these stored procedures was to force a re-examination of stored procedures that use system tables directly when you upgrade to SQL Server 7.0.

Will SQL Server 7.0 read backups from SQL Server 6.5?

No. The backup formats of previous releases are completely different from SQL Server 7.0's backup formats. The version-upgrade utility is the proper mechanism for upgrading a SQL Server 6.x database to SQL Server 7.0. To migrate a database from SQL Server 6.x to version 7.0, ensure the database is working and then run SQL Server Upgrade Wizard to upgrade the database. Microsoft has no plans to make a SQL Server 7.0 utility to read SQL Server 6.x backups.

How do I make system databases and objects appear in SQL Server Enterprise Manager?

Right-click your server, and select Edit SQL Server Registration. Select the Show System Databases and System Objects check box at the bottom of the dialog box. You will then see the system databases (Master, Model, Tempdb, and MSDB), and all system tables in every database.

Can I run SQL Server 6.x and SQL Server 7.0 on the same computer at the same time?

No. You can install SQL Server 6.x and SQL Server 7.0 on the same server, but you cannot simultaneously run the two versions. This limitation occurs because the two versions use the same service name (MSSQLServer) and Registry keys. You can probably make SQL Server 6.x and SQL Server 7.0 run simultaneously, but Microsoft doesn't support this approach. Simultaneously running the two versions is not a good idea because of the resulting demand on memory. SQL Server 7.0's backward compatibility modes let you run SQL Server 7.0 and emulate most of the critical behavior from SQL Server 6.x. Microsoft provides vswitch.exe, a utility that lets you switch between a previous SQL Server 6.x installation and SQL Server 7.0. However, Microsoft recommends you upgrade to and remain on SQL Server 7.0.

Which version of Open Database Connectivity (ODBC) drivers do I need to talk to SQL Server 7.0?

All SQL Server 7.0 tools use either ODBC or Object Linking and Embedding Database (OLE DB) to access SQL Server except isql.exe, which still uses DBLib. Therefore, you must have up-to-date ODBC drivers to use SQL Server 7.0 tools, which use ODBC 3.70 drivers. These new drivers understand how to communicate with SQL Server 7.0, and fully support features such as Unicode. The drivers are fully backward compatible, so you can talk to earlier releases of SQL Server and SQL Server 7.0 with the ODBC 3.70 drivers. You cannot use the ODBC 2.x drivers successfully on the SQL Server 7.0 computer. On client computers, you can still use the older ODBC drivers, but you will lose some functionality such as sending and receiving Unicode datatypes. Also, Microsoft is not shipping any 16-bit ODBC drivers for SQL Server 7.0, but the SQL Server 6.5 drivers will still work.

Can you please explain how to install the client utilities for SQL Server 7.0?

Start setup as you would for an ordinary server install, and select Custom for the setup type. On the Select Components window, which you can see in Screen 1, clear the Server Components check box. If you leave the other Select Components check boxes selected, SQL Server installs the client utilities, Books Online (BOL), and client connectivity components. You will need the client connectivity components and the management tools for the tools to function properly when you connect to SQL Server. The client connectivity components include the ODBC and OLE DB drivers.

What is TDS? What does it mean when I get the error message Error in TDS stream

A Tabular Data Stream (TDS) is the underlying communications protocol SQL Server uses to communicate between clients and the server. TDS is a proprietary, undocumented interface that was part of the original Sybase SQL Server (Microsoft has modified TDS for SQL Server 7.0). If you receive an Error in TDS stream message, or a similar message, a disconnect request from the server is usually the cause. A server error message with high severity will force a disconnect request. Severity errors 20 through 25 automatically terminate the client's connection to SQL Server.