SAP Sybase Advantage Database Server to SQL Server Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from SAP Sybase Advantage Database Server (Sybase ADS) to Microsoft SQL Server.

Databases:

  • SAP Sybase Advantage Database Server 12.x, 11.x, 10.x and 9.x
  • Microsoft SQL Server 2016, 2014, 2012, 2008, 2005 and 2000

Migration Reference

Language Elements

SQL language elements:

Sybase ADS SQL Server
1 [name] Quoted identifer [name]
2 // text Single-line comment -- text
3 TRUE and FALSE Aliases for logical values 1 and 0 1 and 0

Datetime arithmetic:

Sybase ADS SQL Server
1 date + num Add num days to date DATEADD(dd, num, date)
2 date - num Subtract num days from date DATEADD(dd, -num, date)
3 date1 - date2 Get number of days between the two dates DATEDIFF(dd, date1, date2)

Datetime literals:

Sybase ADS SQL Server
1 '0000-01-01 00:00:00.000' Year can be 0000 '0001-01-01 00:00:00.000' Year starts from 0001

Logical expressions:

Sybase ADS SQL Server
1 NOT Logical_expr Negation of logical value (True | False) CASE WHEN Logical_expr = 1 THEN 0 ELSE 1 END

Data Types

Character data types:

Sybase ADS SQL Server
1 NMemo Variable-length UTF-16 string, up to 4 GB NVARCHAR(max)
2 Memo Variable-length string, up to 4 GB VARCHAR(max)

Numeric data types:

Sybase ADS SQL Server
1 CurDouble 8-byte floating-point FLOAT
2 Short 16-bit integer, -32,767 to 32,767 SMALLINT

Datetime data types:

Sybase ADS SQL Server
1 Time Time (hour, minute, seconds and milliseconds) TIME2(3)
2 Timestamp Date and time (milliseconds) DATETIME2(3)

Other data types:

Sybase ADS SQL Server
1 AutoInc Auto-increment INT IDENTITY
2 Logical Boolean 1, 0, 'T', 'F', 'Y', 'N' BIT 1, 0, NULL

Built-in SQL Functions

String functions:

1 INSERT(str, start, len, val) Replace substring with val STUFF(str, start, len, val)

Datetime functions:

Sybase ADS SQL Server
1 CURRENT_DATE() Get the current date (year, month, day) CONVERT(DATE, GETDATE())
2 CURRENT_TIMESTAMP() Get the current datetime CURRENT_TIMESTAMP
3 DAYOFMONTH(datetime) Get day of month from datetime DAY(datetime)
4 DAYOFWEEK(datetime) Get day of week from datetime DATEPART(dw, datetime)
5 DAYOFYEAR(datetime) Get day of year from datetime DATEPART(dy, datetime)
6 HOUR(datetime) Get hour from datetime DATEPART(hh, datetime)
7 MINUTE(datetime) Get minutes from datetime DATEPART(mi, datetime)
8 TIMESTAMPADD(unit, num, datetime) Add an interval to datetime DATEADD(unit, num, datetime)
9 YEAR(datetime) Get year from datetime YEAR(datetime)

Data type conversion functions:

Sybase ADS SQL Server
1 CONVERT(expr, SQL_CHAR) Convert to string CONVERT(VARCHAR, expr) CAST(expr AS VARCHAR)
2 CONVERT(expr, SQL_DATE) Convert to date CONVERT(DATE, expr) CAST(expr AS DATE)
3 CONVERT(expr, SQL_INTEGER) Convert to integer CONVERT(INTEGER, expr) CAST(expr AS INTEGER)
4 CONVERT(expr, SQL_TIME) Convert to time CONVERT(TIME(3), expr) CAST(expr AS TIME(3))
5 CONVERT(expr, SQL_TIMESTAMP) Convert to datetime CONVERT(DATETIME2(3), expr)
CAST(expr AS DATETIME2(3))

CASE and NULL functions:

Sybase ADS SQL Server
1 IFNULL(expr, expr2) Return first non-NULL expression ISNULL(expr, expr2)
2 IIF(bool_expr, true_expr, false_expr) IF -THEN-ELSE expression IIF(bool_expr, true_expr, false_expr)

Identity value and GUID functions:

Sybase ADS SQL Server
1 LASTAUTOINC(STATEMENT) Get last identity value in the same scope SCOPE_IDENTITY()
LASTAUTOINC(CONNECTION) Get last identity value @@IDENTITY
2 NEWIDSTRING() Generate GUID value NEWID()

