Article Topics

All Articles

Peformance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...

Making the Move from Sybase to SQL Server

By : Sayed Geneidy
Aug 30, 2003

Printer friendly

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.

Next Page>>