Bug #31413 Memo fields not working in MSSQL linked server
Submitted: 4 Oct 2007 20:57 Modified: 18 Jun 2008 12:36
Reporter: Jim Breffni Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.20 OS:Microsoft Windows (XP SP2)
Assigned to: CPU Architecture:Any
Tags: odbc longtext mssql linked server

[4 Oct 2007 20:57] Jim Breffni
Description:
Using 
SQL Server 2005 9.00.3054.00
MyODBC 3.51.20
MySQL 4.1.22

When I do a select * on a linked server from mssql to mySQL via odbc I get this error for any table with a longtext field in it:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

How to repeat:
Do a select * on a mySQL table containing a longtext field via odbc from sql server 2005 Management Studio.
[5 Oct 2007 0:32] Miguel Solorzano
Thank you for the bug report. How you process to connect with MySQL server
via MyODBC within SQL Server 2005 Management Studio.?. Thanks in advance.
[5 Oct 2007 13:20] Jim Breffni
Process for connecting to mySQL from SQL Server 2005 Management Studio:

1.  Create System DSN using Windows ODBC Data Source Administrator
2.  Add linked server to MSSQL:
EXEC sp_addlinkedserver 'yourNameForLinkedServer', 'MySQL', 'MSDASQL', Null, Null, 
  'Driver={MySQL ODBC 3.51 Driver};DB=yourdatabasename;SERVER=serverrunningmysql;uid=yourmysqllogin;pwd=yourmysqlpassword

3.  To issue a select:
SELECT * FROM OPENQUERY(yourNameForLinkedServer, 'select * from mySQLdatabase.mysqlTable')
[12 Feb 2008 9:31] Tonci Grgin
Hi Jim. I think I see where the problem lies... Microsoft DB SW is unable to handle data types longer than 32bit. Anyway, I am not able to repeat your problem with 3.51.23 (don't remember the revision number) even though it does not have "Limit column size to signed 32-bit range" like 5.1 has. I presume this was worked around while working on FoxPro problems lately. This is in no case our bug but we do provide workaround.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug31413 (Id int unsigned not null auto_increment primary ke
y, someverylt longtext);
Query OK, 0 rows affected (0.08 sec)

See attached image too.
[12 Feb 2008 9:32] Tonci Grgin
MSSQL project

Attachment: Bug31413.JPG (image/jpeg, text), 75.97 KiB.

[18 Jun 2008 12:24] Cosming Gheorghita
Hi,

Unfortunately this bug is still present. The selectg works well on an empty table, but it doesn't work at all when there is at least one row in the table... Is there any other workaround for this issue?

10q,
CG
[18 Jun 2008 12:36] Jim Breffni
The workaround I used was to change my longtext fields to text fields.

This was only possible because OUR data for the longtext fields can fit into a text field size.
[18 Jun 2008 13:10] Cosming Gheorghita
Thank you Jim, you've saved me. I've made a test and it seems that it works indeed with TEXT and MEDIUMTEXT fields. Indeed the 2^32 limit gets us all... Looks like I'll have to 'downgrade' the LARGETEXT fields to MEDIUMTEXT.

Thanks once more,
CG
[28 Jan 2009 9:40] Muhamamd Kashif
I am using Mysql 5 with sql server 2005 as a linked server with ODBC connector (you can download from here http://dev.mysql.com/downloads/connector/odbc/5.1.html ) on windows XP SP2. I got similiar longtext field issues, it's temporary fixed. 

Solution: 
1 - On Windows XP just go to Administrative Tools -> Data Source(ODBC) -> Select your ODBC connection which you have used for Linked Server. 
2- Click on "Details" button 
3- Click on "Flag 3" Tab
4- Checked the option "Limit Column Size to 32-bit range"

This will limit longtext field to 32-bit range so that your query will work.

that's it. Your query will work now without modifying the schema, as i was not in favor of modifying existing schema. 

Note: Please make sure that you are not loosing data, in my case the actual data was in range of 32-bit . So it worked for me.