Delivering expert support in resolving database conversion errors
21 Jun 2017

One of the major United States universities has been migrating their complex data managing system from Sybase to SQL Server. They used SQL Server Migration Assistant for Sybase to automate the migration process. However, SSMA tool could not deal with 100% of their source code, and their development team required some assistance with a manual conversion. So they asked us to provide technical support in resolving these conversion errors. With our guidance and extensive support, our customer was able to complete the migration within the short period of time. (more…)...

An ultimate approach to converting hierarchical queries from Oracle to SQL Server
Helping our customer to convert Oracle schemas with hierarchical queries to SQL Server
20 Jun 2017

One of the world’s largest information technology companies decided to migrate their Oracle database to SQL Server. They leveraged SQL Server Migration Assistant (SSMA) for Oracle to automate the major part of their migration effort. However, their source database code included over 400 hierarchical queries that SSMA could not convert. Neither could the customer’s technical staff. So, they turned to DB Best asking for assistance in converting a number of Oracle database schemas. We helped our customer to solve this complicated task by converting the required database schemas within the short period of time. Watch the following video to l...

Oracle to SQL Server migration with SSMA: How to convert virtual columns
14 Jun 2017

Oracle allows you to specify virtual columns in the table definition. Oracle doesn’t store the data in virtual columns on the disk. On the contrary, the database derives the values in a virtual column on demand by computing a set of expressions or functions. The computed columns in SQL Server are considered as a direct analog of Oracle’s virtual columns. The problem is that the SQL Server Migration Assistant (SSMA) for Oracle converts the virtual columns as a DEFAULT expression. Thus the logic that stands behind the virtual column doesn’t converts to the target database. This will result in an error when you will try...

Oracle to SQL Server migration with SSMA: How to convert unsupported SQL clause
13 Jun 2017

Oracle allows you to assign a non-scalar condition in WHERE clause. The problem is that SQL Server doesn’t support conditions of this type. Thus, the SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert queries with a a non-scalar condition in WHERE clause. So, when you try to convert the source code that includes a non-scalar condition in a WHERE clause, SSMA will generate an error O2SS0001. (more…)...

Oracle to SQL Server migration with SSMA
Oracle to SQL Server migration with SSMA: How to convert aggregate functions referring to remote table
21 Apr 2017

Oracle allows you to create a query with aggregate functions referring to a remote table. For example, if your query includes two tables: one from the local schema and another from the remote database. You can simply use a database link in Oracle to create that construction. Previously, we already talked about converting database links using a linked server. But the problem is that SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert queries that refer to the remote objects through the database link. So, when you try to convert a query with aggregate functions referring to a remote table, SSMA will generate an error O...

Oracle to SQL Server migration with SSMA: How to convert database links
13 Apr 2017

Oracle uses database links to access objects on another database. In SQL Server you can use linked server or an OPENQUERY function to emulate that functionality. The problem is that SQL Server Migration Assistant (SSMA) for Oracle doesn’t automatically convert database links. Instead, when you try to convert a query that refers to the remote objects through the database link, SSMA will generate an error O2SS0083. (more…)...

Oracle to SQL Server migration with SSMA: How to convert interval literals
30 Mar 2017

In Oracle, you may use specific interval literals that indicate a certain period of time. SQL Server does not have a direct analog for interval literals. Thus, when trying to convert interval literals using  SQL Server Migration Assistant (SSMA) for Oracle, you will get an error O2SS0086. The term literal refers to a fixed data value just as the constant value term. You can specify interval literals in terms of years and months or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. SSMA can convert neither of them, so it generates one of the following...

Oracle to SQL Server migration with SSMA: How to convert interval expressions
30 Mar 2017

Oracle uses specific interval expressions to store a period of time. SQL Server does not have a direct analog for this data type. Thus, when you try to convert interval expressions using  SQL Server Migration Assistant (SSMA) for Oracle, you will get an error O2SS0006. Your source Oracle code may include 2 kinds of intervals: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The first one stores the difference in years and months, while the second type stores the difference in days, hours, minutes and seconds. SSMA does not provide type mapping for Oracle’s interval expressions. Let us try to convert the source Oracle code that i...

Oracle to SQL Server migration with SSMA: How to convert materialized view with float type
24 Mar 2017

SQL Server has several different ways of implementing the same behavior as an Oracle materialized view. SSMA cannot understand the original optimization reason for why the application needed the materialized view, so SSMA flags certain conditions as an error. This blog post discusses error O2SS0522 where the Oracle materialized view includes FLOAT columns, WHERE or GROUP BY clauses, subqueries, UNION, MINUS, INTERSECT, etc. Currently SQL Server Migration Assistant (SSMA) for Oracle converts materialized views to SQL Server indexed views. While converting a materialized view, SSMA creates necessary unique clustered index on the view in SQL Se...

Oracle to MySQL migration with AWS SCT: How to convert BULK COLLECT clause
16 Mar 2017

Oracle’s SELECT INTO statement with BULK COLLECT clause allows you to retrieve an entire result set and store it in a PL/SQL collection type variable in a single operation. This approach allows to avoid the use of a loop statement to retrieve one result row at a time, thus making the code compact and effective. MySQL doesn’t support the BULK COLLECT INTO operation, so, SCT can not convert the source Oracle code correctly. When you try to convert the source code that contains the SELECT INTO statement with BULK COLLECT Clause, SCT will generate the following message “140 — Severity CRITICAL – MySQL doesn’t support BULK ...