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.

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.
>

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.
>>
>

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.
>>>
>>
>

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.
>>>>
>>>
>>
>

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.
>>>>>
>>>>
>>>
>>
>

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.
>>>>>>
>>>>>
>>>>
>>>
>>
>

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.
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

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