Solved

Linked Server query

Posted on 2013-05-13
10
280 Views
Last Modified: 2013-05-22
Hello,

I have a database in mySQL and I wanted to replicate it to SQL Server so I can make queries in a better way (i believe sql server is much more user-friendly to make queries). I've been trying to make a linked server to this mysql database and when I got it (at least it appears in my "Linked Servers" list with all the catalogs and tables), I can't run queries directly...

I'm posting a picture of the linked server i built. When I try to query directly into the tables it doesn't work...
 
For example:
SELECT * FROM hitme.dbhitsystem.tbldatabases;

Open in new window


But when I use openquery it does work:
SELECT * FROM openquery(hitme,'SELECT * FROM dbhitsystem.tbldatabases')

Open in new window


Is there anyway I could make queries directly, using intellisense??


Thanks in advance
0
Comment
Question by:toddinho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 10

Expert Comment

by:ienaxxx
ID: 39161782
did u try
SELECT * FROM [hitme].dbhitsystem.tbldatabases;
?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39161960
The official fully qualified identifier for any object in MSSQL consists of four parts separated by a dot:  Server.Database.Owner.Object
The correct string corresponding to OpenQuery is (probably)
   hitme..dbhitsystem.tbldatabases
but, depending on the driver, this might not work.
0
 

Author Comment

by:toddinho
ID: 39162096
I tried to upload the pictures of the errors, but it keeps loading forever... Anyway, neither of the options worked =/

The first one returned:
Invalid object name 'hitme.dbhitsystem.tbldatabases'.

Open in new window


And the second one:
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "hitme". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Open in new window

0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 23

Expert Comment

by:nemws1
ID: 39162118
There are 2 ways of getting what you want:

1) Create a VIEW in your local database for each table in the linked server.

2) In SQL Server you can also create a SYNONYM:

CREATE SYNONYM MyTable
FOR LinkedServer.database.schema.table;

Open in new window


More info:

http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/


In both cases,  Intellisense will work on the new items (VIEW or SYNONYM).  I actually like using the VIEW, since it seems more standard and the next DBA should be able to figure out what I did more easily. :)
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 39162392
But a view does not reflect changes on the target table, while a synonym does :p.

As I said with the fully qualified name, it might not work. If the driver does not allow for it, you can't. You can try if one of the following work:
   hitme.dbhitsystem..tbldatabases
   hitme...tbldatabases
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39162440
Good point Qlemo.  The one time I needed this was for reporting and I had static tables on the linked server.
0
 

Author Comment

by:toddinho
ID: 39162612
Actually, my need is this: I have a whole system that uses a mySQL database... But mysql is very poor (in my opinion) in managing the data... I don't have the time or the headcount to migrate the database itself and make the system store the data in sql server directly...

I was thinking about linked server because i thought it would be just like linked tables in access, if the source is modified, the table is modified in "real time"... But is it true?

Is the linked server the best way to do this? My idea is this (yes, i know this is ugly and all): the system keeps using the mysql database and when I need to create statistics, analize data, etc, I could do it from SQL Server, which would be a true representation of the actual database... You could wonder why not do all this in mysql itself. The main reasons I can think of (i'm no expert in databases) are: Intellisense, performance and ability to use SQL Dependency and triggers...

PS: There are actually 3 databases, with total of about 98 tables... So creating manually the views for each one would be somewhat difficult =p

I hope it's a little more clear my problem..
0
 
LVL 23

Accepted Solution

by:
nemws1 earned 300 total points
ID: 39162653
You wan to use a SYNONYM like I suggested above.  As to your points:

1) Intellisense - yes, this should work.  (I hate it, personally, and always install SQL Complete, which is free and much better, IMO.)

2) Performance - will go down.  All your data is on one server and you want to do analysis on another server.  Better to do your analysis on the same server as your data, unless this server is *really* slow and your other server is *really* fast.

3) SQL Dependency / triggers - No.  Do not even attempt this.  Both of these will need to be done on the MySQL side.  MSSQL will not know when changes occur on remote systems nor should it be responsible for data integrity *elsewhere*.  You definitely want to do this on the MySQL side.

Finally, whether you want to go with views or synonyms (again, the latter makes more sense) you should always think to yourself "dynamic SQL" when you get in a situation like this.

On the MySQL side:

USE information_schema;
SELECT CONCAT('CREATE SYNONYM [', table_name, '] FOR mysql_link_name.mysql_database_name..[', table_name, '];')
FROM tables
WHERE table_schema = 'mysql_database_name'
;

Open in new window


Then run the output in MSSQL.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39163036
DON'T! All data remains on the MySQL side. Even worse, MSSQL will not be able to use any optimization (indexes, statistics, views, ...) for that data. Performance will cease to exist.

When you use "linked tables" in Access, you substituting a simple SQL engine by a much more advanced SQL engine, and so there is a gain. Using MSSQL to access MySQL will fail horribly because MySQL is less advanced, and the drivers are just not suited to be effective in that environment.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39171075
you can directly call the table


select * from Linkedserver.database.schema.tablename

no need to create synonyms.

If you wish to hide the linked server detail then you can you that or for short the name(avoid writing long text every time).
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question