How to port a database from Sybase to SQL Server (1)

Although some differences between Sybase and SQL Server are quite large, for example, stored procedures in the Sybase database management system cannot be compiled on SQL Server, but other differences are not very large. Before this conversion, it is necessary to test the behavior and results of the programming logic in the script file and stored procedure.

In the following sections, we will discuss some major differences between the two database systems. In the planning phase of migration, we must carefully study these differences.

Data compatibility mode

A temporary solution to some compatibility differences between SQL Server 2000 and Sybase is to change the database Compatibility Level in SQL Server to make it consistent with Sybase. Therefore, we can use the sp_dbcmptlevel stored procedure.

The statements and results in the following table show the differences between databases of different versions:

Note:

1. When the compatibility mode is set to 70, the following words cannot be used as object names and identifiers: BACKUP, DENY, PRECENT, RESTORE, and TOP;

2. when the compatibility mode is set to 65, the following words cannot be used as object names and identifiers: AUTHORIZATION, CASCASE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, and WORK.

The syntax of sp_dbcmptlevel is as follows:

Sp_dbcmptlevel [[@ dbname =] name] [, [@ new_cmptlevel =] version]

@ Dbname is the name of the database used to check and change the compatibility level;

@ New_cmptlevel determines whether the database is set to 70, 65, 60, and the default value is NULL ).

For example:

sp_dbcmptlevel pubs

This line of code returns the following results:

The current compatibility level is 70. The current compatibility level is 70)

Now let's take a look at another example:

sp_dbcmptlevel pubs, 65

It returns the following results:

DBCC execution completed. DBCC execution ends)

If DBCC prints an error message, contact the system administrator. We can use rerunsp_dbcmptlevel to verify whether the pubs database has been modified correctly:

sp_dbcmptlevel pubs

It returns the following results:

The current compatibility level is 65. The current compatibility level is 65)

In addition to the above example, the difference in compatibility level also extends to reserved words. Sybase and SQL Server have many reserved words that cannot be used as object names in the database. The reserved words of the two products are similar, but not identical.

Because objects that can be used in Sybase may not be used in SQL Server, it makes it difficult for Sybase to port SQL Server. The following is a list of words that are reserved in SQL Server but not in Sybase.

Note: objects in the Sybase database whose names are the words in the following list must be replaced with other names when transplanted to the SQL Server database.

BACKUP COLUMN COMMITTED CONTAINS CONTAINSTABLE

CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER

DENY DISTRIBUTED FILE FLOPPY FREETEXT

FREETEXTTABLE FULL IDENTITYCOL INNER JOIN