SSMA Blog - SQL Server Migration

Microsoft announces SQL Server Migration Assistant for MySQL

2010-08-21T06:02:00.000-07:00

Migrate from MySQL to SQL Azure or SQL Server with ease!

Microsoft announced the release of first version of SQL Server Migration Assistant (SSMA) for MySQL! Along with this release, Microsoft refreshed the existing SSMA family of products for Oracle, Sybase and Access with the latest v4.2 release.

SQL Server Migration Assistant for MySQL is the newest migration toolkit, others include Oracle, Sybase, Access, and an analyzer for PowerBuilder. The toolkits were designed to tackle the complex manual process customer’s deal with when migrating databases. In using the SQL Server Migration Assistants, customers and partners reduce the manual effort; as a result the time, cost and risks associated with migrating are significantly reduced. Our recent SSMA survey showed that 94% of SSMA downloader’s would recommend the tool to others. All SSMA toolkits are available for free download.

Since the SQL Server Migration Assistants for Oracle became available in June of 2005, Microsoft has seen more than a 250,000 downloads across all of the SSMAs. US Department of Defense, Aon, AIM Healthcare, Motorola, Simon & Schuster, Sony Ericsson and Artesia are just a few of the customers that have downloaded and used the SQL Server Migration Assistant toolkit to migrate to Microsoft SQL Server.

With this wave of release, customers can migrate to any edition of SQL Server 2008 R2, SQL Server 2008 or SQL Server 2005. Brand NEW with this release, SSMA for MySQL and SSMA for Access products also support simple and direct migration to SQL Azure.

The latest SQL Server Migration Assistant is available for free download and preview at:
1. SSMA for MySQL v1.0, out of the two downloads, ‘SSMA 2008 for MySQL’ enables migration to SQL Azure
2. SSMA for Access v4.2 out of the two downloads, ‘SSMA 2008 for Access’ enables migration to SQL Azure
3. SSMA for Oracle v4.2
4. SSMA for Sybase v4.2
Customers and partners can provide feedback via ssmateam@microsoft.com.

SSMA for MySQL v1.0 is designed to work with MySQL 4.1 and above. Some of the salient features included in this release are the ability to convert/migrate:
• Tables
• Views
• Stored procedures
• Stored functions
• Triggers
• Cursors
• DML statements
• Control statements
• Transactions

The latest SSMA v4.2 release for Oracle/Sybase/Access includes:
• Console support for automated DB assessment/DB migration/SQL syntax conversion for application migration.
• Support for client side and server side data migration for convenience.
• Better support for 64-bit platform.
• Microsoft Access (Access 97 and above) to SQL Azure migration (you can continue to use existing Access front end applications though the data resides in SQL Azure)
• Support for migrating to SQL Server 2008 R2
• Support for migrating from latest Oracle (11g R2), Sybase (15.5) and Access (2010) releases

SSMA for MySQL v1.0 CTP released

2010-01-07T23:03:00.000-08:00

SQL Server Migration Assistant for MySQL is newest member of SSMA family!

Microsoft SQL Server Migration Assistant (SSMA) 2008 is a toolkit that dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2008 and SQL Azure. SSMA 2008 for MySQL v1.0 CTP1 provides an assessment of migration efforts as well as automates schema and data migration. It is first member of SSMA product family which includes support for SQL Azure.

SSMA 2008 for MySQL v1.0 CTP1 is designed to work with MySQL 4.1, 5.0 and 5.1 and all editions of SQL Server 2008 or SQL Azure.
You can download the tool from the links below.

SSMA 2008 for MySQL v1.0 CTP1

SSMA 2005 for MySQL v1.0 CTP1

SSMA Links Listing

2009-09-27T22:36:00.000-07:00

In this section I will list down all the interesting and useful links on the internet on SSMA and SQL Server migration. I will try to update this frequently with new links.

SQL Server 2008 Migration Assistants Downloads
SSMA 2008 for Oracle
SSMA 2008 for Oracle automates the end to end database migration process from Oracle to SQL Server 2008. It performs assessment, schema migration, data migration and validation of migrated database objects.

SSMA 2008 for Sybase ASE
SSMA 2008 for Oracle automates the database migration process from Sybase ASE to SQL Server 2008. It performs assessment, schema migration, data migration and validation of migrated database objects.

SSMA 2008 for Access
SSMA 2008 for Oracle automates the database migration process from Access to SQL Server 2008. It converts Access database objects to SQL Server database objects and performs the data migration. It supports Access 97 - Access 2007 databases.

SSMA 2008 for Sybase PowerBuilder Applications
SSMA 2008 for Sybase PowerBuilder Applications is a tool that cuts the effort, cost, and risk of migrating PowerBuilder Applications designed for Sybase ASE to SQL Server 2008.

SSMA 2008 for MySQL v1.0 CTP1
Microsoft SQL Server Migration Assistant (SSMA) 2008 is a toolkit that dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2008 and SQL Azure.

SQL Server 2005 Migration Assistants Downloads
SSMA 2005 for Oracle
SSMA 2005 for Oracle automates the database migration process from Oracle to SQL Server 2005.

SSMA 2005 for Sybase ASE
SSMA 2005 for Sybase ASE automates the database migration process from Sybase ASE to SQL Server 2005.

SSMA 2005 for Access
SSMA 2005 for Access automates the database migration process from Access to SQL Server 2005.

SSMA 2005 for MySQL v1.0 CTP1
Microsoft SQL Server Migration Assistant (SSMA) 2005 is a toolkit that dramatically cuts the effort, cost, and risk of migrating from MySQL to SQL Server 2005.

SQL Server Migration Solutions Center

SQL Server Migration QuickStart

SSMA Forums
SQL Server Migration Forum

microsoft.public.sqlserver.migrationassistant

Webcasts

SQL Server 2008 for Oracle DBA

Migration Whitepapers

Migrating to SQL Server 2008
Guide to Migrating from Oracle to SQL Server 2008

Guide to Migrating from Sybase ASA to SQL Server 2008

Guide to Migrating from Sybase ASE to SQL Server 2008

Guide to Migrating from MySQL to SQL Server 2008

Guide to Migrating from Informix to SQL Server 2008

Migrating to SQL Server 2005

Guide to Migrating from Oracle to SQL Server 2005 using SSMA

Guide to Migrating from Sybase ASE to SQL Server 2005 using SSMA

Migrating from MySQL to SQL Server 2005

DB2 to SQL Server Migration Guide

Guide to Migrating from Microsoft Access to SQL Server 2005

Optimizing Microsoft Office Access Applications Linked to SQL Server

Solution Guides
Solution for Migrating from Oracle on UNIX to SQL Server on Windows

Solution Guide for Sybase/UNIX to SQL Server

SSMA Knowledgebase

SSMA Conversion Errors and Messages Reference

SAP on SQL Server
Running SAP Applications on SQL Server Blog

SAP with Microsoft SQL Server 2008 and SQL Server 2005 Whitepaper

SAP OS/DB Migration to SQL Server - FAQ

Siebel on SQL Server
Siebel on SQL Server 2008 Whitepaper

How to Articles
Migration to SQL Server Express using SSMA Oracle PDF

SQL Server 2008 Migration Whitepapers

2009-09-08T20:49:00.000-07:00

Microsoft has recently released whitepapers which help to migrate from other databases to SQL Server 2008.

Guide to Migrating from Oracle to SQL Server 2008
This white paper explores challenges that arise when you migrate from an Oracle 7.3 database or later to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms. The entire migration process using SQL Server Migration Assistant (SSMA) 2008 for Oracle is explained in depth, with a special focus on converting database objects and PL/SQL code.

