<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/accessing-mssql-server-with-r-rsqlserver-with-dplyr/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.haunschmid.name/accessing-mssql-server-r-rsqlserver-dplyr/"> r-bloggers – verenahaunschmid</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Recently I have been starting to use <a target="_blank" rel="nofollow" href="https://github.com/hadley/dplyr">dplyr</a> for handling my data in R. It makes everything a lot smoother! My previous workflow – running an SQL query, storing the results as CSV, loading it in RStudio – is now history. With dplyr you can directly query data from many different databases in a very convenient way. Unfortunately Microsoft SQL Server is not directly supported but by using the package RSQLServer it can be done like with any other database. In this blog post I’ll explain how I installed everything on my Windows 7 machine to access MSSQL Server with R, since it was not as straight forward as one might think.</p>
<h1>All you need … theoretically</h1>
<p>The package RSQLServer is not available on CRAN anymore but it can be installed from the github repo <a target="_blank" rel="nofollow" href="https://github.com/imanuelcostigan/RSQLServer">imanuelcostigan/RSQLServer</a>.</p>
<p><code><br>
# install.packages('devtools')<br>
devtools::install_github('imanuelcostigan/RSQLServer')<br>
</code></p>
<p>If this works you’re lucky and already have all the necessary things installed. If not, follow the steps below <img alt="🙂" class="wp-smiley" src="https://i0.wp.com/s.w.org/images/core/emoji/2.2.1/72x72/1f642.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h1>Errors I encountered</h1>
<p>On my path from finding out about RSQLServer to actually using it I had to fix a few things. Searching on the internet and finding most of the answers on stackoverflow I was able to piece together everything I needed. To save everyone else who tries this some time I collected everything I found out.</p>
<h2>Fixing the installation</h2>
<p>The first error occured when I tried to install the package as shown above.</p>
<p>
Downloading GitHub repo imanuelcostigan/RSQLServer@master<br>
from URL https://api.github.com/repos/imanuelcostigan/RSQLServer/zipball/master<br>
Installing RSQLServer<br>
Downloading GitHub repo hadley/dplyr@63d4a9f5<br>
from URL https://api.github.com/repos/hadley/dplyr/zipball/63d4a9f5<br>
Error: running command ‘”C:/PROGRA~1/R/R-33~1.2/bin/x64/R” –no-site-file –no-environ –no-save –no-restore –quiet CMD config CC’ had status 1
</p>
<p>To resolve this:</p>
<ol>
<li>Find out which R version you have, if you don’t know, type <code>R.version</code> inside an R session</li>
<li>Download and install the correct version of <a target="_blank" rel="nofollow" href="https://cran.r-project.org/bin/windows/Rtools/">Rtools</a>
<ul>
<li>Although I had R version 3.3 and downloaded the correct R version for it, it tried to install it in a directory called “34”. To make sure it will later find it, I changed this to “33” to match my R version. I am not sure whether it made a difference.</li>
<li>Make sure to check the option where it asks if you want to add it to the path. This makes a difference.</li>
</ul>
</li>
<li>Make sure you have the correct Java version and/or architecture installed. I didn’t have Java installed for x86 which also caused the installation to fail.
<li>Restart your computer or at least log out and in again.</li>
</ol>
<p>After that I was able to install the package using the following command.</p>
<p><code><br>
devtools::install_github('imanuelcostigan/RSQLServer')<br>
</code></p>
<h2>Getting integrated (NTLM) authentication to work</h2>
<p>Download <a target="_blank" rel="nofollow" href="https://sourceforge.net/projects/jtds/files/jtds/">jtds-drivers</a> and follow <a target="_blank" rel="nofollow" href="http://stackoverflow.com/a/16429490/1117932">these instructions</a>.</p>
<h1>Finally accessing MSSQL Server with R</h1>
<p>I was working with two different databases. One was remote and with that it worked immediately. The other one was a local installation on my computer.</p>
<p>For the local one I got an error message. <strong>Note: I don’t think it had anything to do with the databases being remote/local, just with the settings I used for installing my database!</strong></p>
<p>
Error in rJava::.jcall(driver@jdrv, “Ljava/sql/Connection;”, “connect”, :<br>
java.sql.SQLException: Network error IOException: Connection refused: connect
</p>
<p>To resolve this, you need to open the SQL Server configuration manager. I have the SQL Server configuration manager and the SQL Server configuration manager (2016) installed. For some settings it doesn’t matter which one you use but for this one I had to use the older one otherwise my database (SQL Server 2012) wouldn’t show up. </p>
<p>Make sure to enable TCP/IP. Restart you SQL Server instance. Now it should be working!</p>
<figure class="wp-caption"><a target="_blank" rel="nofollow" href="https://i0.wp.com/blog.haunschmid.name/wp-content/uploads/2017/02/ConfigurationManager.png"><img sizes="(max-width: 531px) 100vw, 531px" alt="Screenshot of the SQL Server configuration manager." class="wp-image-5408" src="https://i0.wp.com/blog.haunschmid.name/wp-content/uploads/2017/02/ConfigurationManager.png?resize=456%2C206" data-recalc-dims="1"></a><br>
<figcaption class="wp-caption-text">Use the configuration manager to enable TCP/IP.</figcaption>
</figure>
<p>All you need to connect (with Windows authentication) now is the following code:</p>
<p><code><br>
library(dplyr)<br>
conn &lt;- RSQLServer::src_sqlserver(&quot;servername&quot;, database = &quot;dbname&quot;)</code></p>
<h2>Limitations</h2>
<p>Currently dplyr cannot deal with schema names in code like this:</p>
<p><code><br>
conn %>% tbl(&quot;schema.table&quot;)<br>
</code></p>
<p>In this case you have to use</p>
<p><code><br>
conn %>% tbl(sql(&quot;SELECT * FROM schema.table&quot;))<br>
</code></p>
<p>But this issue is getting addressed in <a target="_blank" rel="nofollow" href="https://github.com/rstats-db/DBI/issues/24">rstats-db/DBI/issues/24</a> (dplyr uses DBI).</p>
<h1>What now?</h1>
<figure class="wp-caption"><a target="_blank" rel="nofollow" href="https://i0.wp.com/blog.haunschmid.name/wp-content/uploads/2017/02/QueryAllTheThings.jpg"><img sizes="(max-width: 400px) 100vw, 400px" alt="Query all the data - Meme | MSSQL Server with R" class="wp-image-5410" src="https://i0.wp.com/blog.haunschmid.name/wp-content/uploads/2017/02/QueryAllTheThings.jpg?resize=400%2C300" data-recalc-dims="1"></a><br>
<figcaption class="wp-caption-text">Query all the data!</figcaption>
</figure>
<p>Now that you have a connection to your SQL Server it's time to query all the data.</p>
<p>Check out the <a target="_blank" rel="nofollow" href="https://github.com/rstudio/webinars/tree/master/14-Work-with-big-data">RStudio: Work with big data</a> webinar or the <a target="_blank" rel="nofollow" href="https://cran.r-project.org/web/packages/dplyr/vignettes/databases.html">dplyr databases vignette</a>.</p>
<p>Have fun! <img alt="🙂" class="wp-smiley" src="https://i0.wp.com/s.w.org/images/core/emoji/2.2.1/72x72/1f642.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h1>Useful links</h1>
<ul>
<li><a target="_blank" rel="nofollow" href="https://github.com/imanuelcostigan/RSQLServer">imanuelcostigan/RSQLServer</a></li>
<li><a target="_blank" rel="nofollow" href="https://cran.r-project.org/bin/windows/Rtools/">Rtools</a></li>
<li><a target="_blank" rel="nofollow" href="http://stackoverflow.com/a/16429490/1117932">stackoverflow.com: I/O Error: SSO Failed: Native SSPI library not loaded</a></li>
<li><a target="_blank" rel="nofollow" href="http://stackoverflow.com/questions/18841744/jdbc-connection-failed-error-tcp-ip-connection-to-host-failed">stackoverflow.com: JDBC connection failed, error: TCP/IP connection to host failed</a></li>
</ul>
<p>The post <a target="_blank" rel="nofollow" href="http://blog.haunschmid.name/accessing-mssql-server-r-rsqlserver-dplyr/">Accessing MSSQL Server with R (RSQLServer with dplyr)</a> appeared first on <a target="_blank" rel="nofollow" href="http://blog.haunschmid.name/">verenahaunschmid</a>.</p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://blog.haunschmid.name/accessing-mssql-server-r-rsqlserver-dplyr/"> r-bloggers – verenahaunschmid</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/mK38b9azt6Y"></a></div>