Math functions:

Sybase ADS SQL Server
1 MOD(num1, num2) Get the remainder of num1 divided by num2 num1 % num2

SELECT Statement

Specific queries:

Sybase ADS SQL Server
1 SELECT expr FROM System.iota Calculate expression SELECT expr
2 SELECT udf(p1, …) FROM System.iota Execute user function (UDF) SELECT dbo.udf(p1, …) EXEC dbo.udf p1, …
3 SELECT param FROM __input Access procedure input param @param

Logical expressions without boolean operator:

Sybase ADS SQL Server
1 SELECT … FROM … WHERE Logical_func WHERE True i.e. SELECT … FROM … WHERE Bit_func = 1

Create a temporary table and insert data:

Sybase ADS SQL Server
1 SELECT … INTO #temp_tab1 FROM … WHERE … SELECT … INTO #temp_tab1 FROM … WHERE …

CREATE PROCEDURE Statement

Converting stored procedures:

Sybase ADS SQL Server
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 param datatype(length) [OUTPUT] @param datatype(length) [= default] [OUTPUT]
3 No AS keyword AS required
4 SELECT param FROM __input Access input param @param

For more information, see Conversion of Procedural SQL Statements.

CREATE FUNCTION Statement

Converting user-defined functions (UDF):

Sybase ADS SQL Server
1 CREATE FUNCTION name CREATE FUNCTION name
2 param datatype(length) @param datatype(length) [= default]
3 RETURN is optional RETURN must be the last statement within a function, add RETURN NULL

Nested standalone UDF calls (within another CREATE FUNCTION without assignment to a variable):

Sybase ADS SQL Server
1 SELECT udf(param, …) FROM System.iota EXECUTE dbo.udf param, …

CREATE FUNCTION limitations:

Sybase ADS SQL Server
1 You can access temporary tables from UDF Error: Msg 2772, Level 16, State 1
Cannot access temporary tables from within a function

For more information, see Conversion of Procedural SQL Statements.

CREATE TRIGGER Statement

Converting triggers:

Sybase ADS SQL Server
1 CREATE TRIGGER name ON table_name CREATE TRIGGER name ON table_name
2 BEFORE When to fire trigger Not supported, converted to INSTEAD OF
AFTER AFTER
INSTEAD OF INSTEAD OF
3 INSERT | UPDATE | DELETE Trigger events (only one per trigger) INSERT, UPDATE, DELETE (list can be specified)
4 No AS keyword before the trigger body AS keyword is required
5 PRIORITY num User-defined trigger firing priority Use sp_settriggerorder

For more information, see Conversion of Procedural SQL Statements.

Procedural Language

Declarations:

Sybase ADS SQL Server
1 DECLARE cur CURSOR AS select_stmt; Cursor declaration DECLARE cur CURSOR FOR select_stmt;

Variable assignment:

Sybase ADS SQL Server
1 @var = expr Assign value SET @var = expr
2 @var = (SELECT … FROM …) Assign value from scalar query SET @var = (SELECT … FROM …)
3 @bool_var = expr=expr2 Assign logical value SET @bool_var = CASE WHEN expr=expr2
THEN 1 ELSE 0 END

Cursor operations:

Sybase ADS SQL Server
1 OPEN cur; Open a cursor OPEN cur;
2 WHILE FETCH cur DO
sql_statements
END WHILE;
Fetch cursor in a loop FETCH cur INTO …
WHILE @@FETCH_STATUS=0 BEGIN
sql_statements
FETCH cur INTO …
END;
3 cur.var Cursor field reference @cur_var variable declaration

Flow-of-control statements:

Sybase ADS SQL Server
1 IF condition THEN … [ELSEIF/ELSE …] ENDIF IF statement IF condition BEGIN … END [ELSE [IF] BEGIN … END]
2 LEAVE Leave the loop BREAK

Executing stored procedures:

Sybase ADS SQL Server
1 EXECUTE PROCEDURE name (param, …) Procedure with parameters EXECUTE name param, …

Handling exceptions:

Sybase ADS SQL Server
1 TRY … CATCH ALL […] END Catching all exceptions BEGIN TRY … END TRY BEGIN CATCH […] END CATCH

System Tables

System tables:

Sybase ADS SQL Server
1 System.iota Single row/column table (“dual” table) Not required, SELECT can be without FROM clause