Guide to Migrating from Sybase ASA to SQL Server 2008
This white paper explores challenges that arise when you migrate from a Sybase Adaptive Server Anywhere (ASA) database of version 9 or later to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms.

Guide to Migrating from Sybase ASE to SQL Server 2008
This white paper covers known issues for migrating Sybase Adaptive Server Enterprise database to SQL Server 2008. Instructions for handling the differences between the two platforms are included. The paper describes how SQL Server Migration Assistant, the best tool for this type of migration, can help resolve various migration issues.

Guide to Migrating from MySQL to SQL Server 2008
In this migration guide you will learn the differences between the MySQL and SQL Server 2008 database platforms, and the steps necessary to convert a MySQL database to SQL Server.

Guide to Migrating from Informix to SQL Server 2008
This white paper explores challenges that arise when you migrate from an Informix 11 database to SQL Server 2008. It describes the implementation differences of database objects and procedural code between the two platforms. Emulation of system functions is also discussed.

You can download these whitepapers from here.

S2SS0002: The following SQL statement cannot be converted:

2009-08-04T09:03:00.001-07:00

Explanation
S2SS0002: The following SQL statement cannot be converted:
/*
* ROLLBACK TRIGGER WITH
* RAISERROR 130506 @var
*/
Suggest you to convert these statements manually. Actually, rollback trigger statements are supported in sybase but not supported in sql server so it requires some changes.

You can refer to this article:
http://www.devarticles.com/c/a/SQL-Server/Migrating-from-Sybase-to-SQL-Server/1/

More Information
Error

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

O2SS0259: CURSOR type was converted to varchar(8000).

2009-08-04T09:00:00.000-07:00

Explanation
SQL Server returns cursors in different way than Oracle. SSMA converts cursor parameter to a VARCHAR parameter to keep procedure parameter count, but converts OPEN FOR statement inside stored procedure into a SELECT statement. This works in most cases when procedure is called from outside (client application).
In general, you can safely ignore this warning, because all places which require attention because of this migration issue (passing a cursor as a parameter) are marked with other errors with proper “migration hours” assigned.

More Information
Warning

Applies to
• SSMA 2005 for Oracle
• SSMA 2008 for Oracle

Not able to convert Parameterized Queries

2009-08-04T00:27:00.000-07:00

Symptom
SSMA Access is not able to convert parameterized queries.
Eg: “>=[forms]![Date_Range]![BeginDate]
Cause
The current version of SSMA doesn’t support parameterized queries.

Resolution
The current version of SSMA doesn’t support parameterized queries. Need to manually convert the queries.

Applies to
• SSMA 2008 for Access

Nothing to Process error when creating Assessment report

2009-08-04T00:17:00.000-07:00

Symptom
When user tries to create Assessment Report by click “Create Report” they may get error “There is Nothing to process”

Cause
We get this message if there are no objects in the node selected.

Resolution

As there are no objects in any of the schemas(as shown above) you are getting that message ”There is nothing to process”. You can create report only when you have objects(tables,Procedures etc) in the node selected.
In the above the user guest does not have access to any database objects. So there are no objects under guest schema. If the above user is given permission to those objects, then those objects will be visible in Metadeta explorer. Then you won’t get this error message.

More Information
Article Id: KBS2SS1010

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

UDF not converted by SSMA

2009-08-04T00:16:00.000-07:00

Symptom
When using ASE 15.0.2 version SSMA is not able to convert the User Defined functions.

Cause
SSMA does not support this Sybase version

Resolution
User defined functions have to be converted manually.
UDF were introduced in Sybase ASE 15.0.2and SSMA for Sybase v4.0 doesn’t support this version.

More Information
Article Id: KBS2SS1009

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

Text columns get truncated to a size of 32K

2009-08-04T00:14:00.000-07:00

Symptom
When using SSMA 2008 for Sybase for Data migration “text” columns may get truncated to a maximum of 32K

Cause
Text columns can get truncated because of default value of textsize property.

Resolution
Text columns can get truncated because of default value of textsize property. The default size of TextSize attribute of Oledb provider is 32K so if the data is more than 32K , its getting truncated in your case.
As a workaround, you can set the connection string in advanced mode to connect to Sybase.

In the connection string include a parameter ‘textsize’ as shown and assign it the maximum number of characters you have in all your text columns ,so that it won’t truncate data.
Sample connection string:
User ID=myID;PWD=abc;Provider=ASEOLEDB.1;Server=myserver;Port=5000;Database=master;
textsize=;
(Note: Change Provider according to your version)

More Information
Article Id: KBS2SS1008

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

Preventing regeneration of Identity columns

2009-08-04T00:11:00.000-07:00

Symptom
When doing data migration SSMA may regenerate the identity columns.

Cause
Keep Identity property in Project settings is set to false

Resolution
Setting the “Keep Identity” in the Migration tab of the Project settings to true.
Keep identity Specifies whether SSMA will preserve identity values when it inserts data into SQL Server. If this value is false, SQL Server will assign identity values. The default is true.
When Keep Identity is set to true, the data of the identity column resembles the data in the source database.
When Keep Identity is set to false, Identity columns are regenerated when inserting data in the SQL Server table and it will assign identity values taking seed and increment as 1

More Information
Article Id: KBS2SS1007

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

SSMA Extension pack not found on database server

2009-08-04T00:09:00.000-07:00

Symptom
When doing Data migration using SSMA user may encounter the following pop-up message:
Failed prerequisites list:
Common requirement: SSMA extension pack was not found on the database server. Install the SSMA extension pack to use the data migration.

Cause
This error message usually occur when
a) Extension pack is not installed on SQL Server instance
b) The Sysdb and ssma_syb databases doesn’t exist on the sql server instance which you are trying to connect or is not accessible because of lack of permissions.

Resolution
a) Install the extension pack on SQL server instance.
b) Ensure that the Sysdb and ssma_syb databases exists on the sql server machine which you are trying to connect and is accessible.

More Information
Article Id: KBS2SS1006

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

Timeout error when migrating data in a large table

2009-08-04T00:06:00.000-07:00

Symptom
Data Migration times-out for a particular table that contains around 100000 rows (the error message is ‘The command has timed out.’). Sybase OLEDB provider was used to connect to ASE 15.0.2 (on Windows 2003 32bit)

Cause
This is caused when using OLEDB provider to connect to Sybase, which fails when trying to read large tables using cursors.

Resolution
If you are using Oledb provider and facing the same issue, this can be resolved in two ways:
1) It’s failing when oledb provider is trying to read large tables using cursors.
You can give the connection string in Advance mode by setting property UseCursor=0 . Please see the below image for more details.

Sample Connection string:
User ID=sa;PWD=abc;Provider=ASEOLEDB.1;Server=myserver;Port=5000;Database=master;UseCursor=0;
(Please remember provider should be changed as per your sybase version)

2) You can consider changing the provider to ADO.Net provider. Please follow the following process to change the provider:Tools menu => Click on ProjectSettings=> Migration Tab and use the Provider dropdown to change the provider.

More Information
Article Id: KBS2SS1005

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

The table does not exist in SQL Server

2009-08-03T23:59:00.000-07:00

Symptom
When trying to migrate data using SSMA user may get the above error. When clicked on "Migrate Data", after entering the user name and password, it pops up "The table 'xxxxxx' does not exist in SQL Server. You must first convert the table and then load it into the database."

Cause
This error occurs when user tries to migrate the data before doing the ‘synchronize with the database’ operation.

Resolution
This error message generally occurs when the target table doesn’t exist on sql server database. After converting schema, you need to synchronize the table with the database before migrating the data. To do this you right click on the SQL Server database in Metadata explorer and click “Synchronize with database” menu. The table structure will not be created in the sql server database until you synchronize.

