MySQL Bugs: #39555: views and store procedure not converted into mysql database from mssql 2005.

Bug #39555	views and store procedure not converted into mysql database from mssql 2005.
Submitted:	20 Sep 2008 10:12	Modified:	23 Oct 2008 11:20
Reporter:	M G Sukhon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	1.1.12	OS:	Windows (Vista)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	MSSQL jdbc connnection string

Description:
Hi,

I am trying to convert mssql database to mysql database using MySQL GUI 5.0 migration tool. but I could not esteblished connection to mssql server as a source database. I was using
Host: 10.0.0.9 (My PC IP where MSSQL Server 2005 installed)
User: sa
password: root 

I am using user=sa;password=root to connect to mssql server using sql server management tools and this is ok. SO, why this is not working in my PC? Please let me know ASAP.

Thanks,
Sukhon

How to repeat:
I am not clear.

Thank you for a problem report. What error message do you get when you try to connect to MS SQL? What exact version of JDBC driver for MS SQL do yo use.

Mssql server connected by enabling TCP/IP and namedPipe. but MySql not connecting: Please see error below and let me know ASAP.

Driver class MySQL JDBC Driver 5.0
Opening connection ... 
Connection jdbc:mysql://127.0.0.1:3306/?user=root&password=&useServerPrepStmts=false&characterEncoding=UTF-8
The connection to the target database could not be established (error: 0).
ReverseEngineeringMysqlJdbc.getVersion :Unknown initial character set index '48' received from server. Initial client character set can be forced via the 'characterEncoding' property.

Details: 
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.Connection.configureClientCharacterSet(Connection.java:2345)
com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3913)
com.mysql.jdbc.Connection.createNewIO(Connection.java:2683)
com.mysql.jdbc.Connection.<init>(Connection.java:1531)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:141)
com.mysql.grt.modules.ReverseEngineeringGeneric.getVersion(ReverseEngineeringGeneric.java:161)
sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

But Mysql Migration could not convert views and store procedure into mysql database from mssql 2005. Please Let me know ASAP.

mysql server connected by the following way

my.cnf: I have changed

character-set-server = latin1
collation-server = latin1_general_ci

into

character-set-server = utf8
collation-server = utf8_general_ci

Are you sure you have TCP/IP enabled on your SQL Server and know the actual port which it is listening?. You can verify that using xp_readerrorlog. Thanks in advance.

Hi,
Thank you for response.

Mysql migration tools is connecting both mssql server 2005 as source database and mysql as target database by enbling TCP/IP, namedPipe. But This tools cannot convert mssql server 2005 views & store procedures into mysql views & store procedures. This gives message to change the syntax. This cannot do it. Please let me know whether the problem is clear.

Thanks
Sukhon

Could you please provide a sample test create script of views and procedures for SQL Server 2005 which can't converted to MySQL by Migration tool Kit?. Thanks in advance.

Hi,

Here is my sample MSSQL 2005 stored procedure:

*****************

USE [Stock]
GO
/****** Object:  StoredProcedure [dbo].[asp_Admin_DeleteDividendSetting]    Script Date: 10/13/2008 15:30:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[asp_Admin_DeleteDividendSetting]
	@ID INT	
AS
BEGIN
	DELETE FROM [DividendSetting]
      WHERE [ID] = @ID
END
*****************
Please let me know.

Thanks

Thank you for the feedback.

Stored procedure cannot automatically be converted by the Migration Toolkit. This is simply not possible due to various SP languages. You have to do this manually.

For views the situation is that they usually are converted. If you have found a case where this does not happend then please provide it here so I can take a look.

I'm sorry, not even views can be converted automatically due to various differences in the used SQL syntax. So this is not a bug at all.