Connect to MSSQL using DBI

Question

I can not connect to MSSQL using DBI package. I am trying the way shown in package itself

    m <- dbDriver("RODBC") # error

Error: could not find function "RODBC"

    # open the connection using user, passsword, etc., as
    # specified in the file \file{\$HOME/.my.cnf}
    con <- dbConnect(m, dsn="data.source", uid="user", pwd="password"))

Any help appreciated. Thanks

score 15 · Answer 1 · answered Mar 14 '17 at 09:48

15

As an update to this question: RStudio have since created the odbc package (or GitHub version here) that handles ODBC connections to a number of databases through DBI. For SQL Server you use:

con <- DBI::dbConnect(odbc::odbc(),
                      driver = "SQL Server",
                      server = <serverURL>,
                      database = <databasename>,
                      uid = <username>,
                      pwd = <passwd>)

You can also set a dsn or supply a connection string.

answered Mar 14 '17 at 09:48

dougmet

635
4
19

4

And the major advantage of `odbc` compared to `RODBC` is the improved insert performance using e. g. `dbWriteTable`, see [Feature request: Support for ODBC bulk insert and update operations](https://github.com/rstats-db/odbc/issues/34) – R Yoda Apr 26 '17 at 17:10
(read the `odbc` GitHub readme file)... In addition to installing the R package, you need to install an ODBC driver on your machine. If you install the ODBC SQL Server driver from Microsoft, then set `driver = "ODBC Driver 17 for SQL Server"` instead. Look at what drivers are available via `odbcinst -j` find the DRIVERS line location and view that file, e.g., `more /usr/local/etc/odbcinst.ini` – Brian D Mar 25 '21 at 20:23

score 1 · Accepted Answer · answered Jul 21 '14 at 14:29

It looks like there used to be a RODBC driver for DBI, but not any more:

http://cran.r-project.org/src/contrib/Archive/DBI.RODBC/

A bit of tweaking has got this to install in a version 3 R but I don't have any ODBC sources to test it on. But m = dbDriver("RODBC") doesn't error.

> m = dbDriver("RODBC")
> m
<ODBCDriver:(29781)> 
>

Suggest you ask on the R-sig-db mailing list to maybe find out what happened to this code and/or the author...

score -1 · Answer 3 · answered Aug 20 '15 at 09:53

Solved. I used library RODBC. It has great functionality to connect sql and run sql queries in R.

Loading Library:

library(RODBC)

# dbDriver is connection string with userID, database name, password etc.

dbhandle <- odbcDriverConnect(dbDriver)

Running Sql query

sqlQuery(channel=dbhandle, query)

Thats It.

Connect to MSSQL using DBI

3 Answers3

Loading Library:

Running Sql query

Linked