MySQL Bugs: #12446: ODBC not reading field schema correctly in MS SQL DTS

Bug #12446	ODBC not reading field schema correctly in MS SQL DTS
Submitted:	8 Aug 2005 20:51	Modified:	28 May 2013 13:04
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Linux (Linux/SQL 2000/Windows 2000)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
I installed MySQL ODBC 3.15.11.2 on my computer.  I am creating a DTS package in MS SQL server 2000.  When I create the connection to my database table in MySQL, the fields that are defined as varchar are being represented as longtext in the DTS connection.  There are four varchar fields:  one is varchar(32), one is varchar(255), one is varchar(32), and one is varchar(255).  All four fields appear as longtext in my DTS package.  The other fields, which are not varchar, are fine.  When I try to import into the MySQL table, I get a "Query-based insertion or updating of BLOB values is not supported" error.

The computer I use to connect to Enterprise Manager is running Windows 2000.
 

How to repeat:
Import from MS SQL table into MySQL table.  Fields that are varchar in MySQL are showing as longtext in MS SQL DTS.

I am running MySQL 4.0

Found a solution in bug #6452
 
FOUND A SOLUTION - in the configure DSN window under the advanced tab, check "don't optimize column width."

If you're using windows.

Go to Control Panel
Admin Tools
Data Sources (ODBC)
locate your DSN
Hit configure
Go to the Advanced tab
and check the Don't optimize column width

Once I did this it started working.

well,
it did solve some of the problem, most of the tables i had problems with transformed ok, but a few are still giving me the same error.

I have the same problem. 

"source" server: SQL Server 2000 SP4 on Windows Server 2003
"target" server: MySQL 5.0.24a on Windows Server 2003
MyODBC version: 3.51.21
Transfer method: DTS package. 

When I try to transfer data to any TEXT field in a table on MySQL, i get the error  "Query-based insertion of blob values is not supported". 

The option mentioned before in this bugreport - checking "Don't optimize column width" - is not available because that option is not there in version 3.51.21. 

Before i had MyODBC v 3.51.14, but checking "Don't optimize column width" didn't do the trick for me.

Hi all. This seems to be the problem with server metadata where *CHAR fields are returned with BINARY flag set. Please use latest version of MyODBC driver and re-test.

I have upgraded to MySQL ODBC driver v. 3.51.22. 

I still get the same error when transferring data from SQL Server 2000 SP4 to MySQL 5.0.24a through a SQL Server DTS Job: 

---------------------------
Copy Data from tblTest to `test`.`tbltest` Task
---------------------------
The number of failing rows exceeds the maximum specified.

Query-based insertion or updating of BLOB values is not supported.

Unfortunately MSSQL server 2005 does not use DTS but SISS. I spent several hours trying to make project that will do the same but I was unsuccessful... Can anyone post simple DTS packet for me so I can try importing it into 2005?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.