How Do You Query a Linked Database??
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
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.