Skip to content

R Code - mssql_rstudio

Source: czammar/mssql_rstudio: Having fun with mssql and Rstudio (github.com)

Overview

Having fun with mssql and Rstudio.

Docker

Let’s build the Dockerfile based in rocker/verse:3.6.1 (https://hub.docker.com/r/rocker/verse)

docker build -t my_image .

Please note that default user is rstudio and instances run in localhost:8787

docker run -d -p 8787:8787 -e ROOT=TRUE -e PASSWORD=yourpasswordhere my_image

Querying MSSQL

# Install packages if missing
list.of.packages <- c("DBI", "odbc")
new.packages <- list.of.packages[!(
  list.of.packages %in% installed.packages()[,"Package"]
  )]
if(length(new.packages)){
  install.packages(new.packages)
}

# Checking that we have the driver "ODBC Driver 17 for SQL Server"
library(odbc)
sort(unique(odbcListDrivers()[[1]]))

# Connection
library(DBI)

con <- dbConnect(odbc::odbc(),
                 driver = "ODBC Driver 17 for SQL Server",
                 database = "",
                 uid = "",
                 pwd = "",
                 server = "XX.XXX.XX.XXX,1433"
                 )

# Dummy query with previous connection
dbGetQuery(con,'
  select *
  from dbo.my_table
  limit 100
')

Backlinks:

list from [[R Package - mssql_rstudio]] AND -"Changelog"