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.
- 5RODBC? And look at R Data Import/Export manual.Marek– Marek2010-10-14 12:15:31 +00:00Commented Oct 14, 2010 at 12:15
Add a comment |
7 Answers
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/
Comments
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'
Comments
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.
3 Comments
Dirk is no longer here
The freetds package on Linux has worked for me too, in conjunction with RODBC.
Choens
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.
agstudy
Maybe you can take a look at the new promising option rsqlserver package.
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') 
3 Comments
Tanveer Badar
And no longer available according to another comment.
shosaco
RSQLServer != rsqlserver
marbel
that seems to be TRUE
The latest library that allows you to connect to MSSQL databases is RSQLServer.
1 Comment
cbailiss
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: cran.r-project.org/web/packages/RSQLServer/index.html
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')
1 Comment
marbel
rsqlserver was much faster when I used it
library("RODBC")
dbhandle <- odbcDriverConnect('driver={SQL Server};server=;database=;trusted_connection=true')
currTableSQL<-paste("SELECT * FROM ",sep="")
currTableDF<-sqlQuery(dbhandle,currTableSQL)
