Guide to Migrating from SAP SQL Anywhere to SQL Server 2016/2017

129 Pages·2017·0.54 MB·English

Guide to Migrating from SAP SQL Anywhere to SQL
Server 2016/2017

SQL Server Technical Article

Writers: Arthur Alchangian (DB Best Technologies), Igor Yefimov (DB Best Technologies),
Galina Shevchenko (DB Best Technologies), Alexander Pavlov (DB Best Technologies)
Technical Reviewer: Kalen Delaney (DB Best Technologies), Bill Ramos (DB Best
Technologies)

Published: Last updated June 22, 2017
Applies to: SQL Server 2016, SQL Server 2017

Summary: This white paper explores challenges that arise when you migrate from a SAP SQL
Anywhere (also known as SQL Anywhere) database of version 9 or later to SQL Server 2016. It
describes the implementation differences between the two platforms relating to database
objects, SQL dialects, and procedural code. We recommend migration to SQL Server 2016 SP1
or later Express Edition as your target for SQL Anywhere solutions. The Express Edition now
supports all the features that are available in SQL Server 2016. This allows you to easily scale
up or move your databases to Azure SQL Database as your applications grow.

Created by: DB Best Technologies LLC
2763 152nd Ave NE, Redmond, WA 98052
Tel.: [phone]
E-mail: [email protected]
Web: www.dbbest.com
Copyright
This is a preliminary document and may be changed substantially prior to final commercial
release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation
on the issues discussed as of the date of publication. Because Microsoft must respond to
changing market conditions, it should not be interpreted to be a commitment on the part of
Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the
date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES,
EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the
rights under copyright, no part of this document may be reproduced, stored in or introduced into
a retrieval system, or transmitted in any form or by any means (electronic, mechanical,
photocopying, recording, or otherwise), or for any purpose, without the express written
permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual
property rights covering subject matter in this document. Except as expressly provided in any
written license agreement from Microsoft, the furnishing of this document does not give you any
license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-
mail addresses, logos, people, places and events depicted herein are fictitious, and no
association with any real company, organization, product, domain name, email address, logo,
person, place or event is intended or should be inferred.

© 2017 Microsoft Corporation. All rights reserved.
Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the United
States and other countries.
The names of actual companies and products mentioned herein may be the trademarks of their
respective owners.
Contents
Introduction ................................................................................................................................ 4
Conversion of Data Types .......................................................................................................... 5
Stored Procedures ..................................................................................................................... 7
CALL Statements ................................................................................................................... 7
Returning Result Sets from a Stored Procedure ..................................................................... 8
Defining Parameters ..............................................................................................................12
Selecting a Returned Value in the Calling Environment .........................................................14
Exception Handling................................................................................................................15
Flow Control Constructs ........................................................................................................19
Cursors ..................................................................................................................................26
User-Defined Functions ............................................................................................................28
CREATE FUNCTION Statement ...........................................................................................28
Statements ................................................................................................................................29
FROM Clause ........................................................................................................................29
FOR JSON clause .................................................................................................................38
Common Table Expressions ..................................................................................................39
Special INSERT Statements ..................................................................................................41
Migrating SQL Anywhere Standard Functions ...........................................................................46
Equivalent Functions .............................................................................................................46
Emulated Functions ...............................................................................................................46
Conclusion ..............................................................................................................................1 28
About DB Best Technologies ...............................................................................................1 28
Post Migration considerations ................................................................................................. 129
Introduction
SAP SQL Anywhere database engine, also known as Sybase ASA, was originally designed to
support desktop applications, serve as a lightweight database solution to be embedded in
applications, and work for mobile solutions. SQL Server 2016 SP1 Express Edition or later is
also designed to run in the same environments with much greater capabilities. Therefore, when
you consider migrating your Sybase ASA/SAP SQL Anywhere solutions, first consider using the
SQL Server 2016 Express Edition. And here is why:

• Express Edition is designed for web and mobile applications for multiple data types, and
is built to store and support structured and unstructured data, with native support for
relational data, XML, JSON, spatial data, and time data types.

