SQL Server has interesting feature calles Linked Servers. It’s about linking other databases to SQL Server and using their data like it’s local. There are many powerful open-source systems written on PHP and they are mostly using MySQL as database. This blog post shows how to link MySQL database to SQL Server and how to use linked server in SQL queries.

1. Mac Brew Install Mysql Server
2. Brew Mysql Server Command
3. Brew Mysql Server
4. Brew Mysql Server Commands
5. Brew Install Mysql Server
6. Brew Mysql Server Has Gone Away
7. Brew Mysql Server Tutorial

Sometimes I have this issue where I lose permission to my mysql server and get this error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) I tried killing the mysql processes and restarting the server but i keep getting denied. I also tried going into my.cnf and adding. Skip-grant-tables but it did not do. The MySQL-devel packages are found in the download - community server area. The MySQL development library package names start with MySQL-devel and vary based MySQL version and linux platform (e.g. MySQL-devel-5.5.24-1.linux2.6.x8664.rpm.) Note that you do not need to install mysql server.

What is linked server?

Linked server in MSSQL is some other database server connected to given one, making it possible to query and manipulate data in other databases. By example, we can link some MySQL database to MSSQL and use it almost like any other database on MSSQL. Osx node.

Image is taken from MSSQL 2019 documentation page
Linked Servers (Database Engine)

Although communication with linked servers goes through OLE DB providers, there is also OLE DB provider for ODBC and we can use it if our external database doesn’t have OLE DB provider.

NB! Linked server is available for whole SQL Server instance. It means that all SQL Server databases can use linked server to retrieve data.

Linking MySQL to SQL Server

Adding linked server and configuring connection setting is not always easy and straightforward.

To get MySQL linked to SQL Server I needed to create ODBC DSN for MySQL (I named it as MySQLCrm). Before going to next steps, make sure that ODBC data source works.

Mac Brew Install Mysql Server

Follow these steps to link MySQL to SQL Server:

1. Run SQL Server Management Studio (SSMS)
2. Connect to your server
3. Expand Server Objects node from tree at left
4. Right-click on Linked Servers
5. Select New Linked Server…

You should see the following dialog (or bit different but the idea remains the same).

NB! Pay extra attention to what you insert to this dialog. With this set of data I made link work. I tried different values and if something is one millimeter wrong then connection fails. It’s damn sensitive dialog.

Connection string to MySQL database should be like shown here:

Also pay attention to OPTION=3 – without this I got only errors back when connecting to linked server.

Try to save by clicking OK and see if you can browse to linked server. If you get errors then right-click on server and select properties. Keeping the dialog open, move to Server Options page. Set RPC and RPC Out settings to True.

I’m still not very sure what are these options doing but some of those who had issues with link to MySQL made it work after setting RPC-s to true.

To make querying actually work, we need one more little change that affects whole OLE DB provider and therefore all connections using it. Open Providers node under Linked Servers, right-click on MSDASQL (this is OLE DB provider for ODBC data sources) and select properties.

Check the box before Level Zero only and click OK to save changes.

Querying data from linked server

Brew Mysql Server Command

Querying linked databases is actually easy. Here’s the customers table from crmlinked database in MySQL. This database is linked to my SQL Server.

Syntax for querying linked server is a little bit different from what we usually write on SQL Server. We need to use four-part names: server.database.schema.table. As there’s no schemas on MySQL and connection string specifies database name, then we can leave these out like shown here.

Running this query from SSMS gives the following output. It’s the same data that is in MySQL customers table.

Of course, we can also write more complex queries. Everything that ODBC can handle is okay.

Mixing data from local and linked server

Tables from linked server are not totally isolated from local database tables and views. We can also mix data from local and linked server.

To demonstrate mixed query over local and linked tables let’s write simple query to get all customers from local table and their credit ratings from linked table.

Running this query gives us the following output.

