MySQL Linked Server

I am trying to set up MySQL as linked server for SQL Server 2000.
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.
Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up MySQL as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.
|||Thanks for your reply Dennis but my problem is still the same. Being novice I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:

> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Here is an example where I link a MySQL database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>
|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;OPTION=0;PW D=;SERVER=localhost;UID=root'
There is only one more issue; I could query mySQL by using e.g user Table
from mySQL db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:

> Here is an example where I link a MySQL database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>