MySQL Bugs: #26567: Error import data from an SQL 2005 server (trial edition)

Bug #26567	Error import data from an SQL 2005 server (trial edition)
Submitted:	22 Feb 2007 13:29	Modified:	23 Feb 2007 15:22
Reporter:	Octavian IDITA	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.9	OS:
Assigned to:		CPU Architecture:	Any
Tags:	sql 2005

Description:
I tried to import data from an SQL 2005 (trial edition , 180 days) to an MySql database (MySql Administration version is 1.2.9). After I select the schema of the source database it gives me tha following error

Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://127.0.0.1:1434/RldTest;user=sa;password=*******;charset=utf-8;domain=
Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://127.0.0.1:1434/RldTest;user=sa;password=*******;charset=utf-8;domain=
The schema could not be reverse engineered (error: 0).
ReverseEngineeringMssql.reverseEngineer :I/O Error: Connection reset
Details: 
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2277)
net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:599)
net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:331)
net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:178)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:96)
com.mysql.grt.modules.ReverseEngineeringGeneric.getVersion(ReverseEngineeringGeneric.java:116)
com.mysql.grt.modules.ReverseEngineeringMssql.reverseEngineer(ReverseEngineeringMssql.java:164)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

Any help will be appreciated.

How to repeat:
Install SQL 2005 trial edition and MySql on the same computer and try to import in Mysql from SQL 2005

Thank you for the bug report. Could you please provide the below
information:

- The SQL Server is configured with mixed authentication?
- The SQL Server has the TCP/IP protocol enabled?
- Which is the TCP/IP port showed by xp_readerrorlog?

Thanks in advance.

Please find the information you request :
- The SQL Server is configured with mixed authentication?   YES
- The SQL Server has the TCP/IP protocol enabled?           YES
- Which is the TCP/IP port showed by xp_readerrorlog        1434  

Thank you

Thank you for the feedback. I was able to connect and migrate a test database
with an empty table using SQL Server 2005 developer edition.

Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://skybr:1433/test25;user=sa;password=XXXXX;charset=utf-8;domain=
Fetching schemata list.
SELECT TABLE_SCHEMA AS SCHEMANAME,  max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA
Return schemata list.
Schemata names retrieved successfully.
Initializing JDBC driver ... 
Driver class MySQL JDBC Driver 5.0
Opening connection ... 
Connection jdbc:mysql://localhost:3307/?user=root&password=&useServerPrepStmts=false&characterEncoding=UTF-8
Getting version information ... 
Initializing JDBC driver ... 
Driver class MySQL JDBC Driver 5.0

--------------------------------------------------------------------------------
-- MySQL Migration Toolkit Report                                             --
--                                                                            --
-- Title:   Summary Of The Migration Process                                  --
-- Date:    2007-02-23 09:31                                                  --
--------------------------------------------------------------------------------

1. Schema Migration
-------------------

  Number of migrated schemata: 1

  Schema Name:            test25_dbo
  - Tables:               1
  - Views:                0
  - Routines:             0
  - Routine Groups:       0
  - Synonyms:             0
  - Structured Types:     0
  - Sequences:            0

  Details:

  - Tables
      `test25_dbo`.`t1`
      -----------------
  - Views
  - Routines
  - Routine Groups
  - Synonyms
  - Structured Types
  - Sequences

2. Data Bulk Transfer
---------------------

      `test25_dbo`.`t1`
      -----------------
          0 row(s) transfered.

It's seem it is not a bug. Is it my fault. In the screen with Connection Parameters of the MS SQL Server I should fill the 'Hostname' field with the name of computer instead of 'localhost' and the Port Number should be 1433 instead of 1433.
I noticed this in your answer and after I tried it works.

Thank you

As the post origitator 'Octavian IDITA' ultimately concluded, the issue is not a bug. I too experienced the same error with reverse engineering of MS-SQL but was able to connect the MS-SQL server via the 'Connecting to Servers' screen. The only way that I could establish a successful connection was using the MS-SQL configured listen port setting (127.0.0.1 [localhost] on port 1434) as was created during the MS-SQL Server installation. The problem however is that the network and client configuration TCP/IP protocol must be enabled to allow the MySQL Migration Toolkit actual access to the database. I'm not sure exactly why the initial test connection to the listen port works but based upon actual experience, the MySQL Migration Toolkit won't perform the reverse engineering unless the TCP/IP protocol is enabled *and* mixed mode authentication is enabled.

---

Content posted to:
http://forums.mysql.com/read.php?104,181246,181246#msg-181246

Follows are some tips that finally resoved an issue for which I could successfully connect to the MS-SQL server via MySQL Migration Toolkit during the connection test but for which the subsequent reverse engineering phase failed. Initially, the only way I could connect to the MS-SQL server was using 'localhost' [127.0.0.1] as the hostname and port 1434 which was configured by MS-SQL Server during installation as the 'listen' hostname and port. If I set the client computer name and port 1433, the connection test failed.

The resolution is to configure MS-SQL Server to enable the network and client configuration TCP/IP protocols via the 'SQL Server Configuration Manager' with the 'SQL Server 2005 Network Configuration' and 'SQL Native Client Configuration' child objects. The TCP/IP protocol must be enabled for both and each respective property configured (right-click TCP/IP for properties). Note the 'Protocol' and 'IP' tabs and configurations. After enabling the network and client TCP/IP protocols a test connection could be established using the computer name as the hostname and port 1433 *and* the reverse engineering phase successfully completed. I'm not sure if both network and client TCP/IP protocols must be enabled but as configured all worked for me.

SQL Server Configuration Manager configuration settings screen capture image
http://69.65.105.60/temp/mssql_config.gif

Additional Notes:

MS-SQL Server Mixed Mode (Windows Authentication or SQL Server Authentication) must be enabled to allow MySQL Migration Toolkit to establish a connection with a connection string.

SQL Server Management Studio > Object Explorer >
[Server instance] > right-click > properties >
Security > Server Authentication > Windows Authentication or SQL Server Authentication

Microsoft Access: note chapter 13 for the MySQL Migration Toolkit compiled HTML help file.

Preparing a Microsoft Access Database for Migration

1. Tools > Options > View [tab] > Show [section]
Check checkboxes: 'System objects' and 'Hidden objects'

2. Tools > Security > User and Group Permissions
Control-click Select MSysObjects, MSysQueries, and MSysRelationships (highlight all)
or perform individually and sequentially
Check the 'Administer' checkbox to configure each selected item
Click 'Apply' button