5

I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL. The small tables get converted but when it comes to the big one (slightly less than 4 GB), after about 1:30 hour of processing, it gives the following error:

(Elapsed Time = 00:01:24:32:197). Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.

Here is the output:

Migrating data...
Analyzing metadata...
Preparing table `big fmail user`.conversionlog...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 87489673 rows total
Table `big fmail user`.conversionlog data migration: 8760542 rows processed.
Table `big fmail user`.conversionlog data migration: 17510722 rows processed.
Table `big fmail user`.conversionlog data migration: 26249995 rows processed.
Table `big fmail user`.conversionlog data migration: 34998808 rows processed.
Table `big fmail user`.conversionlog data migration: 43757368 rows processed.
Table `big fmail user`.conversionlog data migration: 52494656 rows processed.
Table `big fmail user`.conversionlog data migration: 61254458 rows processed.
Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
 Errors: Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Table `big fmail user`.conversionlog data migration: 0 rows processed.
Completing migration of table `big fmail user`.conversionlog...
Migration complete for table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 0 rows migrated (Elapsed Time = 00:01:24:32:197).
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Information: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Data migration operation has finished.
    0 table(s) successfully migrated. 
    0 table(s) partially migrated. 
    1 table(s) failed to migrate.

Any suggestions as to what might cause this error, and how I should resolve it?

2
  • I'm wondering why I can convert smaller tables without problem but only this big one has problem(both of them are from the same db). Do you think it might be related to timeout parameters? Dec 3, 2012 at 5:51
  • This is a .NET error message. The connection appears to be getting closed after the data transfer, and it isn't being opened again before the truncate operation. Check/debug the application logic.
    – Jon Seigel
    Dec 3, 2012 at 14:26

3 Answers 3

5

I solved the issue. The problem was the value of the Data migration timeout in minutes property.

Go to Tools > Project Settings > General and in the left pane click on Migration and then in the right pane, in the section Misc, change Data migration timeout in minutes property to something larger (the default value was 15; I changed it to 300).

0
2

It seems that Batch size under Tools > Project Settings > General > Migration has an effect on this too. I changed the Data migration timeout in minutes to 300 too but still got that error. After lowering the value of Batch size (in this case to 1000 instead of 10000) the migration succeeded.

1

I had the same error in SSMA while migrating a table from Oracle to SQL Server. The table had 1000 million records and the migration used to fail after 90% of transfer.

By making the changes in project setting BATCH SIZE under TOOLS > PROJECT SETTINGS > GENERAL> MIGRATION to 1000 and TIME OUT to 1000, it completed successfully.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.