More Information
Article Id: KBS2SS1004

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

Data Migration reports migration for each row in table

2009-08-03T23:57:00.000-07:00

Symptom
During data migration of even very big tables, SSMA reports migration of each single data row. This slows down data migration.

Cause
ADO.NET Data Provider for Sybase has a bug in some versions, when AseCommand.ExecuteReader(CommandBehavior.SingleRow) returns AseDataReader which returns false from Read() method when called even though SQL statement returns one single row.

See http://search.sybase.com/kbx/changerequests?bug_id=485824 for details.

This bug is proved to be in the following Sybase provider version: 1.15.152.0.

Resolution
Use OleDb Sybase provider or install ADO.NET Data Provider of version 1.15.50.

More Information
Article Id: KBS2SS1003

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

Extension Pack is corrupted error

2009-08-03T23:55:00.000-07:00

Symptom
When SSMA is used the user can get an error message saying: Failed prerequisites list:
Common requirement: Extension Pack is corrupted or You do not have permission to use it.

Cause
Incorrect privileges of the user. User doesn’t have the privilege to run the following query: SELECT * FROM sysdb.sys.schemas where name = 'ssma_syb'

Resolution
Give the sufficient privileges to the user.
Depending on the operation user performing, following permissions are required:

For most operations it is enough to give a user an access to the database and grant a role ssma_syb.
If a user needs to perform a data migration, rights to create and start job will be needed (including CmdExec).
If a user needs to work with tester, we highly recommend giving the user full rights on ssmatesterdb_syb database.

More Information
Article Id: KBS2SS1002

Applies to
• SSMA 2005 for Sybase
• SSMA 2008 for Sybase

SQL Server cannot access the source table

2009-08-03T23:51:00.001-07:00

Symptom
When Migration Assistant is used to move a table from Sybase ASE 12.5 into MS Sql Server 2005 when they the data migration, they get this in the results pane:
Migrating data...
Analyzing metadata...
Preparing table dbo.table1...
Preparing data migration package...
Starting data migration agent...
Starting data migration...
The data migration agent is migrating table 'dbo.table1': >
dbo.table1, 0 rows total
SQL Server cannot access the source table.
Data migration complete.
0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.

Cause
This issue may occur due to following:
• User credentials used for data migration may not have SELECT permission on this table.
• There are lots of issues with different versions of Sybase providers so need to check the type and version of the driver you are using.

Resolution
The users usually get this error when they use OleDb provider for Sybase for data migration. To resolve this try using ADO.NET provider of version 1.15.50. You can set it up in Project Settings dialog using "Provider" dropdown.

More Information
Article Id: KBS2SS1001

Applies to
• SSMA 2005 for Sybase

Nothing to Process error when creating Assessment report

2009-08-03T23:29:00.000-07:00

Symptom

When user tries to create Assessment Report by click “Create Report” they may get error “There is Nothing to process”

Cause

We get this message if there are no objects in the node selected.

Resolution

As there are no objects in any of the schemas you are getting that message ”There is nothing to process”. You can create report only when you have objects(tables,Procedures etc) in the node selected.

For example, lets say the user guest does not have access to any database objects. So there are no objects under guest schema. If the above user is given permission to those objects, then those objects will be visible in Metadeta explorer. Then you won’t get this error message.

More Information

Article Id: KBO2SS1010

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot Convert Schema or Migrate data for SYSTEM schema

2009-08-03T23:28:00.000-07:00

Symptom

The user created their tables in the SYSTEM schema and when they try to use Convert Schema or Migrate data for the SYSTEM schema the options are greyed out.

Cause

SSMA does not migrate system objects

Resolution

SSMA considers all objects in SYS and SYSTEM schemas as system objects. SSMA does not migrate objects which is considered to be system objects of Oracle in SSMA intentionally. Creating objects in another schema and do the migration.

More Information

Article Id: KBO2SS1010

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot create New Test Case

2009-08-03T23:26:00.002-07:00

Symptom

When using SSMA tester while creating New Test Case users may see the below error.

Error:

Installed data not found.Error occurred during following command execution:

CREATE USER ssmatester oracle IDENTIFIED BY TESTER

Tester database installation failed

Cause

SSMA Tester requires ssmatester_oracle schema to be installed on the oracle database.

Resolution

SSMA Tester requires ssmatester_oracle schema to be installed on the oracle database. The error message usually comes when the execution of the command ‘CREATE USER ssmatester_oracle IDENTIFIED BY TESTER’ failed because of insufficient privileges of the user. Try to execute this command manually in Oracle database with the same user with which you are connecting to SSMA and grant all the required privileges and check with SSMA again. You must have Oracle privileges to create a new user and create objects in the schema of this user. More information about testing is provided in the ‘Testing Migrated Database Objects’ section in the help menu (F1) of the SSMA tool.

More Information

Article Id: KBO2SS1009

Applies to

SSMA 2005 for Oracle

Given key was not present in the dictionary error

2009-08-03T23:26:00.001-07:00

Symptom
During Data Migration using SSMA, tables may get an error “The given key was not present in the dictionary”

Cause
‘The given key was not present in the dictionary’ usually occur when the schema of the table in sql server doesn’t exactly match with that of oracle database.

Resolution
The error message ‘The given key was not present in the dictionary’ usually occur when the schema of the table in sql server doesn’t exactly match with that of oracle database.
There might be lot of reasons related with this:

- Tables have different column names(column names(case sensitive) should match both on oracle and sql server)

- Natural column order- the order of columns as the server(oracle or sql server)

- Data types

More Information
Article Id: KBO2SS1008

Applies to
SSMA 2005 for Oracle
SSMA 2008 for Oracle

SSMA Extension pack not found on database server

2009-08-03T23:25:00.000-07:00

Symptom

When doing Data migration using SSMA user may encounter the following pop-up message:

Failed prerequisites list:

Common requirement: SSMA extension pack was not found on the database server. Install the SSMA extension pack to use the data migration.

Cause

This error message usually occur when

a) Extension pack is not installed on SQL Server instance

b) The Sysdb and ssma_oracle databases doesn’t exist on the sql server instance which you are trying to connect or is not accessible because of lack of permissions.

Resolution

a) Install the extension pack on SQL server instance.

b) Ensure that the Sysdb and ssma_oracle databases exists on the sql server machine which you are trying to connect and is accessible.

More Information

Article Id: KBO2SS1007

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

The table does not exist in SQL Server

2009-08-03T23:24:00.000-07:00

Symptom

When trying to migrate data using SSMA user may get the above error. When clicked on "Migrate Data", after entering the user name and password, it pops up "The table 'xxxxxx' does not exist in SQL Server. You must first convert the table and then load it into the database."

Cause

This error occurs when user tries to migrate the data before doing the ‘synchronize with the database’ operation.

Resolution

This error message generally occurs when the target table doesn’t exist on sql server database. After converting schema, you need to synchronize the table with the database before migrating the data. To do this you right click on the SQL Server database in Metadata explorer and click “Synchronize with database” menu. The table structure will not be created in the sql server database until you synchronize.

More Information

Article Id: KBO2SS1006

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Unable to migrate Oracle to case sensitive SQL Server 2005

2009-08-03T23:20:00.000-07:00

Symptom

User may face problems when using SSMA to convert Oracle to case-sensitive SQL Server

Cause

SSMA does not support migration to case-sensitive SQL Server instance.

Resolution

Even though SSMA does not support migration to case-sensitive SQL Server, it may be used during migration though. Using SSMA is highly recommended because this way amount of manual work will be reduced significantly comparing it to migration without usage of SSMA.

More Information

Article Id: KBO2SS1005

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Deadlock occurs during execution of converted code.

2009-08-03T23:19:00.000-07:00

Symptom

Oracle function with DML was converted using SSMA. When the converted code is executed a deadlock may occur.

Cause

Extended procedure xp_ora2ms_exec2_ex is used for conversion of Oracle stored functions with DML and other statements prohibited in SQL Server UDFs. In this case, Oracle function is converted into implementation procedure (called $impl procedure) and this $impl procedure is called from generated UDF wrapper via xp_ora2ms_exec2_ex.

Extended procedure xp_ora2ms_exec2_ex, which is part of SSMA for Oracle Extension Pack, spawns a new connection to the same SQL Server and executes specified stored procedure in this connection. It is used in many Migration Issues throughout SSMA for Oracle. One of the parameters of this XP is bind_to_transaction which can be "true" or "false".

If bind_to_transaction is "true", xp_ora2ms_exec2_ex binds a new connection to external transaction. Otherwise, called stored procedure is executed in "automomous" transaction.

xp_ora2ms_exec2_ex cannot bind to external transaction if called from SQL Server UDF. That makes all calls to stored procedures made in spawned connection processed in "automomous" transaction, causing possible deadlocks and loss of transaction's atomicity.

Resolution

Replace function calls with direct EXECs of $impl procedures.

More Information

Article Id: KBO2SS1004

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Cannot see SCOTT schema in Metadata explorer

2009-08-03T23:17:00.000-07:00

Symptom

The user cannot see the SCOTT schema in the Oracle Metadata explorer. They can see SYS and SYSTEM schemas.

Cause

SCOTT schema is not selected in Project settings/Default Project Settings.

Resolution

The Loading System Objects page of the Project Settings dialog box lets you specify which Oracle system objects, SSMA converts and loads into SQL Server.

The Loading System Objects pane is available in the Project Settings and Default Project Settings dialog boxes:

To specify settings for all SSMA projects, on the Tools menu, select Default Project Settings, click General at the bottom of the left pane, and then click Loading System Objects.

To specify settings for the current project, on the Tools menu, select Project Settings, click General at the bottom of the left pane, and then click Loading System Objects.

More Information

Article Id: KBO2SS1003

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

Common Requirement: User does not have required permissions

2009-08-03T23:12:00.000-07:00

Symptom

A window will display when the user is trying to use SSMA connect to source Oracle database to load the Oracle metadata, saying that user does not have permissions: SELECT ANY TABLE, CREATE ANY PROCEDURE, SELECT ANY SEQUENCE, CREATE ANY TYPE,CREATE ANY TRIGGER

Cause

The above window will display when the user does not have one of these permissions:

"SELECT ANY TABLE",

"CREATE ANY PROCEDURE",

"SELECT ANY SEQUENCE",

"CREATE ANY TYPE",

"CREATE ANY TRIGGER

Resolution

The account that is used to connect to the Oracle database must have at least CONNECT permissions. This enables SSMA to obtain metadata from schemas owned by the connecting user.

To obtain metadata for objects in other schemas and then convert objects in those schemas, the account must have the following permissions: CREATE ANY PROCEDURE, EXECUTE ANY PROCEDURE, SELECT ANY TABLE, SELECT ANY SEQUENCE, CREATE ANY TYPE, CREATE ANY TRIGGER

Yes, you can ignore the warnings and click on continue so that SSMA will get the metadata from the schemas owned by the user. You can proceed with schema migration and data migration.

SSMA won't write anything to the source (oracle) database when you run schema migration and data migration.

More Information

Article Id: KBO2SS1002

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

2009-08-03T23:07:00.000-07:00

Symptom

When doing data migration you may get the following error. Following message may be displayed on the console.
Migrating data...
Analyzing metadata...
Preparing data migration package...
Starting data migration agent...
Starting data migration...
Exception during processing: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
Data migration complete.
0 table(s) successfully migrated.
0 table(s) partially migrated.
1 table(s) failed to migrate.

Cause

This error may be caused due to one the following reasons:
a) Oracle client provider is not installed on the sql server, you are trying to connect.

b) SSMA Extension Pack is not installed on SQL Server

c) Oracle Client may not be accessible to the DataMigrator utility from Extension Pack. This utility is started with the same account used to run SQL Agent.

Resolution

SSMA Data Migrator needs Oracle client provider installed on the SQL Server machine to perform data migration. When it does not find Oracle Client provider it gives this error.

1. Install the Oracle Client provider on your sql server and verify data migration.

2. Cross check Extension pack is installed on your sql server machine

3. Oracle Client may not be accessible to the BCPDataMigrator utility from Extension Pack. This utility is started with the same account used to run SQL Agent. Make sure this account has access to the Oracle client folder and it’s contents.

More Information

Article Id: KBO2SS1001

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle

SSMA Knowledgebase

2009-08-02T21:46:00.000-07:00

SSMA Forums
microsoft.public.sqlserver.migrationassistant

Knowledgebase
SSMA Oracle Knowledgebase HTML PDF

SSMA Sybase Knowledgebase HTML PDF

SSMA Access Knowledgebase HTML PDF

SSMA Conversion Errors and Messages Reference
SSMA Oracle Conversion Errors and Messages Reference HTML PDF

SSMA Sybase Conversion Errors and Messages Reference HTML PDF

How to Articles
Migration to SQL Server Express using SSMA Oracle PDF

SSMA Email Support Alias
ssmahelp@microsoft.com

Migration to SQL Server

2009-07-31T22:28:00.000-07:00

Are you planning to move your database from Oracle, Sybase and Access to SQL Server?
Do you need assistance and guidance on the migration?

Microsoft provides SQL Server Migration Assistants for Oracle, Sybase and Access is a complete suite of tools which cuts the effort, cost and risk of migration to SQL Server.

Microsoft has also created Migration Solutions Center which consists of whitepapers , prescriptive guidance and other resources to assist you with your migration.

SQL Server 2008 Migration Assistants Downloads
SSMA 2008 for Oracle
SSMA 2008 for Oracle automates the end to end database migration process from Oracle to SQL Server 2008. It performs assessment, schema migration, data migration and validation of migrated database objects.

SSMA 2008 for Sybase ASE
SSMA 2008 for Oracle automates the database migration process from Sybase ASE to SQL Server 2008. It performs assessment, schema migration, data migration and validation of migrated database objects.

SSMA 2008 for Access
SSMA 2008 for Oracle automates the database migration process from Access to SQL Server 2008. It converts Access database objects to SQL Server database objects and performs the data migration. It supports Access 97 - Access 2007 databases.

SSMA 2008 for Sybase PowerBuilder Applications
SSMA 2008 for Sybase PowerBuilder Applications is a tool that cuts the effort, cost, and risk of migrating PowerBuilder Applications designed for Sybase ASE to SQL Server 2008.

SQL Server 2005 Migration Assistants Downloads
SSMA 2005 for Oracle
SSMA 2005 for Oracle automates the database migration process from Oracle to SQL Server 2005.

SSMA 2005 for Sybase ASE
SSMA 2005 for Sybase ASE automates the database migration process from Sybase ASE to SQL Server 2005.

SSMA 2005 for Access
SSMA 2005 for Access automates the database migration process from Access to SQL Server 2005.

Migration Whitepapers
Guide to Migrating from Oracle to SQL Server 2005 using SSMA

Guide to Migrating from Sybase ASE to SQL Server 2005 using SSMA

Migrating from MySQL to SQL Server 2005

DB2 to SQL Server Migration Guide

The intent of this blog is to help you in using SSMA tools for database migration and sharing knowledge,expertise and best practices from migration community.