toddinho
asked on
Linked Server query
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:
But when I use openquery it does work:
Is there anyway I could make queries directly, using intellisense??
Thanks in advance
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;
But when I use openquery it does work:
SELECT * FROM openquery(hitme,'SELECT * FROM dbhitsystem.tbldatabases')
Is there anyway I could make queries directly, using intellisense??
Thanks in advance
The official fully qualified identifier for any object in MSSQL consists of four parts separated by a dot: Server.Database.Owner.Obje ct
The correct string corresponding to OpenQuery is (probably)
hitme..dbhitsystem.tbldata bases
but, depending on the driver, this might not work.
The correct string corresponding to OpenQuery is (probably)
hitme..dbhitsystem.tbldata
but, depending on the driver, this might not work.
ASKER
I tried to upload the pictures of the errors, but it keeps loading forever... Anyway, neither of the options worked =/
The first one returned:
And the second one:
The first one returned:
Invalid object name 'hitme.dbhitsystem.tbldatabases'.
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.
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:
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. :)
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;
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. :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good point Qlemo. The one time I needed this was for reporting and I had static tables on the linked server.
ASKER
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
you can directly call the table
select * from Linkedserver.database.sche ma.tablena me
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).
select * from Linkedserver.database.sche
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).
SELECT * FROM [hitme].dbhitsystem.tbldat
?