Reading data from Microsoft SQL Server into R

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.

---

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/

---

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'

---

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.

---

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')

---

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

It can be found on GitHub and CRAN.

---

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')

---

library("RODBC")

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

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

currTableDF<-sqlQuery(dbhandle,currTableSQL)