• SQL Server supports a notion of a local database (SQL Server Express LocalDB), fully
compatible with SQL Server, allowing you to embed a lightweight database into
applications, and run it in-process with your applications and not as a service

• SQL Server enables you to easily scale your applications across different editions.
Express Edition already comes with solid functionality: up to 4 cores, 1.4 GB maximum
RAM, up to 10 GB per database, in-memory OLTP, advanced security and much more,
and you can upgrade your database platform to Standard or Enterprise edition as your
applications grow. Code once- deploy anywhere. SQL Server is SQL Server “Anywhere”.

This migration guide outlines problems and solutions for migrating from SAP SQL Anywhere to
the Microsoft SQL Server 2016 or later.
This guide explains the data type mapping and adds remarks about the related conversion
issues, explores the challenges you might encounter when migrating from SAP SQL Anywhere
to SQL Server 2016/2017, and offers possible solutions and examines SAP SQL Anywhere
system function references, divided into equivalent functions and emulated functions.
Conversion of Data Types
This section covers data types mapping between SQL Anywhere and SQL Server 2016. The
following table shows SQL Anywhere data types and their equivalents in SQL Server 2016
including differences in data type length and ranges.
SQL Anywhere SQL Server 2016

char[(n)] 32767 varchar[(n)] n<=8000, varchar(max) n>8000

varchar[(n)] 32767 varchar[(n)] n<=8000, varchar(max) n>8000
nchar[(n)] 32767 nvarchar[(n)] n<=4000, nvarchar(max) n>4000

nvarchar[(n)] 32767 nvarchar[(n)] n<=4000, nvarchar(max) n>4000
text varchar(max)
long varchar
[text has been deprecated in SQL Server]
long nvarchar nvarchar(max)
xml xml

bit 0,1 bit 0,1
tinyint 0…255 tinyint 0…255
smallint 2^15 – 1 smallint 2^15 - 1
unsigned smallint 2^16 - 1 integer 2^31 – 1
integer 2^31 – 1 integer 2^31 - 1
unsigned integer 2^32 - 1 bigint 2^63 – 1
bigint 2^63 - 1 bigint 2^63 - 1
unsigned bigint 2^64 – 1 numeric(20,0)
decimal[(p[,s])] 127 decimal[(p[,s])] p<=38
numeric[(p[,s])] 127 numeric[(p[,s])] p<=38
float(53) p>38

float[(precision)] float[(precision)]
double real
real

money money +922,337,203,685,477.5807
+999,999,999,999,999.9999 numeric(19,4) +999,999,999,999,999.9999
smallmoney smallmoney +214,748.3647
+999,999.9999 numeric(10,4) +999,999.9999

date (January 1, 0001, date (January 1, 0001, through December 31, 9999)
through December 31,
9999)
datetime datetime2
smalldatetime
(January 1, 0001 00:00:00.0000000, through December 31,
(January 1, 0001 9999 23:59:59.9999999 )
00:00:00.000000, through
December 31, 9999
23:59:59.999999 )
time time
datetimeoffset datetimeoffset

binary[(n)] 32767 binary[(n)] n<=8000, varbinary(max) n>8000
varbinary[(n)] n<=8000, varbinary(max) n>8000
varbinary[(n)] 32767
long binary
image varbinary(max)

varbit 32767 There are a few options here:
long varbit 32767 • Use varbinary() and emulate type methods
• Create CLR UDT with methods
• Create CLR aggregate
Spatial data type Spatial data types are compliant with SQL MM (ISO standard).
The following diagram show SQL Server spatial data types:

The instantiable types are indicated in blue.
Stored Procedures
This section discusses differences between the SQL procedural language in SQL Anywhere
and Microsoft SQL Server. This includes the creation and calling of stored procedures as well
as working with local variables, cursors, and control-of-flow statements.

CALL Statements
This section covers possible issues which can appear while converting SQL Anywhere CALL
statements and offers possible solutions.

