Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  kami Dec 3 '12 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 '12 at 14:26
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Please mark this as the accepted answer. –  Paul White Dec 4 '12 at 6:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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