Tuesday, August 4, 2009

S2SS0002: The following SQL statement cannot be converted:

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).

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

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

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

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

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

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

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

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

Monday, August 3, 2009

The table does not exist in SQL Server

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

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

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

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

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

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

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

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

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

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

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.

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

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

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.

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

Sunday, August 2, 2009

SSMA Knowledgebase

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