Insert into openquery( MySql)

[Tinkerers]

Hi,

I've been struggling all day attempting to perform a simple insert from SQL Server 2005 to MySql. I can perform the insert and it completes succesfully in query analyzer, but here's the problem: The one field in the MySql db that is a TEXT data type never populates. Here's the query:

insert into openquery(Archer,'
select Orig_Alarm_ID,ProbableCause,Item from archer.archer_output')
select 123473,'wide area network','router'

The field labeled ProbableCause is a TEXT data field and when I do a select to see what I've just inserted, the ProbableCause field is null. I am using the native MySql 5.1 driver. I've also tried a Microsoft OLE for ODBC driver with same results.

If I perform an insert using a 3rd party MySql tool, the insert works perfectly !!!

Ideas?

Thanks,
Paul

 

[sblocher]

Never done this against MySQL, but i've had explicitly type the source fields before. Not sure what type you need to map correctly to the "text" type in MySQL, but you can try a couple.

Like this:

insert into openquery(Archer,'
select Orig_Alarm_ID,ProbableCause,Item from archer.archer_output')
select 123473,convert(varchar(500),'wide area network'),'router'

 

[Tinkerers]

Well I tried casting and converting to every possible combination and still no luck.

We solved the problem by using the MySql.exe app and passing the sql insert parameters to it on the command line. Worked great and very fast too.