I was involved in a recent project to migrate a Sybase database to Microsoft SQL Server 2000. So the experience I gained by working on this project has led me to write this article to show Sybase database administrators (DBAs) and managers the steps that should be followed to migrate from Sybase databases to SQL Server 2000.
Differences Between Microsoft SQL Server and Sybase Adaptive Server
Although some differences will be quite noticeable, such as a Sybase stored procedure that refuses to compile in SQL Server, other differences are much more subtle. It will be necessary to heavily test the behavior and resultsets of all programming logic in script files and stored procedures prior to completing the conversion.
In the following section I provide a set of core differences between the two databases that must be explored during the critical planning stage.
Data Compatibility Mode Behavior
A temporary solution to some of the compatibility differences between SQL Server 2000 and Sybase is to change the databases compatibility level in SQL Server to match that of Sybase. To make this change, use the sp_dbcmptlevel stored procedure.
The following statements and results are listed in this table to show the difference in the versions.
|
Statement |
Sybase |
SQL Server |
| Select au_fname From authors Group BY au_fname |
All distinct au_fname in Ascending order. |
All distinct au_fname in Inserted order. |
| UPDATE authors SET a.au_fname = “John” |
Au_fname in all rows updated to “John” |
Error |
| CREATE TABLE malls (ID int, test bit) |
Test column(bit) defaults to not NULL. | Test column(bit) determined by session or database settings. |
| ALTER TABLE authors ALTER COLUMN au_lname Varchar(60) NULL |
Invalid syntax. | Changes the au_lname column from varchar(40) to varchar(60). |
| CREATE TRIGGER tu_authors on authors for update as print ‘Update tu_authors’ go |
tu_authors2 overwrites tu_authors |
Both tu_authors and tu_authors2 are created and fire when the authors table is updated. |
| CREATE TRIGGER tu_authors2 on authors for update as print ‘Update tu_authors2’ go |
||
| CREATE PROC test_proc As SELECT au_lname From #archive |
Sybase-error if #archive does not exist |
No warning. |
| SELECT DATALENGTH(‘ ‘) | Returns 1. | Returns 0. |
| SELECT DATALENGTH( N’ ‘ ) | Returns 1. | Returns 0. |
| SELECT LTRIM( ‘ ‘ ) | Returns NULL. | Returns an empty string. |
| SELECT LTRIM(N’ ‘ ) | Returns NULL. | Returns an empty string. |
| select REPLICATE ( ‘123’ ,0) | Returns NULL. | Returns an empty string. |
| select REPLICATE (N’ 123′ ,0) | Returns NULL. | Returns an empty string. |
| select RIGHT(N ‘123’ ,0) | Returns NULL. | Returns an empty string. |
| select RIGHT(‘123’ ,0) | Returns NULL. | Returns an empty string. |
| select RIGHT( ‘123’ , -1) | Returns NULL. | Error. |
| select RIGHT(N ‘123’ , -1) | Returns NULL. | Error. |
| select RTRIM( ‘ ‘ ) | Returns NULL. | Returns an empty string. |
| select RTRIM(N’ ‘ ) | Returns NULL. | Returns an empty string. |
| Select space(0) | Returns NULL. | Returns an empty string. |
| select SUBSTRING(‘123’,1,0) | Returns NULL. | Returns an empty string. |
| select SUBSTRING(N’123′,1,0) | Returns NULL. | Returns an empty string. |
| select CHARINDEX (‘SQLServer’ ,NULL) | Return 0. | Return NULL. |
| INSERT x SELECT 1 INTO y FROM authors |
Error. | Error. |
Notes:
1. When the compatibility mode is set to 70, the following words cannot be used for 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 for object names and identifiers: AUTHORIZATION, CASCASE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, and WORK.
Here is the syntax for sp_dbcmptlevel:
sp_dbcmptlevel [[@dbname=] name][,[@new_cmptlevel=]version]
@dbname is the name of the database for checking or changing the compatibility level.
@new_cmptlevel determines which compatibility level the database is set at (set it to 70, 65, or 60 with a default of NULL)
Example:
sp_dbcmptlevel pubs
It returns the following:
The current compatibility level is 70.
Now take a look at another example
sp_dbcmptlevel pubs, 65
It returns this:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.