How to zip in terminal mac. As Mark is not present in MySQL database (suppose he is new customer in e-shop and sales department doesn’t have him yet in their CRM system) then he doesn’t have credit rating available. Credit ratings for John and Mary are coming from MySQL in current case.

Using OPENQUERY() to execute query in linked server

The examples above make all data processing on SQL Server. It can be very unoptimal if there’s a lot of data in linked server’s tables. We may want – or usually want – to process some data in linked server before SQL Server starts local processing. For this we have OPENQUERY().

Here’s the example of using OPENQUERY() function in mixed query. We have to specify linked server name and SQL query to run in linked server when calling OPENQUERY(). The query in red is executed in MySQL server and results are read to SQL Server for further processing.

OPENQUERY() is great way to optimize and speed up mixed queries by running more complex queries over linked server data in linked server.

Wrapping up

Linked servers is powerful feature of SQL Server making it easy for us to use data from external servers. There are two ways to write queries using data from linked servers – direct queries that do all processing on SQL Server and OPENQUERY() that lets us do some processing in remote server. Linked server is integration and therefore using it needs extra care. Planning and performance measuring are must-be activities when planning to use linked server.

For Unix and MacOS, ODBC drivers should be compiled againstunixODBC. Drivers compiled against iODBCmay also work, but are not fully supported.

After installation of the driver manager and driver, you will have toregister the driver in a odbcinst.ini file for it to appear in odbc::odbcListDrivers().

Microsoft Windows

Database Drivers

Windows is bundled with ODBC libraries; however, drivers for each databaseneed to be installed separately. Windows ODBC drivers typically include aninstaller that must be run to install the drivers in the properlocations.

Administration

The ODBC Data Source Administratorapplication is used to manage ODBC data sources on Windows.

Apple MacOS

Installation

1. Install homebrew to install database drivers easily on MacOS

2. Install UnixODBC, which is required for all databases

3. Install common DB drivers (optional)

Setting up database connections

Brew Mysql Server

See the section with the same name in the Linux section.

Linux Debian / Ubuntu

Installation

The apt-get command can be used to install databasedrivers easily on Linux distributions that support it, such as Debian and Ubuntu.

1. Install UnixODBC, which is required for all databases

2. Install common DB drivers (optional)

Setting up database connections

On MacOS and Linux, there are two separate text files that need to be edited.UnixODBC includes a command-line executable called odbcinst, which can be used toquery and modify the DSN files. However, these are plain text files youcan also edit by hand if desired.

There are two different files used to set up the DSN information:

Brew Mysql Server Commands

• odbcinst.ini defines driver options

• odbc.ini defines connection options

odbcinst.ini

This file contains the driver information, particularly the name of the driver library.Multiple drivers can be specified in the same file.

odbc.ini

This file contains the connection information, particularly the username, password, databaseand host information. The Driver line corresponds to the driver defined inodbcinst.ini.

See also: unixODBC without the GUI for more information and examples.

Location

Brew Install Mysql Server

The DSN configuration files can be defined globally for all users of thesystem, often at/etc/odbc.ini or /opt/local/etc/odbc.ini. The file location depends onwhat option was used when compiling unixODBC; odbcinst -j can be used to findthe exact location. Alternatively, the ODBCSYSINI environment variable can beused to specify the location of the configuration files. Ex. ODBCSYSINI=~/ODBC

A local DSN file can also be used with the files ~/.odbc.ini and ~/.odbcinst.ini.

Connecting to a Database in R

Databases can be connected by specifying a connection string directly, or withDSN configuration files.

Connection Strings

Pass the connection parameters as arguments to the dbConnect() function.

For database-specific settings, go to the Databases section in the menu and look for the page that matches the desired database type.

Brew Mysql Server Has Gone Away

DSN Configuration files

Brew Mysql Server Tutorial

ODBC configuration files are another option to specify connection parameters; theyallow you to use a Data Source Name (DSN) to make it easier to connect to a database.

For more information about how DSN can be used, check out the following articles in this site: