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:

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)
5	CONVERT(expr, SQL_TIMESTAMP)	Convert to datetime	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()
1	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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference

SAP Sybase Advantage Database Server to SQL Server Migration

Migration Reference

Language Elements

Data Types

Built-in SQL Functions

SELECT Statement

CREATE PROCEDURE Statement

CREATE FUNCTION Statement

CREATE TRIGGER Statement

Procedural Language

System Tables