December 21, 2005 at 10:17 am
I've been trying to setup a linked server in SQL Server 2000 to a different server with MySQL. I've got the MySQL ODBC driver installed on the SQL Server machine.
Has anyone here been able to get this working? If so, I'd like some specifics on the settings you're using.
Thanks!
December 21, 2005 at 11:02 am
Yes, I've gotten it working. Can you describe your setup and the error message you've gotten? I'm using a standard System DSN and connecting to it using the OLEDB Provider for ODBC data sources.
K. Brian Kelley
@kbriankelley
December 21, 2005 at 12:14 pm
The SQL Server machine is Windows Server 2003 with this version of SQL Server:
1)
Microsoft SQL Server 2000 - 8.00.878 (Intel X86) Nov 11 2003 13:37:42
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
The MySQL machine is Windows Server 2003 with latest version of MySQL and ODBC Driver (3.5.1)
There's another SQL Server machine with this version on it:
2)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0
(Build 2195: Service Pack 4)
I'm not sure how to configure the linked server settings. I've tried a number of things.
When I try the setup below, the server shows as linked, but when I try to expand the tables, I
get an hourglass and it just hangs.
On the #2 SQL Server, it doesn't hang, but I get this error when selecting from a table:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The query: SELECT * FROM OPENQUERY(servername,'SELECT * FROM bugs')
The setup (I've not included the actual usernames/passwords, etc.:
EXEC sp_addlinkedserver @server ='linkedservername' ,
@srvproduct = 'MySQL' ,
@provider = 'MSDASQL',
@datasrc = null ,
@location ='myservername' ,
--@provstr = 'Driver={MySQL ODBC 3.51 Driver};Database=bugs;Server=servername;UID=username;PWD=thisUserPassword;' ,
--@provstr = 'Provider=MySQLProv;Data Source=bugs;User Id=username;Password=thisUserPassword',
@provstr = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=servername;DATABASE=bugs;USER=username;PASSWORD=thisUserPassword;OPTION=3',
@catalog = null
EXEC sp_addlinkedsrvlogin
@rmtsrvname ='linkedservername',
@useself ='false',
@locallogin =NULL,
@rmtuser = 'username',
@rmtpassword = 'thisUserPassword'
December 21, 2005 at 7:49 pm
Is MyODBC installed on the same systems as the SQL Server or just on the machine with MySQL?
K. Brian Kelley
@kbriankelley
December 22, 2005 at 8:51 am
It's on both.
Further developments: mostly what's happening now is that Enterprise Manager hangs (wait cursor) when I try to look at a table.. as does Query Analyzer when I query using OPENQUERY.
I did get data back from QA at one time yesterday, but now it'll just run forever with no result.
Really, what I'm looking for is how should my params be set for the linked server? I've tried many different combinations. At one point, I may have had it right, but still have never gotten EM to see the tables.
December 22, 2005 at 10:17 am
If you're getting results back from QA, albeit intermittently, it sounds like you've got the right setup. Out of curiosity, when you try to use the MySQL command line client, do you get the same sort of result? If so, it might be that your MySQL installation is hammered.
K. Brian Kelley
@kbriankelley
December 22, 2005 at 10:24 am
Haven't tried the MySQL command line client..
We're trying out Bugzilla as an issue-tracking system.. this is why we're using MySQL - it's required for Bugzilla.
Bugzilla (web application using Perl and MySQL) is working fine, no problems with data.
I've also got MS Access linked to MySQL and this works very well.
Just can't get it working with SQL Server. I can't get any queries to finish running today. We don't have a lot of data, maybe 20 rows max for a given table.
Can you tell me how your params are set (without giving away passwords, etc) so that I might try it?
Thanks.
December 22, 2005 at 11:29 am
Basically the same as you:
EXEC sp_addlinkedserver @server ='LocalMySQL' ,
@srvproduct = 'MySQL' ,
@provider = 'MSDASQL',
@datasrc = null ,
@location ='localhost' ,
@provstr = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=world;USER=hidden;PASSWORD=hidden;OPTION=3',
@catalog = null
GO
My MySQL install is local to the SQL Server, but that doesn't explain the issue you're having. Now I am having to do SELECT * FROM OPENQUERY(LocalMySQL, <my query> ) because otherwise I get the schema error...
K. Brian Kelley
@kbriankelley
December 22, 2005 at 12:38 pm
Well, I'm stumped.
Brian, thanks for your input.
December 22, 2005 at 12:43 pm
What do your queries look like? Also, if you have MyODBC installed on your workstation, do you have the same issue, say connecting from SQL Server (if installed) or MS Access?
K. Brian Kelley
@kbriankelley
December 22, 2005 at 1:22 pm
Hi,
I know someone who works with MySQL client towards SQL Server database, but he will be available after the Holidays. You may send me a message on /after Jan 3 if you still have this issue.
Regards,Yelena Varsha
December 22, 2005 at 2:55 pm
Here's an example query that I've tried:
SELECT * FROM OPENQUERY(Bugzilla,'SELECT * FROM bugs')
or
SELECT * FROM OPENQUERY(Bugzilla,'SELECT * FROM profiles')
Bugzilla is the name I assigned to the linked server, bugs and profiles are tables.
I don't have an instance of SQL Server on my workstation, just client tools. I'm working with an MS Access application that has tables linked to SQL Server and tables linked to MySQL. It works, no problem.
Even if I can get the linked server (to MySQL) working, I doubt I can do was I was hoping: create a view to the linked server that's accessible from (or published to) another application.
What I was originally trying to do was to avoid having everyone that uses the Access application install the MyODBC 3.5.1 driver on his/her workstation. So I wanted to see if I could just use SQL Server as a go-between. I'm using SQL Server for several other tables anyway, so it would be convenient.
December 22, 2005 at 3:12 pm
What happens if you create a system DSN on the SQL Server pointing to the MySQL database and use it instead of the full-blown connection string? Any difference?
K. Brian Kelley
@kbriankelley
December 22, 2005 at 4:13 pm
Same behavior. I also get the same behavior if I put in completely bogus info (server that doesn't exist, etc.)
What's happening is Enterprise Mangler and Query Analyzationer are still hanging. Click on the Tables under the new linked server in EM and hourglass.. run any query (using openquery) and no result ever comes back.
December 22, 2005 at 7:50 pm
Out of curiosity, what happens if you do a linked server connection to another SQL Server?
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply