Making the Move from Sybase to SQL Server

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.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |