sql varchar(max) to Mysql (Longtext)

Question

Post reply

sql varchar(max) to Mysql (Longtext)

sreddyv26

Ten Centuries

Points: 1038
More actions
November 25, 2009 at 9:14 am

#156445

I had similar tables in both sql 2005 and mysql
in that one column has lontext data type in mysql
and similar column has varchar(max) data type in sql 2005
I can transfer the data from mysql to sql for that column but i can't transfer data from sql to mysql for the same column (It isinserting blank data in mysql column)
quite wondering if any body have answer for this

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

try this

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5637cfef-6078-4bc0-bce2-a4175b45e23a

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sreddyv26 Ten Centuries Points: 1038 More actions · Answer 2

Thanks for the information .But My issue is when i run my sql statement it is working fine but inserting blank data in longtext column field on MYSQL

SQL Table --- Test

ID - INT

COLUMNLONG - VARCHAR(MAX)

MYSQL Table -- Test

ID - INT

COLUMNLONG - LONGTEXT

I got some data in SQL (Test)

when I try to run following statement

Insert openquery(LinkServerMysql,'select ID,COLUMNLONG from TestTable ')

select ID,COLUMNLONG from TestTable

I supoosed to expect something in COLUMNLONG in MYSQL After i ran above statement

But for some reason it is inserting blank data

If i change COLUMNLONG to Varchar(8000) in MYSQL then it is ok

Is anybody can give me solution

Regards

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

What MySQL storage are you using? And you could use ADO.NET with the current provider.

Kind regards,
Gift Peddie

sreddyv26 Ten Centuries Points: 1038 More actions · Answer 4

Hi

Thanks for getting back to me

I created created linked server by using MSDASQL provider and running that statement from TSQL

with current restrictioons i cant' change other provider

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

I wanted to add a quick additional note. I had several columns that were "longtext" in my MySQL database. When I would try to copy those tables I was getting a "catastrophic error".
I finally got it to work by putting a LEFT on the LONGTEXT column. For example:

SELECT * FROM openquery(

'SELECT UNIQUE_ID,

LEFT(DISSERTATION, 4000) AS DISSERTATION'

)

Hi,

Check the above if it takes you in the direction to solve your problem, I got it from a long blog post from another developer at the MSDN forums. It comes with many users added options dealing with moving MySQL to SQL Server Express.

http://studentclub.ro/lucians_weblog/archive/2007/04/30/migrate-from-mysql-to-ms-sql-server-2005-express-edition.aspx

Kind regards,
Gift Peddie