Post reply

Connection to mySQL

  • January 7, 2003 at 4:32 pm

    #64489

    I'm running mySQL 4.0.7 nt on MS Windows 2000 Pro and having trouble connecting from MS SQL 2000 to mySQL.

    I successfully use the following script to link to mySQL

    EXEC sp_addlinkedserver

    @srvproduct = 'MSDASQL',

    @provider = 'MSDASQL',

    @server = 'localhost',

    @datasrc = 'mySQL_Test'

    GO

    But when I tried to select a record in mySQL, the following message appear.

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.

    This is happening on the same machine.

    Please shed some light

  • January 7, 2003 at 6:41 pm

    #445812

    New findings

    I manage to create Link Servers via SQL Enterprise Manager.

    Created 'LOCALHOST' with provider 'MySQL.OLEDB Provider'

    I could view the tables but could not select the records.

    When I tried to Select * from Localhost...testname

    the following message appear

    Server: Msg 7313, Level 16, State 1, Line 1

    Invalid schema or catalog specified for provider 'MySqlProv'.

    Please shed some light

  • January 8, 2003 at 4:08 am

    #445823

    I believe your problem is that MySQL does not have an object owner part of the name. It is db_name.object_name. Try this

    Select * from Localhost..testname

    or

    Select * from Localhost.db_name.testname

    or

    Select * from openquery(Localhost,'select * from db_name.testname')

    or

    Select * from openquery(Localhost,'select * from testname')

    And see which works if any.

  • January 8, 2003 at 2:48 pm

    #445885

    Thanks Antares686

    I could select rows using openquery.

    Any idea how can I 'Insert' or 'Update'

    I only manage to update using

    Select * from Openquery (Localhost,'Insert into

    values [values]')

    Although this insert a record into the table but it also return a message. Is there a better way to insert ?

    Thanks

  • January 8, 2003 at 3:14 pm

    #445889

    I am not sure as I have only use MySQL once so far and that was testing over a year ago. You might try like so.

    You may need to start MSDTC (not sure if it does help here). And in EM look at the options on the last tab for the linked server, you may need to turn DATA ACCESS on.

    Try

    INSERT INTO OPENQUERY(localhost,'SELECT columnnames FROM tablename') VALUES (relatedvaluesincolumnorder)

  • January 8, 2003 at 11:35 pm

    #445917

    I've tried but it return the following error

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'MySqlProv' reported an error. The provider did not support an interface.

    I'm using MYOLEDB3 is there a later version ?

  • January 9, 2003 at 4:45 am

    #445922

    Unfortunately that is where things get fuzzy for me and I don't know for sure what to do with MySQL as a linked server. I also don't have a server setup here to test anything.

    However based on things I have seen this may not have a support method. You could possible try using the sp_OACreate methods to build a COM connection using the OLE provider and then a dynamic insert string to do this. However unless you are needing to insert the data into the SQL Server as well it may work better to do from your app. Can you explain what you are trying to accomplish overall as there are drawbacks to Linke Servers that may make it better to avoid anyway?

  • January 9, 2003 at 4:23 pm

    #446014

    Here's the scenario

    I need to be able to connect to mySQL from MSSQL2000.

    Then create several store proc in MSSQL2000 to select, insert and update data in mySQL.

    The problem is mySQL is the MAIN database and driving all application. The application that I'm using runs on MSSQL2000.

    If only mySQL could replicate down to MSSQL2000, or my client change their db to MSSQL2000, else I've got to find a way to run queries from MSSQL2000 to mySQL.

    PLEASE HELP

    William

  • November 21, 2006 at 8:32 am

    #673239

    Hallo ,

    sorry for ask. But how did you managed the linked server to mysql. I have only problems and still white screens when i do this in the EM.

    Thanks for help

    Thomas

Viewing 9 posts - 1 through 8 (of 8 total)

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