Connecting from Rtools and RStudio

R is a free software environment for statistical computing and graphics. It compiles and runs on a wide variety of UNIX platforms, Windows and MacOS.

Note

To learn more about R, please visit the r-project.org.

Rtools and RStudio both support connectivity to Querona by using the SQL Server ODBC driver and the ODBC package. Querona is compatible with SQL Server ODBC driver v13 and later.

To use the ODBC package in R, you first have to install it:

install.packages('odbc');

The following example code uses the compatible SQL Server ODBC v13 driver and integrated authentication:

library(odbc);

qOdbc13 <- dbConnect(odbc(),
                   Driver = "ODBC Driver 13 for SQL Server",
                   Server = "your-source-server-name",
                   Database = "YourDatabaseName",
                   Trusted_connection = "Yes")

The following example demonstrates connecting using standard authentication:

library(odbc);

qOdbc13 <- dbConnect(odbc(),
             Driver = "ODBC Driver 13 for SQL Server",
             Server = "your-source-server-name",
             Database = "YourDatabase",
             UID = "yourUser",
             PWD = "yourPassword")

data <- dbGetQuery(qOdbc13, "select * from YourTable")

The following example does not work and returns an error, because it uses an obsolete ODBC driver to connect:

oldOdbc <- dbConnect(odbc(),
             Driver = "SQL Server",
             Server = "your-source-server-name",
             Database = "YourDatabase",
             UID = "yourUser",
             PWD = "yourPassword")

Error in result_fetch(res@ptr, n, ...) :
nanodbc/nanodbc.cpp:2525: HY000: [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server

The “SQL Server” ODBC driver ships with Windows only to support legacy applications and does not support new SQL data types and features introduced since the release of SQL 2005.