Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ramsfield
Starting Member
9 Posts |
 Posted - 2004-09-13 : 19:40:31
|
| I have been trying to convert some information in my MS SQL server database to MySQL using DTS, so that the information can be dumped to MySQL on an automated schedule. I am having a problem with a few fields. Specifically text fields which contain over 8000 characters. When the DTS package runs, I receive an error indicating that the query based insertion of Blob fields is not supported. I have tried CASTing the field, but because some of the fields contain more then 8000 characters I still receive the error. I can use Access to "pass" the data from MS SQL server to MySQL. I can also use SQLyog to suck data from MS SQL and push it to MySQL. Why won't DTS work? Is there any way to make this work from MS SQL / DTS? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-09-13 : 20:23:46
|
| It all depends on the ODBC or OLEDB driver you're using for MySQL. It's possible that it doesn't (correctly) support MySQL's method for handling BLOB data. Try googling for "MySQL drivers" and see what's available. In all likelihood you'll find another driver that will work. If not, there's really nothing wrong with the method you're using now. Worst case is that you split the job into two, independent jobs. One does the export to an intermediate format, the other does the import into MySQL. You might still be able to do it in one DTS package, as two separate data transformations. |
 |
|
|
ramsfield
Starting Member
9 Posts |
Posted - 2004-09-14 : 00:02:48
|
| Unfortunately, I have tried two jobs as you suggested. I first downloaded to a text file, then tried pumping the data from the text file, I receive the same error message.I am currently using the latest driver from MySQL, do you think another vendors driver may work?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-09-14 : 07:59:21
|
| I would say yeah, you have nothing to lose. I've heard of similar experiences with Oracle drivers (I've had my own problems with it, but not BLOB related) and usually a different driver will fix it.If you haven't already, try reading up on how MySQL handles BLOBs, 99.99% of the time a database that supports BLOBs has some kind of weird handling that is not standard and very difficult or impossible to support via ODBC. ODBC's BLOB support is lacking at best. OLE DB will be a better choice, but even then BLOB support depends entirely on how the vendor implements it. You should also try posting in a MySQL forum. Try here:http://dbforums.com/ |
|
|
|
|
|
|
|
|
MSSQL -> MySQL problems