Making the Move from Sybase to SQL Server

Isolation levels

In a multithreaded application, such as a relational database, it is important for the database engine to manage how data is isolated between running processes.

The syntax is different for Sybase and SQL Server when referring to the isolation levels with the SET statement.

Here in the next table I explain the isolation levels differences between Sybase and SQL Server

Cursor syntax:

Creating and executing stored procedure in both products remain similar, but a few exceptions in the cursor statements should be noted when converting.

Here's an example:

CREATE PROCEDURE sql_cursor AS
DECLARE  @lname char(20), @fname char(20)
DECLARE mycursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
OPEN mycursor
FETCH FROM mycursor INTO @lname, @fname
WHILE @@ FETCH_STATUS = 0

/*  Sybase uses  @SQLSTATUS instead of @@ FETCH_STATUS */
BEGIN
FETCH FROM  mycursor INTO @lname, @fname
/*
** SOME BUSINESS LOGIC GOES HERE
*/
END
CLOSE mycursor
DEALLOCATE /* Sybase needs the word CURSOR right here * / mycursor

It is important to note that SQL Server uses @@ FETCH_STATUS and Sybase uses @@SQLSTATUS both have a similar purpose, but they do not return the same error codes.

In the next table I explain the differences between the return codes:

Rollback Trigger

This command does not exist in SQL Server, so all Sybase stored procedures that incorporate the ROLLBACK TRIGGER command must be converted before a successful migration to SQL Server. 

By using the ROLLBACK TRIGGER command, it could be misleading when modifying data in tables with triggers. A single ROLLBACK TRIGGER rolls back only the trigger and the modification that fired the trigger. The remainder of the transaction continues and, if committed, is written to the database without the single command that was rolled back. Therefore, all statements in the transaction might not have completed successfully, but the data was committed anyway.

Here I give a sample trigger using ROLLBACK TRIGGER in Sybase:

CREATE TABLE table1 (a int, b int)
GO
CREATE TRIGGER trigger1 on table1 FOR  INSERT
AS
IF EXISTS  (SELECT  1 FROM inserted WHERE a = 100)
BEGIN
ROLLBACK TRIGGER  with RAISERROR  50000 Invalid value for column a
END
INSERT INTO table2
SELECT a, GETDATE() from inserted
RETURN
GO

In this code, all inserts into table1 also insert as audit row into table2 unless a = 100.

IF a = 100, the ROLLBACK TRIGGER command is fired and the INSERT is not fired. The rest of the batch continues, and a raiserror occurs, stating that there was an error in one of the INSERT commands.

The INSERT commands are shown here:

BEGIN TRAN
INSERT INTO table1 VALUES (1, 1)
INSERT INTO table1 VALUES (100,2)
INSERT INTO table1 VALUES (3, 3)
GO
SELECT  *  FROM table1

After issuing these commands, table1 and table2 each have two rows. Table1 has the values 1,1 and 3,3 and the second INSERT isn't committed because of the ROLLBACK TRIGGER. Table2 has the values 1, (currentdate) and 3, (currentdate) and the 100 isn't inserted because all processing in the trigger halts when a = 100 and the ROLLBACK TRIGGER is fired.

Mimicking this behavior in MS SQL Server requires some additional code. The outer transaction must now be accompanied with savepoints, as shown here:

CREATE trigger1 on table1 FOR INSERT
AS
SAVE TRAN trigger1
IF EXISTS (SELECT * FROM inserted WHERE a = 100)
BEGIN
ROLLBACK TRAN trigger1
RAISERROR  50000 ROLLBACK
END
INSERT INTO table2
SELECT a,  GETDATE() FROM inserted
GO

This trigger now begins with a savepoint and ROLLBACK TRANSACTION rolls back only the trigger logic, not the entire transaction (which is similar to Sybases ROLLBACK TRIGGER statement).

The changes to the batch job are shown here:

BEGIN TRAN

SAVE TRAN save1
INSERT INTO table1 VALUES (1, 1)
IF @@error = 50000
ROLLBACK TRAN save1

SAVE TRAN save2
INSERT INTO table1 VALUES (100, 1)
IF @@error = 50000
ROLLBACK TRAN save2

SAVE TRAN save3
INSERT INTO table1 VALUES (3, 3)
IF @@error = 50000
ROLLBACK TRAN save3

COMMIT TRAN

As you can see, the changes are not trivial. Because the ROLLBACK TRIGGER command can allow any single batch statement to fail, the additional logic must be included in the migrated SQL Server stored procedure code. Depending on the use of ROLLBACK TRIGGER, this could be a big,  but necessary, job. There are no shortcuts here. The behavior of the trigger changes if all the ROLLBACK TRIGGER statements are changed to ROLLBACK TRANSACTION after converting, so be careful.