title: How to connect R DBI package to SQL Server?
body: <p>I'm currently using the <a href="https://cran.r-project.org/web/packages/RODBC/index.html" rel="nofollow noreferrer">RODBC</a> package in R to run queries against SQL Server databases. Knitr now has the ability to use <a href="http://rmarkdown.rstudio.com/authoring_knitr_engines.html#sql" rel="nofollow noreferrer">SQL code chunks</a> (using the <a href="https://cran.r-project.org/web/packages/DBI/index.html" rel="nofollow noreferrer" title="DBI">DBI package</a>). I'd like to take advantage of this, as it allows me to have clean SQL embedded in my RMarkdown (RMD) document. However, I'm running into trouble getting DBI to connect to my SQL Server environment.</p>
  
  <p>I've looked through DBI's help file, and vignettes, but am at a loss. They mention <a href="https://cran.r-project.org/web/packages/RSQLServer/index.html" rel="nofollow noreferrer">RSQLServer</a> for SQL Server, but it has been pulled from CRAN. I'm aware I could install it from gitHub, using devtools, but I would prefer not relying on packages that can't be kept up on CRAN. I'd also prefer to avoid the RJDBC connector, as I've heard of reliability issues, and it makes the code that much less portable.</p>
  
  <p>While my goal is to use this in RMD files, the below code is a simple R Script to keep the example as minimal as possible.</p>
  
  <p>Does anyone know how to get the DBI example working in a similar fashion to the RODBC example?</p>
  
  <pre><code># *** Query - edit table name to work with your db
  my_query = "select top 100 * from my_table"
  
  # *** This code works ***
  library(RODBC)
  # Replace &lt;&lt;SERVER&gt;&gt; and &lt;&lt;DB&gt;&gt; with your server and database, respectively
  SQLServer &lt;- odbcDriverConnect(connection = "Driver={SQL Server};server=&lt;&lt;SERVER&gt;&gt;;database=&lt;&lt;DB&gt;&gt;;trusted_connection=yes")
  sqlResult &lt;- sqlQuery(SQLServer, my_query)
  
  
  # *** This code doesn't ***
  library(DBI)
  # Replace &lt;&lt;SERVER&gt;&gt; and &lt;&lt;DB&gt;&gt; with your server and database, respectively
  SQLServer &lt;- dbConnect(dbDriver("ODBC"), "server=&lt;&lt;SERVER&gt;&gt;;database=&lt;&lt;DB&gt;&gt;;trusted_connection=yes")
  sqlResult &lt;- dbSendQuery(con, my_query)
  </code></pre>