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.
Drill down to Server Object → Linked Servers → Providers, right-click MSDASQL, and select “Properties”.
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”.