Connect R to local sql server using RSQLServer package

Question

I am trying to connect R and local sql server i.e. windows authentication based using dplyr package as I have large data set to work upon and cannot pull all of them into R. Can someone please help me with the connection.

I believe RSQLServer::src_sqlserver under RSQLServer package would help but I am not able to do so.

I tried using this command:

install_github('rsqlserver', 'agstudy',args = '--no-multiarch')

but I get an error message

:ERROR:
dependency 'rClr' is not available for package 'rsqlserver'.

Please help

Zahiro Mor · Accepted Answer · 2017-04-19 15:44:55Z

1

the error message you got is caused by the inability to install a the rClr package. however - you can try the following method:

a. install package 'RODBC' from cran and attach it:

install.packages("RODBC")

library("RODBC")

b. define a connection: (change to your server's IP or localhost)

connection <- odbcDriverConnect("driver=SQL Server; server=XXX.XXX.XXX.XXX; database=[your db name]; trusted_connection=true")

c. type your query using the defined connection:

sqlQuery(connection , 'select * from information_schema.tables')

cool?

edited Apr 19, 2017 at 15:44

answered Apr 19, 2017 at 15:38

Zahiro Mor

1,7181 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nitin Over a year ago

Hey, I want the connection to run dplyr function directly on the local database instead of pulling the entire data frame into R. Hence require a RSqlserver package for this.

Zahiro Mor Over a year ago

why? what exactly are you trying to do? what functionality?

Nitin Over a year ago

I am working on a large data set and cannot pull the entire data frame so I want to use the dplyr package for data wrangling on the complete data.

Nitin Over a year ago

The link has it all but am unable to execute the same as the package is not getting installed. google.co.in/amp/s/www.r-bloggers.com/…

Zahiro Mor Over a year ago

if you wanna query the db in anyway, you can use RODBC as suggested. I don't know about dplyr functionality but i suspect it has nothing to do with the connection method. you can also use dplyr with it. maybe there is something I'm missing imho but using dplyr as a DB is redundant if you're connected to SQL DB. the data resides on the DB and not in memory on both cases\

|

Collectives™ on Stack Overflow

Connect R to local sql server using RSQLServer package

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related