http://www.microsoft.com/TechNet/boes/bo/sql/technote/admn_a.htm (PC Press Internet CD, 03/1996)
| Updated: March 14,1996 |  Go To TechNet Home Page |
Note
The information in this paper addresses administration and connectivity
topics. For information about language, statement, query, and
other compatibility issues between SQL Server 6.0 and SQL Server
4.2 databases, see the compatibility information provided in the
Microsoft SQL Server Transact&hyph;SQL Reference.
Servers and Clients
Using SQL Administrator and SQL Object Manager
SQL Enterprise Manager Capabilities
Running SQL Server 4.2 Alongside 6.0
Loading SQL Server 4.2 Databases
Data Transfer
Using Extended Stored Procedures
Support for New Features
Both types of client can connect to both types of server. SQL Server 6.0 clients can connect to SQL Server 6.0 and 4.2 servers, and SQL Server 4.2 clients can connect to SQL Server 6.0 and 4.2 servers.
To a SQL Server 4.2 server, a SQL Server 6.0 client appears to be a 4.2 client. To a SQL Server 6.0 server, a SQL Server 4.2 client appears and behaves as a SQL Server 4.2 client. A SQL Server 4.2 client does not support new SQL Server 6.0 features. For example, the decimal datatype is converted to float. For more information, see "Support for New Features," later in this paper.
SQL Server 6.0 and SQL Server 4.2 servers may be set up as remote servers to each other. Server-to-Server RPC operates normally between SQL Server 6.0 and SQL Server 4.2 servers.
ODBC connections between 6.0 and 4.2 function normally.
SQL Administrator and SQL Object Manager are no longer provided. They have been superseded by SQL Enterprise Manager.
Servers or clients that still have old copies of SQL Administrator or SQL Object Manager can still use those tools to administer a SQL Server 6.0 server, but only after that server has run two scripts--ADMIN60.SQL and OBJECT60.SQL--that upgrade its stored procedures to allow SQL Administrator and SQL Object Manager to administer it. However, you will notice a few differences when using these tools to administer a SQL Server 6.0 server, as compared to administering a SQL Server 4.2 server.
When an existing SQL Server 4.2 server is upgraded to SQL Server 6.0, the old copies of SQL Administrator or SQL Object Manager are left in place, and the necessary upgrade scripts are automatically run by the setup program. You do not need to take any action to allow an upgraded server to be managed by those tools.
For new SQL Server 6.0 installations, the two upgrade scripts must be run if other servers or clients that still have SQL Administrator and SQL Object Manager installed will be used to administer the new SQL Server 6.0 server.
To allow this server to be administered by the SQL Administrator utility, from the \SQL60\INSTALL directory, open and run the script ADMIN60.SQL.
The following table indicates which SQL Enterprise Manager commands can be used to administer SQL Server 6.0 and SQL Server 4.2 servers.
|
Menu |
Command |
6.0 |
4.2 |
Comment |
|---|---|---|---|---|
|
File |
all commands |
X |
X |
|
| View |
all commands |
X |
X |
|
| Server |
Register Server |
X |
X |
|
| Server Groups |
X |
X |
| |
| Alerts |
X |
| ||
| Configurations |
X |
| ||
| Replication Options |
X |
| ||
| Current Activity |
X |
| ||
| Error Log |
X |
| ||
| Replication Configuration |
X |
| ||
| Tools |
Task Scheduling |
X |
| |
| Query Analyzer |
X |
X |
| |
| Backup/Restore |
X |
X |
SQL Server 4.2 servers cannot use backup/restore features that are new for SQL Server 6.0. | |
|
| Set Server/Database |
X |
X |
|
| Preferences/Configure |
X |
X |
| |
| Manage |
Logins |
X |
X |
|
| Devices |
X |
X |
| |
| Databases |
X |
X |
| |
| Replication |
X |
| ||
| Users |
X |
X |
| |
| Groups |
X |
X |
| |
| Tables |
X |
X |
Constraints for declarative referential integrity are not supported for SQL Server 4.2 servers. | |
|
| Indexes |
X |
X |
|
| Triggers |
X |
X |
| |
| Stored Procedures |
X |
X |
| |
| Views |
X |
X |
| |
| Rules |
X |
X |
| |
| Defaults |
X |
X |
| |
| User Defined Datatypes |
X |
X |
| |
| Object |
Permissions |
X |
X |
REFERENCES permission is not supported for SQL Server 4.2 servers. |
|
| Dependencies |
X |
X |
|
| Generate SQL Scripts |
X |
X |
"Table DRI" checkbox is disabled for SQL Server 4.2 servers. | |
|
| Drop |
X |
X |
|
| Rename |
X |
X |
| |
| Window |
all commands |
X |
X |
|
| Help |
all commands |
X |
X |
|
When running both SQL Server 4.2 and SQL Server 6.0 servers at the same time on the same computer, to be supported by the graphical tools, the servers must use different names. For example, if you want to use SQL Transfer Manager to transfer data between the two, each must use a different server name.
You change a server name by using sp_addserver with the local option. For example:
sp_addserver POODLE, local
This changes the server name in the master.dbo.sysservers table and sets the @@servername global variable. The server must be restarted for the change to take effect.
Note
The server name is only an issue if the two SQL Server installations are run simultaneously. You can have SQL Server 4.2 and SQL Server 6.0 installed on the same computer and have both use the same server name, as long as you run them one at a time.
For information about setting up a side-by-side installation, see Microsoft SQL Server Setup. For instructions on using sp_addserver, see the Microsoft SQL Server Transact&hyph;SQL Reference.
You can dump a SQL Server 4.2 user database and load it on a SQL Server 6.0 server (although you cannot do this for master.) However, this capability is one-way only, as you cannot dump a SQL Server 6.0 database and load it on a SQL Server 4.2 server.
When loading a SQL Server 4.2 user database on a SQL Server 6.0 server, all necessary comments must exist in syscomments. SQL Server cannot upgrade all objects from SQL Server 4.2 databases to SQL Server 6.0 if you have deleted data from the text column in the syscomments table.
Also, you may encounter conflicts with the new SQL Server 6.0 keywords. If you do, after loading the database, you can change the conflicting table or object name and alter the applications that access those objects so that they use the new names. For a list of the new keywords, see the Microsoft SQL Server Transact&hyph;SQL Reference.
Note
For information about language, statement, query, and other compatibility issues between SQL Server 6.0 and SQL Server 4.2 databases, see the compatibility information provided in the Microsoft SQL Server Transact&hyph;SQL Reference.
You can use bcp to transfer a native or character format file from SQL Server 4.2 into a SQL Server 6.0 table of the same definition. You can also transfer in the other direction, but if you are bulk copying a table with the new SQL Server 6.0 datatypes into a SQL Server 4.2 table, you must use character mode bcp. The destination table would use only SQL Server 4.2 datatypes. Identity column values and the identity property are not transferred.
You can use SQL Transfer Manager to transfer objects and data between SQL Server version 4.2 and SQL Server version 6.0. When moving objects data from SQL Server 4.2 to SQL Server 6.0, some stored procedures or views may contain Transact&hyph;SQL statements that are no longer accepted in version 6.0, and some 4.2 object names may now be reserved words in 6.0. In these cases, the transfer will fail for these objects and will not complete correctly for objects that depend on them. For more information, see the compatibility information in the Microsoft SQL Server Transact&hyph;SQL Reference.
When using SQL Transfer Manager to transfer objects and data from SQL Server 6.0 to SQL Server 4.2, you cannot include 6.0-specific datatypes in the source objects, and 6.0-specific schema additions such as identity columns and declarative referential integrity will not be included in the transfer.
Note
For a list of new SQL Server 6.0 keywords and for other information about language, statement, query, and other compatibility issues between SQL Server 6.0 and SQL Server 4.2 databases, see the compatibility information in the Microsoft SQL Server Transact&hyph;SQL Reference.
If you are using extended stored procedure .DLLs written for previous versions of Microsoft SQL Server, you will need to rebuild those extended stored procedures by using the new SQL Server .DLLs. For more information, see Microsoft SQL Server Programming Open Data Services.
For a summary of new SQL Server 6.0 features, see the Release Notes provided with SQL Server Books Online.
As should be expected, SQL Server 4.2 servers do not support new SQL Server 6.0 features such as constraints, new keywords, the identity property, the decimal, numeric, float(b), and double precision datatypes, expanded ANSI SQL support and language enhancements, server cursors, constraints, and so on. For information on these features, see the Microsoft SQL Server Transact&hyph;SQL Reference.
SQL Server 4.2 servers are not supported as replication participants. They cannot act as publication servers or distribution servers and are not supported subscribers.
 |
Click Here to Search TechNet Web Contents | TechNet CD Overview | |
Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information. |
| Go To TechNet Home Page |
©1996 Microsoft Corporation |  Go To Microsoft Home Page |