March 12, 2011 at 8:37 am
Hello,
I have a SQL 2008 install. I have linked to a MySQL server and it seems I can get query results. I have multiple DB's on the MySQL server and the following syntax to switch between databases works so far:
SELECT *
FROM OPENQUERY(MySQLSlave, 'SELECT * FROM DBName.Table_Name')
My question is why can't I get the following syntax to work instead, so I don't have to use Openquery?
SELECT *
FROM [MYSqlSLAVE].[DB_Name].[Table_Name]
March 12, 2011 at 10:35 am
because sqlserver needs a 4-part name to access a linked server.
Your 3-part name is translated to the instances own DB.schema.object
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2011 at 11:58 am
In other words try:
SELECT *
FROM [MYSqlSLAVE]..[DB_Name].[Table_Name]
March 12, 2011 at 9:17 pm
When trying that I get the following error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSqlSLAVE" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSqlSLAVE". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
March 12, 2011 at 9:27 pm
Actually nevermind, I believe I found the issue:
This “four-part name” error is due to a limitation in the MySQL ODBC driver. You cannot switch catalogs/schemas using dotted notation. Instead, you will have to register another DSN and Linked Server for the different catalogs you want to access. Be sure and follow the three-dot notation noted in the example query.
If, however, you want to access other schemas, you can do so utilizing OPENQUERY. This is also a great way to test your connection if you’re receiving problems. The syntax looks like this:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply