0

I'm currently using the RODBC package in R to run queries against SQL Server databases. Knitr now has the ability to use SQL code chunks (using the DBI package). 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.

I've looked through DBI's help file, and vignettes, but am at a loss. They mention RSQLServer 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.

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.

Does anyone know how to get the DBI example working in a similar fashion to the RODBC example?

# *** Query - edit table name to work with your db
my_query = "select top 100 * from my_table"

# *** This code works ***
library(RODBC)
# Replace <<SERVER>> and <<DB>> with your server and database, respectively
SQLServer <- odbcDriverConnect(connection = "Driver={SQL Server};server=<<SERVER>>;database=<<DB>>;trusted_connection=yes")
sqlResult <- sqlQuery(SQLServer, my_query)


# *** This code doesn't ***
library(DBI)
# Replace <<SERVER>> and <<DB>> with your server and database, respectively
SQLServer <- dbConnect(dbDriver("ODBC"), "server=<<SERVER>>;database=<<DB>>;trusted_connection=yes")
sqlResult <- dbSendQuery(con, my_query)
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
John Tarr
  • 717
  • 1
  • 9
  • 21
  • I've edited the question to provide a clear problem statement in the question itself, along with a reproducible example. Can someone please review for re-open? – John Tarr Feb 27 '17 at 14:43
  • 1
    You might want to read this: https://github.com/rstats-db/odbc – p0bs Mar 01 '17 at 11:49

1 Answers1

1

You could try the rjdbc package, using the Microsoft SQL Server JDBC Driver from: https://www.microsoft.com/en-us/download/details.aspx?id=21599

And then do something along the lines of:

drv <- JDBC(driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
            classPath = 'path/to/sqljdbc4.jar") 

con <- dbConnect(drv, 'jdbc:sqlserver:path/to/server;user=user;password=password;)
Taeke
  • 179
  • 5
  • Thanks for the answer, but I would prefer to stick with packages available on CRAN, and this solution requires a driver that isn't available on CRAN, and, as far as I'm aware, wouldn't be cross platform. I'll update my question to better reflect that. – John Tarr Feb 10 '17 at 20:32
  • @JohnTarr https://cran.r-project.org/web/packages/RJDBC/index.html – Hack-R Apr 06 '20 at 23:21