Create Linked Server from SSMS to MySQL Database

MySQL

 

Sometimes you need to connect to non-SQL Server databases, and that can be difficult to understand the required steps or what to do if you run into error messages. When connecting to a MySQL database, there are several options on how to get the connection to work, but in this article by Leif Neland, we see what worked for him. You can also get some information from Microsoft.

Adjusting the Linked Server Provider is simple, but it comes with a caveat: When adjusting a provider, you are adjusting it for all connections using that provider. I am not aware of any way to change these settings on a per-connection basis.

Provider Properties
Provider Properties

Drill down to Server Object → Linked Servers → Providers, right-click MSDASQL, and select “Properties”.

Set Provider Options
Set Provider Options

The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:

  • Nested queries
  • Level zero only
  • Allow inprocess
  • Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s