Viewing tables on SQL Server 2016 Developer using R GUI

In this post we will use ODBC Connectivity to connect to SQL Server 2016. We create a new ODBC UserDSN or modify an existing ODBC DSN to connect to the 'pubs' database on SQL Server 2016 (the modification in the link changed the database from 'master' to 'Adventureworks2014').

You need to user the RODBC package to connect to SQL Server if you are using the R from CRAN.

From R GUI (x64bit) click Packages and choose to install packages.

rodbc_00

You may be directed to a list of mirror sites from where you can get the package.

rodbc_00_1
Click "OK" to download the package to your computer.

I had earlier chosen California site and the package has RODBC.

robdc_01

Click on it and click OK.
The package gets downloaded to local file system.

rodbc_02

Load the library for RODBC so that you can use it in the session.

rodbc_03

Connect to the ODBC User DSN "HSQL" you created earlier. Then take a look at all the tables (this is a long list and only few pieces are shown).

rodbc_04

You can also use sqlTables() and sqlFetch() functions with a table name (such as 'employee' in this case) to get relevant items as shown.

rodbc_05

In case you are using a DSN with a username and passwordd, you can connect to it as shown here:
         odbcConnect("dsn", uid="user", pwd="***")

You can close the conneciton with:
         odbcClose(ch)

You will not get a response to the above.

It is quite easy if you have the DSN and the RODBC library.