Here's my situation. I have two databases on SQL Server and one MySQL
(see details below). I've used myODBC to create a System DNS to the
mySQL database on the Windows server. I've created a link using OLEDB
for ODBC to create a linked server to MySQL. The link allows me to
view a list of all the tables but now how do I view the data in a
particular table?
What is the syntax to query a linked server? I've tried using the four
part name in a query (select * from linkname...tablename) and received
the following error,
Server: Msg 7356, Level 16, State 1, Line 3
OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
Any help would be greatly appreciated, thanks!
Server 1
--------
OS: Windows 2000 Server.
DB: SQL Server 2000.
Server 2
--------
OS: OpenBSD v2.9 i386.
DB: MySQL Database v8.19 Distrib 3.23.37.
Peter J. Boettcher posted this at 23:35—23rd September 2002.
They have: 812 posts
Joined: Feb 2000
I have never linked to a mySQL db but I have had problems in the past linking to an Access db that wasn't on the same machine or mapped.
Have you linked the databases inside SQL Server (sp_addlinkedserver) ?
PJ | Are we there yet?
pjboettcher.com
The Warden posted this at 14:41—24th September 2002.
He has: 9 posts
Joined: Sep 2002
I have the link created successfully but can only see a list of tables but not the content of a table. I created the link using the GUI (Enterprise Manager). I would assume it uses the sp_addlinkedserver. Like I stated above in the previous message, I'm trying to figure out how to query the linked server using the four part name (ex. servername...tablename). So far I've be unsuccessful. I did recently get openquery() to work but I would prefer to use the four part name. Using openquery() in my situation would create a lot of overhead considering in most cases that I woul have 1 query with 2 embedded queries (3 levels). This method doesn't make sense to me and makes more sense to go direct.
Any ideas?
Peter J. Boettcher posted this at 13:14—25th September 2002.
They have: 812 posts
Joined: Feb 2000
Not really. Have you used a linked mySQL database successfully before? Maybe there is a bug in the ODBC driver.
openquery() sounds like it would add a lot of extra overhead but I think in some cases it is actually faster (because of the way SQL Server interprets and returns the data) than four-part naming.
PJ | Are we there yet?
pjboettcher.com