How to Create Linked Server for a MySQL database using SQL Server Management Studio

Question

How to Create Linked Server for a MySQL database using SQL Server Management Studio

Viewing 15 posts - 31 through 45 (of 128 total)

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

Travis Peters SSC Enthusiast Points: 148 More actions · Answer 1

Tim Cooke (3/25/2008)
Hi
This info for linking MySQL Server is realy helpful and I almost have it working. Unfortunately I seem to be stuck at the last hurdle.
I have a working connection to the MySQL server and can retrieve and display the Schema, however, I get an error if I try to perform a simple SELECT on any of the tables I get an error.
If I use the "Sctript table as SELECT to..." from enterprise manager I get:
-- [MYSQL_MAGSUBS].[gdspub]..[econference] contains no columns that can be selected or the current user does not have permissions on that object.
If I try to run a SELECT directly I get:
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "MYSQL_MAGSUBS" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 3
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSQL_MAGSUBS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

I'm having the same trouble. Any ideas?

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

We have a linked server setup to access a MySQL database. The view that queries the MySQL database accesses the data with the following format: FROM MySQLServer...MySQLTable.

Hope this helps. (The names have been changed to protect the guilty).

😎

Travis Peters SSC Enthusiast Points: 148 More actions · Answer 3

To elaborate on Lynn's post:

I was able to get it working when I setup the ODBC DSN to connect to a default database, then use

SELECT * FROM PJSR...tblafe

The problem occurs when attempts are made to specify a database different from the default (even if a default isn't set).

Tim Cooke SSC Veteran Points: 273 More actions · Answer 4

Thanks All

This has been excercising the old grey cells for some time

Problem solved 🙂

Coop-728726 Valued Member Points: 65 More actions · Answer 5

This topic saved me big time. Thanks to everyone who took the time to participate!!

KP-29583 SSC-Addicted Points: 436 More actions · Answer 6

Hi everybody,

FYI, Microsoft has released a 64-bit OLEDB Provider for ODBC recently. The URL for downloading the driver is:

http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en#Instructions

I have installed the driver on 64-bit Windows 2003 and able to set up a linked server from 64-bit SQL server 2005 to MySQL without any problem.

taddbartley Valued Member Points: 57 More actions · Answer 7

Your instructions saved my butt. Thanks a lot.

Although your instructions are outdated now because there is a new version of the MySQL driver, and so I had to guess at some of the flag options.

Still, it seems to work. Thanks.

bigcraiginjax Old Hand Points: 353 More actions · Answer 8

Hello, could you please document your steps to create a linked server in 64 bit? I haven't had any luck.

My Steps

1. Download and install mysql-connector-odbc-5.1.4-win32.msi on my Windows 2003 x64 machine.

2. Went to Start/Run, pasted in C:\WINDOWS\SysWOW64\odbcad32.exe to bring up the ODBC administrator.

3. added system dsn, tested connection works just fine.

4. installed WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe to get MSDASQL provider for SSMS.

. Open SSMS, try to add linked server, provide DSN and login. When I click OK, I get:

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL".

OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

It seems like the Provider is not looking for the DSNs set up in the 32 bit administrator, or did I just miss something? Thanks!

bigcraiginjax

bigcraiginjax Old Hand Points: 353 More actions · Answer 9

Update--I found the correct driver and installed mysql-connector-odbc-5.1.4-winx64.msi

and created the ODBC connection in the 64 bit ODBC administrator--test connection worked fine. When I went back to SQL to try again, I still got the same error. Do I need a reboot or restart of the SQL Server box?

bigcraiginjax

khurram.saleem Old Hand Points: 328 More actions · Answer 10

Hi,

I have managed to successfully make a connection with a mySQL database server.

However, when attempting to expand the Catalogs, It just seems to hang or indefintely attempts to expand.

Catalogs (expanding...)

I have waited 15 mins so far and nothing has happened.

Any advice would be appreciated.

thanks.

khurram.saleem Old Hand Points: 328 More actions · Answer 11

Aaah, just got a message.....

"Failed to retrieve data for this request. (Microsoft.SQlServer.SmoEnum).

Timeout expired.

Actually, all queries are timing out.

🙁

KP-29583 SSC-Addicted Points: 436 More actions · Answer 12

I remember rebooting the box after I installed the Microsoft 64-bit ODBC driver. So, just try that, it might help.

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 13

I can second that.

I definitely had to restart the SQL Server service after installing the driver.

-Simon

khurram.saleem Old Hand Points: 328 More actions · Answer 14

THanks for the advise. I restarted the machine and still no joy.

When I run the command "EXEC sp_tables_ex is2" I get the following error:

OLE DB provider "SQL Server" for linked server "(null)" returned message "Unspecified error".

OLE DB provider "SQL Server" for linked server "(null)" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.24-rc-community-log]Table 'video.TABLES' doesn't exist".

Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.

When i use query "SELECT * FROM IS2...Campaigns" I get a "Catastrophic failure" - rather dramatic error!

Not sure what to try next 🙁

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 15

Hi All:

I'm working with a MySQL database that serves a website, and need to periodically transfer data from our local SQL Server 2005 server to the MySQL server.

I've setup the MySQL server as a linked server using SMSS, using the directions provided here, and things are working great. (Thanks again to everyone for the contributions.)

What I'm concerned about now is performance.

When I run INSERT statements that insert rows from the MsSQL Server (SQL Server 2005) server to the MySQL server, the performance is terrible.

For example, on average it takes almost 8 minutes to insert about 5,000 rows into an empty table in MySQL with no indexes.

I've tried using the following two methods, with identical results:

INSERT MySQLServer...MySQLTable (Columns, ...)

SELECT (Columns, ...)

FROM MsSQLServer

and

INSERT OPENQUERY (MysQLServer, 'SELECT [Columns,...] FROM MySQLSchema.MySQLTable'

SELECT (Columns,...)

FROM MsSQLServer

Can anyone offer any thoughts or insights?

Any input would be greatly appreciated.

-Simon