Marcus Aurelius
asked on
Need to access data on LINUX MySQL using a different SQL Server 2005 Machine...?
Experts:
I have a SERVER that has Ubuntu Linux OS running and MySQL.
I need to ACCESS the Data in MySQL using another SERVER that runs Windows Server 2003 and has SQL Server 2005.
I think I can use the LINKED Server process in SQL Server, but need some direction on HOW to get this done.
Your help is greatly appreciated...
THanks
M
I have a SERVER that has Ubuntu Linux OS running and MySQL.
I need to ACCESS the Data in MySQL using another SERVER that runs Windows Server 2003 and has SQL Server 2005.
I think I can use the LINKED Server process in SQL Server, but need some direction on HOW to get this done.
Your help is greatly appreciated...
THanks
M
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Issue...SQL Server Can't find the Stored Proc:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.sp_MSset_oledb _prop'.
Thanks
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.sp_MSset_oledb
Thanks
ASKER
Ok I think I know what the issue is....I was setting this up using a SQL 2000 connection and DB.
Can I do this using SQL 2000 OR...will I need to switch to a SQL Server 2005 database..?
Can I do this using SQL 2000 OR...will I need to switch to a SQL Server 2005 database..?
ASKER
More info...I'm using SQL Server 2005 Management Studio to ACCESS a SQL 2000 DB.
I do have SQL 2005 DB's but on another Server BOX. If I need to go over to that box..I will..but just wanted to doubecheck to see IF this LINKED Server can be setup using SQL 2000 or not...?
Thanks
M
I do have SQL 2005 DB's but on another Server BOX. If I need to go over to that box..I will..but just wanted to doubecheck to see IF this LINKED Server can be setup using SQL 2000 or not...?
Thanks
M
Ah. That component's built for 2005, using Visual Studio 2005... I'm not exactly sure about the backwards compatibility with 2000 as I don't have it at my disposal to test, but I'd imagine 2000 doesn't have all the ADO.net components needed.
In that case, you'd probably have to use the SQL Server OLE Provider for ODBC. You can always download the MySQL Connector/ODBC for Windows (http://dev.mysql.com/downloads/connector/odbc/5.1.html) and set up a System DSN to the MySQL server on the SQL Server box. Then, you can access MySQL's data over ODBC. It's a bit more inefficient than the OLE DB provider, but it is at least an alternative.
To take advantage of the full OLE provider and setup as a Linked Server, you'll need to go with 2005. 2000 seems ODBC-only.
In that case, you'd probably have to use the SQL Server OLE Provider for ODBC. You can always download the MySQL Connector/ODBC for Windows (http://dev.mysql.com/downloads/connector/odbc/5.1.html) and set up a System DSN to the MySQL server on the SQL Server box. Then, you can access MySQL's data over ODBC. It's a bit more inefficient than the OLE DB provider, but it is at least an alternative.
To take advantage of the full OLE provider and setup as a Linked Server, you'll need to go with 2005. 2000 seems ODBC-only.
ASKER
I think I have it setup now......so next question is....how do I query the MySQL DB...from SQL 2005...???
ASKER
whats the basic syntax?
Can I SEE the entire list of DB Tables from my SQL 2005..or will I have to just KNOW what tables are there...?
Can I SEE the entire list of DB Tables from my SQL 2005..or will I have to just KNOW what tables are there...?
ASKER
This Query:
Select * from openquery(MYREMOTESMYSQLSE RVER,'sele ct* from dm.dbo.client')
Give me this error:
Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "OleMySql.MySqlSource" for linked server "MYREMOTESMYSQLSERVER".
Thanks
M
Select * from openquery(MYREMOTESMYSQLSE
Give me this error:
Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "OleMySql.MySqlSource" for linked server "MYREMOTESMYSQLSERVER".
Thanks
M
ASKER
Also, I notice that when I was setting up the LINKED SERVER, after I chose the "MySQL Provider",...the PROVIDER STRING, LOCATION, and CATALOG fields are all DIMMED and I cannot enter anything there.
Not sure why they get dimmed when I chose the MY SQL PROVIDER....?
By the way, this is a 64byte Server....if that matters at all.....???
Not sure why they get dimmed when I chose the MY SQL PROVIDER....?
By the way, this is a 64byte Server....if that matters at all.....???
ASKER
I'm still working through this,...but your info was most helpful...
ASKER
THANKS