Sybase SQL Anywhere 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 SQL Anywhere, Sybase SQL Anywhere, Sybase Adaptive Server Anywhere (Sybase ASA) to Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse.

Databases:

  • SAP Sybase SQL Anywhere 17, 16, 12, 11, 10, 9, 8, 7 and 5.x
  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse

See also Sybase Adaptive Server Enterprise to SQL Server Migration.

SQLines SQL Converter

SQLines SQL Converter converts database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Sybase SQL Anywhere to SQL Server.

The tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from a live Sybase ASA database use SQLines Data tool.

Try SQLines Online or download a Desktop Version. For more information, see Overview.

SQLines Data

SQLines Data is a high performance data transfer, schema conversion and data validation tool for Sybase ASA to SQL Server migration. For more information, see SQLines Data.

Migration Reference

Identifiers

Converting identifiers:

Sybase SQL Anywhere SQL Server
1 Constraint names are unique per table Constraint names are unique within a database more...
2 Default constraint name for a foreign key is the primary key table name Constraint, table names must be unique within a database more...

SQL Language Elements

Converting SQL and T/SQL language elements:

Sybase SQL Anywhere SQL Server
1 // (Double slash) Single line comment -- (Double hyphen)
2 --
3 /* */ Multi-line comment /* */
4 IF condition THEN exp ELSE exp2 ENDIF IF expression CASE WHEN condition THEN exp ELSE exp2 END
5 SQLCODE Error code of last operation @@ERROR

Data Types

Data type mapping from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 BIGINT 64-bit integer BIGINT
2 BINARY(n) Variable-length binary string, 1 ⇐ n ⇐ 32767 VARBINARY(n)
3 BINARY VARYING(n) Variable-length binary string, 1 ⇐ n ⇐ 32767 VARBINARY(n)
4 BIT 0 or 1 value; NULL is not allowed BIT
5 BIT VARYING(n) Variable-length bit array, 1 ⇐ n ⇐ 32767 BINARY(n/8)
6 CHAR(n), CHARACTER(n) Variable-length string, not padded, 1 ⇐ n ⇐ 32767 VARCHAR(n)
7 DATE Date (year, month and day) DATE
8 DATETIME Date and time with fraction DATETIME2(6)
9 DATETIMEOFFSET Date and time with fraction and time zone DATETIMEOFFSET(6)
10 DECIMAL(p,s), DEC(p,s) Fixed point number DECIMAL(p,s), DEC(p,s)
11 DOUBLE [PRECISION] Double-precision floating-point number FLOAT
12 FLOAT(p) Floating-point number FLOAT(p)
13 IMAGE Binary data, ⇐ 2G VARBINARY(max)
14 INTEGER, INT 32-bit integer INTEGER, INT
15 LONG BINARY Binary data, ⇐ 2G VARBINARY(max)
16 LONG BIT VARYING Bit array data, ⇐ 2G VARBINARY(max)
17 LONG NVARCHAR UTF-8 character data, ⇐ 2G NVARCHAR(max)
18 LONG VARBIT Bit array data, ⇐ 2G VARBINARY(max)
19 LONG VARCHAR Character data, ⇐ 2G VARCHAR(max)
20 MONEY Monetary data MONEY
21 NCHAR(n) Fixed-length UTF-8 string, 1 ⇐ n ⇐ 32767 NCHAR(n)
22 NTEXT UTF-8 character data, ⇐ 2G NVARCHAR(max)
23 NUMERIC(p,s) Fixed point number NUMERIC(p,s)
24 NVARCHAR(n) Variable-length UTF-8 string, 1 ⇐ n ⇐ 32767 NVARCHAR(n)
25 REAL Single-precision floating-point number REAL
26 SMALLDATETIME Date and time with fraction DATETIME2(6)
27 SMALLINT 16-bit integer SMALLINT
28 SMALLMONEY Monetary data, ⇐ million currency units SMALLMONEY
29 TEXT Character data, ⇐ 2G VARCHAR(max)
30 TIME Time (hour, minute, second and fraction) TIME(6)
31 TIMESTAMP Date and time with fraction DATETIME2(6)
32 TIMESTAMP WITH TIME ZONE Date and time with fraction and time zone DATETIMEOFFSET(6)
33 TINYINT 8-bit unsigned integer, 0 to 255 TINYINT
34 UNIQUEIDENTIFIER 16-byte GUID (UUID) data UNIQUEIDENTIFIER
35 UNIQUEIDENTIFIERSTR GUID (UUID) data in string format CHAR(36)
36 UNSIGNED BIGINT 64-bit unsigned integer NUMERIC(20)
37 UNSIGNED INT 32-bit unsigned integer NUMERIC(10)
38 UNSIGNED SMALLINT 16-bit unsigned integer NUMERIC(5)
39 UNSIGNED TINYINT 8-bit unsigned integer, 0 to 255 TINYINT
40 VARBINARY(n) Variable-length binary string, 1 ⇐ n ⇐ 32767 VARBINARY(n)
41 VARBIT(n) Variable-length bit array, 1 ⇐ n ⇐ 32767 BINARY(n/8)
42 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32767 VARCHAR(n) VARCHAR(max)
43 XML XML data XML

Built-in SQL Functions

Converting built-in SQL functions from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 ABS(num) Get the absolute value ABS(num)
2 ACOS(num) Get the arc cosine ACOS(num)
3 ARGN(position, exp, exp2, …) Get selected argument from list CASE expression
4 ASCII(str) Get ASCII code of left-most char ASCII(str)
5 ASIN(num) Get the arc sine ASIN(num)
6 ATAN(num) Get the arc tangent ATAN(num)
7 ATAN2(x, y) Get the arc tangent of x and y ATN2(x, y)
8 BASE64_DECODE(base64_str) MIME base64 decoding User-defined function
9 BASE64_ENCODE(str) MIME base64 encoding User-defined function
10 BIT_LENGTH(bit_str) Get length of bit_str in bits LEN(bit_str) * 8
11 BIT_SUBSTR(bit_str, start, len) Get a substring of bit_str SUBSTRING(bit_str, start, len)
12 BYTE_LENGTH(string) Get length of string in bytes DATALENGTH(string)
13 BYTE_SUBSTR(string, start, len) Get a substring of string in bytes SUBSTRING(string, start, len)
14 CEILING(num) Get the smallest following integer CEILING(num)
CEIL(num)
15 CHAR(num) Get character from ASCII code CHAR(num)
16 CHAR_LENGTH(string) Get length of string in characters LEN(string)
17 CHARINDEX(substring, string) Get position of substring CHARINDEX(substring, string)
18 COALESCE(exp1, exp2, …) Return first non-NULL expression COALESCE(exp1, exp2, …)
19 COMPARE(string, string2) Compare two character strings CASE expression
20 CONVERT(datatype, exp[, style]) Convert to another datatype CONVERT(datatype, exp[, style])
21 COS(num) Get the cosine COS(num)
22 COT(num) Get the cotangent COT(num)
23 CSCONVERT(string, charset) Convert string between character sets
24 CURRENT DATE Get the current date CONVERT(DATE, GETDATE())
25 CURRENT TIME Get the current time CONVERT(TIME, GETDATE())
26 CURRENT TIMESTAMP Get the current date and time GETDATE()
CURRENT_TIMESTAMP
27 CURRENT USER Get the authenticated user name SYSTEM_USER
CURRENT_USER
28 DATALENGTH(exp) Get length of exp in bytes DATALENGTH(exp)
29 DATE(datetime) Extract date from datetime CONVERT(DATE, datetime)
30 DATEADD(units, num, datetime) Add an interval to datetime DATEADD(units, num, datetime)
31 DATEDIFF(units, start, end) Get datetime difference in units DATEDIFF(units, start, end)
32 DATEFORMAT(datetime, format) Convert datetime to string CONVERT(VARCHAR(n), datetime, style)
33 DATENAME(datepart, date) Get the name of datepart as a string DATENAME(datepart, date)
34 DATEPART(datepart, date) Get datepart as an integer DATEPART(datepart, date)
35 DATETIME(exp) Convert exp to TIMESTAMP CONVERT(DATETIME, exp)
36 DAY(datetime) Extract day from datetime DAY(datetime)
37 DAYNAME(datetime) Get the name of the weekday DATENAME(DW, datetime)
38 DAYS(date) Get the number of days since Feb 29, 0000
DAYS(date, num) Add num days to date DATEADD(DAY, num, date)
39 DB_ID(database_name) Get the database ID number DB_ID(database_name)
40 DB_NAME(database_id) Get the name of a database DB_NAME(database_id)
41 DEGREES(num) Convert radians to degrees DEGREES(num)
42 DOW(date) Get the day of the week as integer DATEPART(DW, date)
43 ERRORMSG() Get the last error message STR(@@ERROR)
44 EXP(n) Raise e to the nth power EXP(n)
45 EXPRTYPE(exp, integer) Get the data type of exp User-defined function
46 FIRST(exp) Aggregate to return the first row only TOP 1 exp
47 FLOOR(num) Get the largest preceding integer FLOOR(num)
48 GET_BIT(bit_array, position) Get specified bit in a bit_array User-defined function
49 GETDATE() Get the current date and time GETDATE()
50 GREATER(exp, exp2) Get the greater of two expressions CASE expression
51 HASH(string[, algorithm]) Get the specified value in hashed form HASHBYTES(algorithm, string)
52 HEXTOINT(hexadecimal) Convert hexadecimal to integer CONVERT(INT, hexadecimal)
53 HOUR(datetime) Extract hour from datetime DATEPART(HH, datetime)
54 HTML_DECODE(string) Decode special HTML characters User-defined function
55 HTML_ENCODE(string) Encode special HTML characters (SELECT string FOR XML PATH(''))
56 HTTP_DECODE(string) URL decoding User-defined function
57 HTTP_ENCODE(string) URL encoding User-defined function
58 IDENTITY(exp) Get the sequential number of a row ROW_NUMBER()
OVER(ORDER BY (SELECT 1))
59 IFNULL(exp, exp2[, exp3]) Return exp2 if exp is NULL,
otherwise exp3
CASE expression
60 INSERTSTR(position, str, str2) Insert str2 into str in specified position SUBSTRING and concatenation
61 INTTOHEX(integer) Convert integer to hexadecimal CONVERT(VARBINARY(8), integer)
62 ISDATE(string) Check for a valid datetime ISDATE(string)
63 ISNULL(exp, exp2, …) Return first non-NULL expression ISNULL(exp, exp2)
COALESCE(exp, exp2, …)
64 ISNUMERIC(string) Check for a valid numeric ISNUMERIC(string)
65 LCASE(string) Lowercase string LOWER(string)
66 LEFT(string, n) Get n leftmost characters LEFT(string, n)
67 LENGTH(string), LEN(string) Get length of string in chars LEN(string)
68 LESSER(exp, exp2) Get the lesser of two expressions CASE expression
69 LIST(string, del) Aggregate string concatenation STUFF and FOR XML PATH
70 LOCATE(str, substring, start) Get position of substring CHARINDEX(substring, str, start)
71 LOG(numeric) Get natural logarithm LOG(numeric)
72 LOG10(numeric) Get base 10 logarithm LOG10(numeric)
73 LOWER(string) Lowercase string LOWER(string)
74 LTRIM(string) Remove leading spaces LTRIM(string)
75 MINUTE(datetime) Extract minute from datetime DATEPART(MI, datetime)
76 MOD(dividend, divisor) Get remainder (dividend % divisor)
77 MONTH(date) Extract month from date MONTH(date)
78 MONTHNAME(date) Get the name of the month DATENAME(MONTH, date)
79 NCHAR(integer) Convert Unicode code point to char NCHAR(integer)
80 NEWID() Generate GUIDs NEWID()
81 NOW([*]) Get the current date and time GETDATE()
82 NULLIF(exp1, exp2) Return NULL if exp1 = exp2 NULLIF(exp1, exp2)
83 NUMBER([*]) Get the sequential number of a row ROW_NUMBER()
OVER(ORDER BY (SELECT 1))
84 PATINDEX(pattern, string) Get starting position of pattern PATINDEX(pattern, string)
85 PI([*]) Get number pi PI()
86 POWER(value, n) Raise value to the nth power POWER(value, n)
87 QUARTER(date) Get the quarter of the year DATEPART(Q, date)
88 RADIANS(numeric) Convert degrees to radians RADIANS(numeric)
89 RAND([integer]) Get random float value in (0, 1) RAND([integer])
90 REGEXP_SUBSTR(str, pattern) Get a substring of str using regexp
91 REMAINDER(dividend, divisor) Get remainder (dividend % divisor)
92 REPEAT(string, n) Repeat string n times REPLICATE(string, n)
93 REPLACE(str, search, replace) Replace search-string REPLACE(str, search, replace)
94 REPLICATE(string, n) Repeat string n times REPLICATE(string, n)
95 REVERSE(string) Get reverse string REVERSE(string)
96 RIGHT(string, n) Get n rightmost characters RIGHT(string, n)
97 ROUND(num, integer) Get rounded value ROUND(num, integer)
98 RTRIM(string) Remove trailing spaces RTRIM(string)
99 SECOND(datetime) Extract second from datetime DATEPART(SS, datetime)
100 SIGN(exp) Get sign of exp SIGN(exp)
101 SIN(num) Get sine SIN(num)
102 SOUNDEX(string) Get 4-character sound code SOUNDEX(string)
103 SPACE(integer) Get string of spaces RPAD(' ', integer)
104 SQRT(num) Get square root SQRT(num)
105 STR(numeric) Get string equivalent of a number STR(numeric)
106 STRING(str, str2, …) Concatenates strings CONCAT(str, str2, …)
107 STUFF(exp, start, len, rep) Replace characters in string STUFF(exp, start, len, rep)
108 SUBSTRING(string, pos, len) Get a substring of string SUBSTRING(string, pos, len)
109 SUBSTR(string, pos, len)
110 SUSER_ID([user-name]) Get the server user’s ID SUSER_ID([user-name])
111 SUSER_NAME([user-id]) Get the server user’s name SUSER_NAME([user-id])
112 SWITCHOFFSET(tmz, offset) Change time zone offset SWITCHOFFSET(tmz, offset)
113 TAN(num) Get tangent TAN(num)
114 TEXTPTR(exp) Get pointer that conform to LOB TEXTPTR(exp)
115 TO_CHAR(exp) Convert to string CONVERT(VARCHAR, exp)
116 TO_NCHAR(exp) Convert to NCHAR CONVERT(NCHAR, exp)
117 TODATETIMEOFFSET(tms, tz) Convert to DATETIMEOFFSET TODATETIMEOFFSET(tms, tz)
118 TODAY([*]) Get the current date CAST(GETDATE() AS DATE)
119 TRIM(string) Remove leading and trailing spaces RTRIM(LTRIM(string))
120 TRUNCNUM(numeric, integer) Truncate numeric User-defined function
121 TRUNCATE(numeric, integer)
122 TSEQUAL(value1, value2) Compare two timestamp values value1 = value2
123 UCASE(string) Uppercase string UPPER(string)
124 UNICODE(string) Get Unicode code of the 1st character UNICODE(string)
125 UNISTR(string) Convert Unicode code points to chars Expressions using NCHAR
126 UPPER(string) Uppercase string UPPER(string)
127 USER_ID([user_name]) Get user ID USER_ID([user_name])
128 USER_NAME([user_id]) Get user name USER_NAME([user_id])
129 XMLCONCAT(exp, exp2, …) Concatenate XML expressions
130 XMLELEMENT(NAME exp) Get an XQuery element node
131 XMLFOREST(exp, exp2, …) Get a forest of XML expressions
132 XMLGEN(construnctor, exp, …) Get XML based on constructor
133 YEAR(date) Extract year from date YEAR(date)

SELECT Statement

Converting SQL SELECT statement from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 SELECT FIRST col, … Select the first row only SELECT TOP 1 col, …
SELECT FIRST(col) …
2 SELECT col,… INTO var, … FROM … Select a single row into variables SELECT @var = col, … FROM …

CREATE TABLE Statement

Converting CREATE TABLE statement from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 DEFAULT GLOBAL AUTOINCREMENT (partition_size) Autoincrement column IDENTITY
2 Multiple AUTOINCREMENT columns allowed per table Only one identity
column per table is allowed
2 DEFAULT TIMESTAMP Auto-updated timestamp GETDATE() and trigger
3 col data_type COMPUTE (exp) Computed column col AS (exp)
4 INLINE num Data type attribute Removed
5 PREFIX num Data type attribute Removed

Foreign key actions:

Sybase SQL Anywhere Microsoft SQL Server
1 ON UPDATE RESTRICT NO ACTION
CASCADE CASCADE
SET NULL SET NULL
SET DEFAULT SET DEFAULT
2 ON DELETE RESTRICT NO ACTION
CASCADE CASCADE
SET NULL SET NULL
SET DEFAULT SET DEFAULT

CREATE PROCEDURE Statement

Converting stored procedures from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 IN | OUT | INOUT param datatype(length) DEFAULT default @param datatype(length) = default OUT
3 RESULT (col datatype(length), …) Result set definition Removed, not required
4 No AS keyword AS required

For more information, see Conversion of Procedural SQL Statements.

CREATE FUNCTION Statement

Converting user-defined SQL functions from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 CREATE FUNCTION name CREATE FUNCTION name
2 IN param datatype(length) DEFAULT default @param [AS] datatype(length) = default
2 RETURNS datatype(length) RETURNS datatype(length)
3 [NOT] DETERMINISTIC Removed
4 ON EXCEPTION RESUME Continue execution in an error occurs Removed, it is the default behavior
5 [AS] [AS]

For more information, see Conversion of Procedural SQL Statements.

CREATE TRIGGER Statement

Converting triggers from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere - Watcom-SQL SQL Server
1 CREATE TRIGGER name CREATE TRIGGER name
2 when event(s) ON table ON table when event(s)
3 BEFORE When to fire trigger Not supported, converted to INSTEAD OF
AFTER AFTER | FOR
INSTEAD OF INSTEAD OF
4 INSERT, UPDATE, DELETE Trigger events (list) INSERT, UPDATE, DELETE
5 REFERENCING OLD AS old NEW AS new inserted and deleted system tables
6 FOR EACH ROW Row-level trigger Cursor for inserted and deleted
7 No AS keyword before the trigger body AS keyword is required

For more information, see Conversion of Procedural SQL Statements.

Procedural SQL Statements

Converting Transact-SQL and Watcom-SQL statements used in stored procedures, functions and triggers from Sybase SQL Anywhere to SQL Server:

Sybase SQL Anywhere SQL Server
1 DECLARE var datatype(length) Variable declaration DECLARE @var datatype(length)
2 DECLARE LOCAL TEMPORARY TABLE name (columns)
ON COMMIT DELETE | PRESERVE ROWS
DECLARE @name TABLE (columns)
3 DECLARE name EXCEPTION FOR SQLSTATE VALUE 'sqlstate' Declaration not required, removed
4 IF condition THEN … ELSEIF/ELSE … END IF IF statement IF condition BEGIN … END ELSE …
IF condition BEGIN … END ELSE …
5 SIGNAL exception_name Raise an exception RAISERROR('error text', 16, 1)