Saturday, August 13, 2016

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.


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

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

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


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


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


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).


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


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:

You will not get a response to the above.

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

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...