Accessing MySQL data from MS SQL Server 2000


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Accessing MySQL data from MS SQL Server 2000

  1. #1
    Andy H Guest

    Accessing MySQL data from MS SQL Server 2000


    We are attempting to access MySQL data from MS SQL Server 2000
    Using OLE DB Provider for MySQL under Windows 2000.

    We are attempting to open the datasource as follows:
    Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"


    i.e. in query analyser
    select * from opendatasource('MySQLProv','Data Source=<DSN name>;User ID=root').<database>.dbo.<table>

    We get the response:
    Server: Msg 7313, Level 16, State 1, Line 1
    Invalid schema or catalog specified for provider 'MySQLProv'.

    We have tried to access this table with and without catalog referencing.
    Does anyone have a simple example that allow us to do this?

    Any help would be much appreciated.


  2. #2
    Chris Hylton Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Don't have an example, never done this with MySQL before, but if you have
    an OLEDB data provider...have you looked at creating a Linked Server...there
    is info on this in BOL if you need help setting one up.

    Chris

    "Andy H" <andyhud1@netscape.net> wrote:
    >
    >We are attempting to access MySQL data from MS SQL Server 2000
    >Using OLE DB Provider for MySQL under Windows 2000.
    >
    >We are attempting to open the datasource as follows:
    >Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >
    >
    >i.e. in query analyser
    >select * from opendatasource('MySQLProv','Data Source=<DSN name>;User ID=root').<database>.dbo.<table>
    >
    >We get the response:
    >Server: Msg 7313, Level 16, State 1, Line 1
    >Invalid schema or catalog specified for provider 'MySQLProv'.
    >
    >We have tried to access this table with and without catalog referencing.
    >Does anyone have a simple example that allow us to do this?
    >
    >Any help would be much appreciated.
    >



  3. #3
    Andy H Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Thanks for your response Chris.
    We have tried this using the following:
    Provider - "MySqlProv"
    DSN - DSN that was set up with MyODBC


    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Don't have an example, never done this with MySQL before, but if you have
    >an OLEDB data provider...have you looked at creating a Linked Server...there
    >is info on this in BOL if you need help setting one up.
    >
    >Chris
    >
    >"Andy H" <andyhud1@netscape.net> wrote:
    >>
    >>We are attempting to access MySQL data from MS SQL Server 2000
    >>Using OLE DB Provider for MySQL under Windows 2000.
    >>
    >>We are attempting to open the datasource as follows:
    >>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>
    >>
    >>i.e. in query analyser
    >>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User ID=root').<database>.dbo.<table>
    >>
    >>We get the response:
    >>Server: Msg 7313, Level 16, State 1, Line 1
    >>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>
    >>We have tried to access this table with and without catalog referencing.
    >>Does anyone have a simple example that allow us to do this?
    >>
    >>Any help would be much appreciated.
    >>

    >



  4. #4
    Andy H Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Following on from this issue, this works:

    Using myODBC driver DSN create a linked server that references it.
    Make sure the provider is an OLE DB Provider for ODBC and query in the following
    way: -

    select * from openquery(<linked server name>, <pass through query>)

    Seems as though the OLE DB Provider for MySQL is not entirely compatible
    with SQL Server 2000

    "Andy H" <andyhud1@netscape.net> wrote:
    >
    >Thanks for your response Chris.
    >We have tried this using the following:
    >Provider - "MySqlProv"
    >DSN - DSN that was set up with MyODBC
    >
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Don't have an example, never done this with MySQL before, but if you have
    >>an OLEDB data provider...have you looked at creating a Linked Server...there
    >>is info on this in BOL if you need help setting one up.
    >>
    >>Chris
    >>
    >>"Andy H" <andyhud1@netscape.net> wrote:
    >>>
    >>>We are attempting to access MySQL data from MS SQL Server 2000
    >>>Using OLE DB Provider for MySQL under Windows 2000.
    >>>
    >>>We are attempting to open the datasource as follows:
    >>>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>>
    >>>
    >>>i.e. in query analyser
    >>>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User

    ID=root').<database>.dbo.<table>
    >>>
    >>>We get the response:
    >>>Server: Msg 7313, Level 16, State 1, Line 1
    >>>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>>
    >>>We have tried to access this table with and without catalog referencing.
    >>>Does anyone have a simple example that allow us to do this?
    >>>
    >>>Any help would be much appreciated.
    >>>

    >>

    >



  5. #5
    Chris Hylton Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Yea, if you got it to work through ODBC, then there is something wrong w/
    either the OLEDB provider...OR the information you are providing in the connection
    string...or the OLEDB provider isn't installed correctly.

    Have you tried using the mysql oledb provider in VB and just querying data
    into a recordset ?

    I suspect it's just a connection string issue, but not having worked with
    MySQL, I'm not sure what it might be.

    Chris

    "Andy H" <andyhud1@netscape.net> wrote:
    >
    >Following on from this issue, this works:
    >
    >Using myODBC driver DSN create a linked server that references it.
    >Make sure the provider is an OLE DB Provider for ODBC and query in the following
    >way: -
    >
    > select * from openquery(<linked server name>, <pass through query>)
    >
    >Seems as though the OLE DB Provider for MySQL is not entirely compatible
    >with SQL Server 2000
    >
    >"Andy H" <andyhud1@netscape.net> wrote:
    >>
    >>Thanks for your response Chris.
    >>We have tried this using the following:
    >>Provider - "MySqlProv"
    >>DSN - DSN that was set up with MyODBC
    >>
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>Don't have an example, never done this with MySQL before, but if you have
    >>>an OLEDB data provider...have you looked at creating a Linked Server...there
    >>>is info on this in BOL if you need help setting one up.
    >>>
    >>>Chris
    >>>
    >>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>
    >>>>We are attempting to access MySQL data from MS SQL Server 2000
    >>>>Using OLE DB Provider for MySQL under Windows 2000.
    >>>>
    >>>>We are attempting to open the datasource as follows:
    >>>>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>>>
    >>>>
    >>>>i.e. in query analyser
    >>>>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User

    >ID=root').<database>.dbo.<table>
    >>>>
    >>>>We get the response:
    >>>>Server: Msg 7313, Level 16, State 1, Line 1
    >>>>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>>>
    >>>>We have tried to access this table with and without catalog referencing.
    >>>>Does anyone have a simple example that allow us to do this?
    >>>>
    >>>>Any help would be much appreciated.
    >>>>
    >>>

    >>

    >



  6. #6
    Chris Hylton Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    The standard connect string for ADO for MySQL is here...

    http://www.able-consulting.com/ADO_C...oviderForMySQL

    That string should contain everything that a SQL Server Linked Server connection
    requires. Give this a try, pulling the provider out (as it sounds like you
    already have) and use the rest of the string as your connection string and/or
    pull the pieces out to the appropriate Linked Server parameters.

    If you have problems figuring out where to put the various pieces, use the
    sp_addlinkedserver and sp_addlinkedserverlogin (or whatever they are called)
    sometimes they are easier to work with than the poorly documented interface
    inside the SQL Server Linked Server setup screen.

    Chris

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Yea, if you got it to work through ODBC, then there is something wrong w/
    >either the OLEDB provider...OR the information you are providing in the

    connection
    >string...or the OLEDB provider isn't installed correctly.
    >
    >Have you tried using the mysql oledb provider in VB and just querying data
    >into a recordset ?
    >
    >I suspect it's just a connection string issue, but not having worked with
    >MySQL, I'm not sure what it might be.
    >
    >Chris
    >
    >"Andy H" <andyhud1@netscape.net> wrote:
    >>
    >>Following on from this issue, this works:
    >>
    >>Using myODBC driver DSN create a linked server that references it.
    >>Make sure the provider is an OLE DB Provider for ODBC and query in the

    following
    >>way: -
    >>
    >> select * from openquery(<linked server name>, <pass through query>)
    >>
    >>Seems as though the OLE DB Provider for MySQL is not entirely compatible
    >>with SQL Server 2000
    >>
    >>"Andy H" <andyhud1@netscape.net> wrote:
    >>>
    >>>Thanks for your response Chris.
    >>>We have tried this using the following:
    >>>Provider - "MySqlProv"
    >>>DSN - DSN that was set up with MyODBC
    >>>
    >>>
    >>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>
    >>>>Don't have an example, never done this with MySQL before, but if you

    have
    >>>>an OLEDB data provider...have you looked at creating a Linked Server...there
    >>>>is info on this in BOL if you need help setting one up.
    >>>>
    >>>>Chris
    >>>>
    >>>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>>
    >>>>>We are attempting to access MySQL data from MS SQL Server 2000
    >>>>>Using OLE DB Provider for MySQL under Windows 2000.
    >>>>>
    >>>>>We are attempting to open the datasource as follows:
    >>>>>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>>>>
    >>>>>
    >>>>>i.e. in query analyser
    >>>>>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User

    >>ID=root').<database>.dbo.<table>
    >>>>>
    >>>>>We get the response:
    >>>>>Server: Msg 7313, Level 16, State 1, Line 1
    >>>>>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>>>>
    >>>>>We have tried to access this table with and without catalog referencing.
    >>>>>Does anyone have a simple example that allow us to do this?
    >>>>>
    >>>>>Any help would be much appreciated.
    >>>>>
    >>>>
    >>>

    >>

    >



  7. #7
    Andy H Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Chris,

    Looks great, until you attempt to connect to the linked server with a openquery
    or even select the table view via enterprise manager (linked servers)

    SQl Server 2000 Service Pack 2 - OLE DB Provider for MySQL
    (http://www.mysql.com/Downloads/Win32/MyOLEDB3.exe)


    Raises the following error:
    Server: Msg 7302, Level 16, State 1, Line 1
    Could not create an instance of OLE DB provider 'MySqlProv'.

    We have tested this using the Provider string 'MySqlProv' and 'MySqlProv.2.5'

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >The standard connect string for ADO for MySQL is here...
    >
    >http://www.able-consulting.com/ADO_C...oviderForMySQL
    >
    >That string should contain everything that a SQL Server Linked Server connection
    >requires. Give this a try, pulling the provider out (as it sounds like

    you
    >already have) and use the rest of the string as your connection string and/or
    >pull the pieces out to the appropriate Linked Server parameters.
    >
    >If you have problems figuring out where to put the various pieces, use the
    >sp_addlinkedserver and sp_addlinkedserverlogin (or whatever they are called)
    >sometimes they are easier to work with than the poorly documented interface
    >inside the SQL Server Linked Server setup screen.
    >
    >Chris
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>Yea, if you got it to work through ODBC, then there is something wrong

    w/
    >>either the OLEDB provider...OR the information you are providing in the

    >connection
    >>string...or the OLEDB provider isn't installed correctly.
    >>
    >>Have you tried using the mysql oledb provider in VB and just querying data
    >>into a recordset ?
    >>
    >>I suspect it's just a connection string issue, but not having worked with
    >>MySQL, I'm not sure what it might be.
    >>
    >>Chris
    >>
    >>"Andy H" <andyhud1@netscape.net> wrote:
    >>>
    >>>Following on from this issue, this works:
    >>>
    >>>Using myODBC driver DSN create a linked server that references it.
    >>>Make sure the provider is an OLE DB Provider for ODBC and query in the

    >following
    >>>way: -
    >>>
    >>> select * from openquery(<linked server name>, <pass through query>)
    >>>
    >>>Seems as though the OLE DB Provider for MySQL is not entirely compatible
    >>>with SQL Server 2000
    >>>
    >>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>
    >>>>Thanks for your response Chris.
    >>>>We have tried this using the following:
    >>>>Provider - "MySqlProv"
    >>>>DSN - DSN that was set up with MyODBC
    >>>>
    >>>>
    >>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>>
    >>>>>Don't have an example, never done this with MySQL before, but if you

    >have
    >>>>>an OLEDB data provider...have you looked at creating a Linked Server...there
    >>>>>is info on this in BOL if you need help setting one up.
    >>>>>
    >>>>>Chris
    >>>>>
    >>>>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>>>
    >>>>>>We are attempting to access MySQL data from MS SQL Server 2000
    >>>>>>Using OLE DB Provider for MySQL under Windows 2000.
    >>>>>>
    >>>>>>We are attempting to open the datasource as follows:
    >>>>>>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>>>>>
    >>>>>>
    >>>>>>i.e. in query analyser
    >>>>>>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User
    >>>ID=root').<database>.dbo.<table>
    >>>>>>
    >>>>>>We get the response:
    >>>>>>Server: Msg 7313, Level 16, State 1, Line 1
    >>>>>>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>>>>>
    >>>>>>We have tried to access this table with and without catalog referencing.
    >>>>>>Does anyone have a simple example that allow us to do this?
    >>>>>>
    >>>>>>Any help would be much appreciated.
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    Chris Hylton Guest

    Re: Accessing MySQL data from MS SQL Server 2000


    Wish I had an answer. If the same information used in an ADO connection won't
    allow you to setup a linked server, then I agree w/ your earlier comment
    that there may be a problem w/ SQL Server playing nice w/ the OLEDB data
    provider. But that's really all Linked Servers are, OLEDB connections to
    the source database.

    I tried to run a Google search on MySQL and SQL Server Linked Servers...got
    nothing of use...other than the link you provided and that's really just
    MySQL info on the OLEDB provider.

    What seems strange to me is that you can get it to work w/ ODBC but not with
    the pure OLEDB driver direct to the MySQL database.

    It's hard to say...and especially hard to debug since I don't work with MySQL.

    Sorry...
    Chris

    "Andy H" <andyhud1@netscape.net> wrote:
    >
    >Chris,
    >
    >Looks great, until you attempt to connect to the linked server with a openquery
    >or even select the table view via enterprise manager (linked servers)
    >
    >SQl Server 2000 Service Pack 2 - OLE DB Provider for MySQL
    >(http://www.mysql.com/Downloads/Win32/MyOLEDB3.exe)
    >
    >
    >Raises the following error:
    >Server: Msg 7302, Level 16, State 1, Line 1
    >Could not create an instance of OLE DB provider 'MySqlProv'.
    >
    >We have tested this using the Provider string 'MySqlProv' and 'MySqlProv.2.5'
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>The standard connect string for ADO for MySQL is here...
    >>
    >>http://www.able-consulting.com/ADO_C...oviderForMySQL
    >>
    >>That string should contain everything that a SQL Server Linked Server connection
    >>requires. Give this a try, pulling the provider out (as it sounds like

    >you
    >>already have) and use the rest of the string as your connection string

    and/or
    >>pull the pieces out to the appropriate Linked Server parameters.
    >>
    >>If you have problems figuring out where to put the various pieces, use

    the
    >>sp_addlinkedserver and sp_addlinkedserverlogin (or whatever they are called)
    >>sometimes they are easier to work with than the poorly documented interface
    >>inside the SQL Server Linked Server setup screen.
    >>
    >>Chris
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>
    >>>Yea, if you got it to work through ODBC, then there is something wrong

    >w/
    >>>either the OLEDB provider...OR the information you are providing in the

    >>connection
    >>>string...or the OLEDB provider isn't installed correctly.
    >>>
    >>>Have you tried using the mysql oledb provider in VB and just querying

    data
    >>>into a recordset ?
    >>>
    >>>I suspect it's just a connection string issue, but not having worked with
    >>>MySQL, I'm not sure what it might be.
    >>>
    >>>Chris
    >>>
    >>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>
    >>>>Following on from this issue, this works:
    >>>>
    >>>>Using myODBC driver DSN create a linked server that references it.
    >>>>Make sure the provider is an OLE DB Provider for ODBC and query in the

    >>following
    >>>>way: -
    >>>>
    >>>> select * from openquery(<linked server name>, <pass through query>)
    >>>>
    >>>>Seems as though the OLE DB Provider for MySQL is not entirely compatible
    >>>>with SQL Server 2000
    >>>>
    >>>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>>
    >>>>>Thanks for your response Chris.
    >>>>>We have tried this using the following:
    >>>>>Provider - "MySqlProv"
    >>>>>DSN - DSN that was set up with MyODBC
    >>>>>
    >>>>>
    >>>>>"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>>>>>
    >>>>>>Don't have an example, never done this with MySQL before, but if you

    >>have
    >>>>>>an OLEDB data provider...have you looked at creating a Linked Server...there
    >>>>>>is info on this in BOL if you need help setting one up.
    >>>>>>
    >>>>>>Chris
    >>>>>>
    >>>>>>"Andy H" <andyhud1@netscape.net> wrote:
    >>>>>>>
    >>>>>>>We are attempting to access MySQL data from MS SQL Server 2000
    >>>>>>>Using OLE DB Provider for MySQL under Windows 2000.
    >>>>>>>
    >>>>>>>We are attempting to open the datasource as follows:
    >>>>>>>Using the SQL Server OLE provider for ODBC - provider string "MySQLProv"
    >>>>>>>
    >>>>>>>
    >>>>>>>i.e. in query analyser
    >>>>>>>select * from opendatasource('MySQLProv','Data Source=<DSN name>;User
    >>>>ID=root').<database>.dbo.<table>
    >>>>>>>
    >>>>>>>We get the response:
    >>>>>>>Server: Msg 7313, Level 16, State 1, Line 1
    >>>>>>>Invalid schema or catalog specified for provider 'MySQLProv'.
    >>>>>>>
    >>>>>>>We have tried to access this table with and without catalog referencing.
    >>>>>>>Does anyone have a simple example that allow us to do this?
    >>>>>>>
    >>>>>>>Any help would be much appreciated.
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  9. #9
    Join Date
    Oct 2005
    Posts
    1

    How to connect with Linked Server

    I got those kind of problems. My solution was simple ...

    Use Microsoft OLE DB Provider for ODBC Drivers

    Use the Connection String

    DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserver.com;DATABASE=database;USER=user;PASSWORD=password;OPTION=3

    And in Provider Options select:

    Only level zero
    Non-transactional (something)
    Allow InProcess <--- not sure ... but the prev. two yes

    the linked thing will work as

    mylinkedserver...tablename

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center