SELECT * FROM Linked MySQL server

Question

I have a SQL Server 2012.(120.120.55.15)

Today I linked MySQL server(120.120.55.30) to my SQLServer and gave it a name "MYSQL".

In Object Explorer everything seems fine. I can see MySQL server's database "exampleDataBase" and tables in it.

But when I try to run select query like this:

SELECT * 
FROM   openquery
        (
        MYSQL, 
        '
         SELECT * 
         FROM [exampleDataBase].[msProcMatrix]
        '
        )

I get a mistake:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "MYSQL" reported an error. The provider did not give any information about the error. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQL".

What should be additionally done to use my linked MySQL server?

You have to adjust if SQL Server Management Studio uses 64Bit so for MySQL Connector ODBC it must be 64bit too — AgungCode.Com, Jul 14 '20 at 08:52

score 16 · Accepted Answer · edited Jan 13 '16 at 07:48

16

Found the decision:

SELECT * 
FROM openquery(MYSQL, 'SELECT * FROM exampleDataBase.msProcMatrix')

Without brackets!

Strange for me but works...

edited Jan 13 '16 at 07:48

veljasije

6,262
12
45
77

answered May 22 '13 at 10:42

Almazini

1,735
4
24
46

1

It's the proper way to escape column names in MySQL (NOT MSSQL!) See: http://stackoverflow.com/a/2889884/244067 – Filip Jun 12 '14 at 14:14
1

and the column names are case sensitive I found too! – Matt Kemp Jun 05 '18 at 03:02

score 3 · Answer 2 · edited Oct 17 '13 at 18:50

3

This worked great for me after fighting the same issue on MS SQL Server 2008 64bit using the MY SQL 3.51 64 bit ODBC driver

SELECT *
FROM OPENQUERY
(
   linked_server_name,
   'SELECT * FROM linked_database_name.linked_table_name'
)

edited Oct 17 '13 at 18:50

Jon Lin

139,544
27
213
216

answered Oct 17 '13 at 18:31

user2891719

31
3

score 1 · Answer 3 · edited Jan 13 '16 at 07:47

1

You might need a schema name between the database name and the table name.

SELECT * 
FROM   openquery
        (
        MYSQL, 
        '
         SELECT * 
         FROM [exampleDataBase].**[dbo]**.[msProcMatrix]
        '
        )

edited Jan 13 '16 at 07:47

veljasije

6,262
12
45
77

answered May 22 '13 at 10:12

Marius

23
5

Mariau, find my answer below. – Almazini May 22 '13 at 10:42

score 1 · Answer 4 · edited Jan 28 '14 at 14:58

1

When I working with linked server, I never use Select * From.

Try with Select Column1, Column2, ... ColumnN From.

Always works fine for me.

edited Jan 28 '14 at 14:58

veducm

5,885
2
33
40

answered Jan 28 '14 at 14:41

Roberto Vargas

11
1

score 0 · Answer 5 · answered Feb 03 '16 at 17:40

0

If the default catalog ("exampleDataBase") is configured in ODBC, the following will work as well:

select * from MYSQL...msProcMatrix

answered Feb 03 '16 at 17:40

Vadim Rapp

23
5

score -2 · Answer 6 · answered Mar 14 '18 at 09:50

-2

You Can try this query .

EXEC ( 'SELECT * FROM [exampleDataBase].[msProcMatrix]' ) AT MYSQL

answered Mar 14 '18 at 09:50

Parassharma1990

21
6

score -2 · Answer 7 · edited Mar 14 '18 at 12:18

-2

Please try the statement in the format below .. for me it works very well

SELECT * 
FROM   openquery
        (
        MYSQL, 
        '
         SELECT * 
         FROM [MYSQL]...[exampleDataBase].[msProcMatrix]
        '
        )

Including the extra levels in the name may solve your issue.

edited Mar 14 '18 at 12:18

Turophile

3,267
1
11
21

answered Mar 14 '18 at 10:02

vahid basirat

415
4
7

SELECT * FROM Linked MySQL server

7 Answers7

Linked

Related