Issue: Syntax for Calling Procedures
SQL Anywhere uses the CALL statement to invoke a procedure.

SQL Anywhere example:
CREATE PROCEDURE new_dept (
IN id INT,
IN name CHAR(35),
IN head_id INT )
BEGIN
INSERT INTO DBA.department (dept_id, dept_name, dept_head_id)
VALUES (id, name, head_id);
END

CALL new_dept(210, 'Eastern Sales', 902);
Solution:
Convert SQL Anywhere CALL statements to Transact-SQL EXECUTE or EXEC statements.

SQL Server example:
CREATE PROCEDURE new_dept (
@id INT,
@name CHAR(35),
@head_id INT )
AS
BEGIN
INSERT INTO DBO.department (dept_id, dept_name, dept_head_id)
VALUES (@id, @name, @head_id);
END;
RETURN;
GO
-- Call the procedure
EXEC new_dept 210, 'Eastern Sales', 902;

Returning Result Sets from a Stored Procedure
This section contains descriptions of issues that can appear when you convert code that returns
result sets from a stored procedure and possible solutions.

Issue: RESULT Keyword
In SQL Anywhere, you can use the RESULT keyword to return a result set from a stored
procedure and then select data from the result set. To do this, you define the keyword as a
return parameter in the stored procedure.

SQL Anywhere examples:
Example 1:
CREATE PROCEDURE "DBA"."ManageContacts"(IN action char(1),
IN contact_ID integer)
RESULT(ID integer,
Surname char(20),
GivenName char(20),
Street char(30),
City char(20),
State char(16)
)
BEGIN
CASE action
WHEN 'S' THEN
SELECT * FROM DBA.Contacts
WHERE Contacts.ID=contact_ID
WHEN 'D' THEN
DELETE FROM DBA.Contacts
WHERE Contacts.ID=contact_ID
END CASE
END

SELECT t.Surname, t.GivenName FROM DBA.ManageContacts('S', 1) t

Example 2:
CREATE PROCEDURE "DBA"."ShowContactsByCity"(IN city char(20))
RESULT(ID integer,
Surname char(20),
GivenName char(20),
Street char(30),
City char(20),
State char(16)
)
BEGIN
SELECT ID, Surname, GivenName, Street, City, State
FROM DBA.Contacts
WHERE City=city
END

SELECT t.Surname, t.GivenName, t.Street, t.City, t.State
FROM DBA.ShowContactsByCity('Atlanta') t

Solution:
Replace the RESULT keyword with a temporary table defined in a calling code and insert the
returned result set into this temporary table. Then you can execute queries on the table and
apply WHERE clauses and other SELECT features to limit the result set.
If there are no Data Manipulation Language (DML) statements in the source stored procedure
body, consider using a SQL Server table-valued function as an alternative to the procedure.
Note that SQL Server does not allow the use of side-effecting DML operators within a function.

SQL Server examples:
Example 1:
CREATE PROCEDURE DBO.ManageContacts(@action char(1),
@contact_ID int)
BEGIN
IF @action = 'S'
SELECT * FROM DBO.Contacts
WHERE Contacts.ID=@contact_ID;
IF @action = 'D'
DELETE FROM DBO.Contacts
WHERE Contacts.ID=@contact_ID;
END;
RETURN;
GO

-- Create a temporary table
CREATE TABLE #temp_result(
ID int,
Surname char(20),
GivenName char(20),
Street char(30),
City char(20),
State char(16)
);
GO

-- Call the procedure to populate the table
INSERT INTO #temp_result
EXEC dbo.ManageContacts 'S', 1;
GO

-- Select from the temporary table
SELECT Surname, GivenName FROM #temp_result;
GO

Example 2:
This example does not contain DML operators that change tables, so it can be emulated using
table-valued functions in SQL Server:
CREATE FUNCTION DBO.ShowContactsByCity(@city char(20))
RETURNS @result TABLE
(
ID int,
Surname char(20),
GivenName char(20),
Street char(30),
City char(20),
[State] char(16)
)
AS
BEGIN