37

Is it possible to read the data stored in MS SQL server from R interface?

If it is I would be also thankful if anyone could show the way to do it.

zx8754
  • 42,109
  • 10
  • 93
  • 154
Niko Gamulin
  • 63,517
  • 91
  • 213
  • 274
  • 5
    [RODBC](http://cran.r-project.org/package=RODBC)? And look at [R Data Import/Export manual](http://cran.r-project.org/doc/manuals/R-data.html#R-interface-packages). – Marek Oct 14 '10 at 12:15

7 Answers7

19

Tried the RODBC package already?

http://cran.r-project.org/web/packages/RODBC/index.html

There's also the RJDBC package : http://www.rforge.net/RJDBC/

See also : http://www.r-bloggers.com/connecting-to-sql-server-from-r-using-rjdbc/

Joris Meys
  • 98,937
  • 27
  • 203
  • 258
10

I've applied RODBC function suggested by other users. LinkSQL Server RODBC Connection

library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL 
Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')

change two variables based on your Data table. 'server=mysqlhost;database=mydbname'

Tanveer Badar
  • 4,541
  • 2
  • 27
  • 31
Jfang
  • 311
  • 2
  • 4
  • 9
6

Niko, What operating system are you running? The answer to your question varies, depending on the platform you are using.

If you are using Windows (of any stripe), connecting to MSSQL Server via ODBC (RODBC) makes the most sense. When I connect to a MSSQL Server on Linux, I use JDBC as suggested by Joris. I would assume that JDBC is also the best solution for Macs, but I could very well be wrong.

Choens
  • 1,282
  • 2
  • 13
  • 23
  • 2
    The freetds package on Linux has worked for me too, in conjunction with RODBC. – Dirk Eddelbuettel Oct 14 '10 at 13:40
  • 2
    I didn't even think of freetds but you're right that would be a good option. I usually feel that a JDBC connection is easier to set up, but it also requires Java which some folks find frustrating / difficult to work with. – Choens Oct 14 '10 at 18:46
  • Maybe you can take a look at the new promising option [rsqlserver package](https://github.com/agstudy/rsqlserver). – agstudy Nov 10 '13 at 23:20
5

There another option that seems to outperform RODBC and RJDBC

rsqlserver package written by agstudy.

Installation:

require(devtools)
install_github("rClr", 'jmp75')
install_github('rsqlserver', 'agstudy',args='--no-multiarch')
epo3
  • 2,573
  • 1
  • 29
  • 51
marbel
  • 6,933
  • 5
  • 46
  • 65
1

The latest library that allows you to connect to MSSQL databases is RSQLServer.

It can be found on GitHub and CRAN.

epo3
  • 2,573
  • 1
  • 29
  • 51
  • 2
    This package seems to no longer be available. >> Package ‘RSQLServer’ was removed from the CRAN repository. >> Formerly available versions can be obtained from the archive. >> Archived on 2016-12-01 as check problems were not corrected despite reminders. Source: https://cran.r-project.org/web/packages/RSQLServer/index.html – cbailiss Feb 08 '17 at 10:21
0

You can connect to SQL server using DBI package, which I think works better than RODBC. DBI is a database interface package for relational databases. for SQL I use it along with odbc package as in the example below.

Visit this page for full details: Database Queries with R

An example would be as follows

library(DBI) library(odbc) con <- dbConnect(odbc::odbc(), .connection_string = "driver={SQL Server}; server= ServerName; database=DatabaseName; trusted_conncetion=true")) dbGetQuery(con,'Select * from Table')

ok1more
  • 527
  • 4
  • 11
0

library("RODBC")

dbhandle <- odbcDriverConnect('driver={SQL Server};server=;database=;trusted_connection=true')

currTableSQL<-paste("SELECT * FROM ",sep="")

currTableDF<-sqlQuery(dbhandle,currTableSQL)

Shrey
  • 23
  • 7