Access mysql through linked server error

Question

Access mysql through linked server error

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
March 26, 2010 at 10:23 am

#217078

I have established a linked server to MYSQL to SQL Server Database
When I run this query I get error :
select * from mysql.brightlink.src_customers
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
however this works fine..
select * from openquery(MySql, 'select * from brightlink.src_customers')
I want to avoid using openquery as this is slow in production
please advise about four part naming convention from sql server to mysql

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

WangcChiKaBastar (3/26/2010)
I have established a linked server to MYSQL to SQL Server Database
When I run this query I get error :
select * from mysql.brightlink.src_customers
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "mysql". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
however this works fine..
select * from openquery(MySql, 'select * from brightlink.src_customers')
I want to avoid using openquery as this is slow in production
please advise about four part naming convention from sql server to mysql

select * from <yourlinkedservername>...<thetablename>

The ODBC driver should be convered to the database (catalog) your are accessing.

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

ok I ran this as per your advise..

select * from MySQL...src_customers

and got this error ...

OLE DB provider "MSDASQL" for linked server "MySQL" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "MySQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.77]No database selected".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "MySQL". The provider supports the interface, but returns a failure code when it is used.

please advise if its possible to run create table and insert into statements from SQL Server to the linked MYSql server database ???

its urgent..thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I just checked our System DSN to the server running MySQL and it specifies the database to be accessed. How do you have your ODBC connection configured?

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 4

I have odbc connector installed (.net msi executable from web) and then created a dsn to mysql..

everything else works fine

my task is to fire sql code from sql server on to mysql database

logic will be

check if the table exists

if no then create the table

and then insert into the table select from sqlserver.dbo.table

I am looking into SSIS now with Script task...but doesnt look like its going to be starighforward plz advise

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is a screen print of the ODBC config for our MySQL system. Is yours configured similarly?

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 6

absolutely similar...

can I run something like select * into mysql...test1 from sqlserver.dbo.test

Paul White SSC Guru Points: 150467 More actions · Answer 7

There is a complete step-by-step guide here:

http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/

Be sure to follow the steps exactly, especially the part concerning the MSDASQL provider properties 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi