MS SQL Server ODBC - MySQL Linked Server

Author

MySQL Linked Server

Asaf

2005-08-28, 8:23 pm

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.

Dennis Black

2005-08-29, 3:23 am

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.

Asaf

2005-08-29, 3:23 am

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

Asaf

2005-08-29, 3:23 am

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

Dennis Black

2005-08-29, 11:23 am

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_N
AME', -- This should be the ODBC System DSN. Do
@provstr='DATABASE=m
ydb;DSN=My
DSN;OPTION=0;PWD=pas
sword;SERVER=localho
st;UID=myuser'

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:[color=darkred
]
> 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

2005-08-29, 8:23 pm

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=m
ySql;DSN=MySqlODBC;O
PTION=0;PWD=;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(MySQLServe
r, '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_N
AME', -- This should be the ODBC System DSN. Do
> @provstr='DATABASE=m
ydb;DSN=My
> DSN;OPTION=0;PWD=pas
sword;SERVER=localho
st;UID=myuser'
>
>
>
> 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:
>

Sponsored Links