2

Working on setting up a new MySQL database for a website, and I'm trying to figure out how to transfer over long strings.
I have quite a few MSSQL tables that have Varchars ranging from 500 to 2000.
Obviously in MySQL these have to be stored in a Text field, which is a BLOB.
If I query a table on the MySQL with a Text field, it seems to return the data just fine. But if I Try to do an Insert into the table with the Text field, it returns an error saying [OLE/DB provider returned message: Query-based insertion or updating of BLOB values is not supported.]

Is there a way to make this work, or will I have to do it by flatfile transfer?

AndyD273
  • 207
  • 1
  • 11

2 Answers2

4

So, after looking around a lot, I noticed that there are two versions of the ODBC Connector, 3.51.28, and 5.1.8 ( http://dev.mysql.com/downloads/connector/odbc/ ).

Not sure why they are maintaining two versions, but whatever. Installed it, reset it up in DNS, and was able to insert 1000+ character string in.

Now hopefully there aren't any other bugs with this new one.

On a side note for future generations, when I set it up in the DNS I didn't check any of the boxes in Details options. I don't know if there are any that need to be checked, but I guess I'll figure that out when something doesn't work.

AndyD273
  • 207
  • 1
  • 11
4

This problem occured few days ago in my MySQL linked server. ODBC Driver version 5.1.

MySQL connection is made using system DSN, not connecion string in linked server properties. The solution: in DSN connection options switch to tab "Cursors/Results" and uncheck "force use of forward-only cursors" box.

Solution based on this: http://support.microsoft.com/default.aspx?scid=kb;en-us;175245

BartekR
  • 141