0

I have a working R script. Now, I extract the data from the SQL server into an Excel Sheet and import this Excel sheet into R. I would like to import directly from the SQL Server. The data I have:

  • DNS
  • Database
  • Login
  • Password

Is this enough to extract the database from this SQL Server?

George
  • 73
  • 4
  • 15

1 Answers1

3

I think it is enough. You can benefit from RODBC library like below: 

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

Ref: SQL Server RODBC Connection

EDIT: For your case, you can try:

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

Ref: https://blog.learningtree.com/querying-sql-server-data-from-r-using-rodbc/

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • The `dbhandle` gives an error: `Error: State 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL-server doesn't exist or access is denied.` – George Apr 04 '18 at 14:21
  • Make sure you give the correct server and database names, and you have rights to access the db and the table – Eray Balkanli Apr 04 '18 at 14:22
  • `dbhandle <- odbcDriverConnect('driver={SQL Server};server=DNS;database=DBNAME;trusted_connection=true')` is my entry in the console. But where do I give the login and password? – George Apr 04 '18 at 14:26
  • @George ... `trusted_connection` should be replacement of user/password. Also a DSN (not DNS) is not replacement of server but replacement of driver. In fact, DSNs can contain server/database/user information. Hence, you would use: `odbcConnect('DSN')`. This avoids exposing DB credentials and specs in code. – Parfait Apr 04 '18 at 16:33
  • So what should I replace driver with? – George Apr 05 '18 at 09:33