61

Does anyone have a connection string example for using RODBC and connecting to MS SQL Server 2005 or 2008.

Thank you.

Michele
  • 7,793
  • 5
  • 39
  • 69
Brandon
  • 996
  • 2
  • 15
  • 19

5 Answers5

104
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')
Josh O'Brien
  • 148,908
  • 25
  • 332
  • 435
Henrico
  • 2,369
  • 1
  • 17
  • 10
  • 1
    I'm trying this but am getting error "SQL Server does not exist or access denied." Do you have to enter a username/password? Also I have a dsn on my remote site but this does not seem to be mentioned. The RODBC docs were not all that clear to me – pssguy Nov 19 '12 at 21:45
  • 3
    The example uses the option "trusted_connection=true" for automatic windows authentication. You can also provide username=... and password=... as parameters in the connection string. – Henrico Dec 07 '12 at 20:21
  • 16
    actually for me `username` and `password` did not work - i had to provide the parameters `uid=...;` and `pwd=...;`. – Seb Aug 30 '13 at 11:00
  • Any ideas how to obtain column names from the DW into R? – BlackHat Jan 20 '14 at 22:56
  • 1
    NOTE I had to use the IPV6 Ip address instead of the server name to get this to work. – Matt Johnson Jan 27 '16 at 21:00
  • 2
    Just for what it's worth, if you are familiar with the `DBI` interface, the newer [`odbc`](https://cran.r-project.org/web/packages/odbc/index.html) package is DBI-compliant and an alternative to the `RODBC` package. – cole Nov 27 '17 at 16:42
8

Taken from a posting to r-help:

 library(RODBC)
 channel <- odbcDriverConnect("driver=SQL Server;server=01wh155073")
 initdata<- sqlQuery(channel,paste("select * from  test_DB .. 
 test_vikrant"))
 dim(initdata)
 odbcClose(channel)
RockScience
  • 15,586
  • 22
  • 74
  • 117
IRTFM
  • 240,863
  • 19
  • 328
  • 451
  • How would you do this from Mac OSX? Same way? Is that possible, with the windows login requirement? – mikebmassey Jan 28 '12 at 21:24
  • Seems unlikely this would work using same machine with MS's SQL Server on a Mac, but with some sort of remote access terminal arrangement, perhaps. – IRTFM Nov 19 '13 at 16:39
3

If you have to include the USERNAME and PASSWORD:

library(RODBC) # don't forget to install it beforehand

my_server="ABC05"
my_db="myDatabaseName"
my_username="JohnDoe"
my_pwd="mVwpR55zobUldrdtXqeHez"


db <- odbcDriverConnect(paste0("DRIVER={SQL Server};
                                 server=",my_server,";
                                 database=",my_db,";
                                 uid=",my_username,";
                                 pwd=",my_pwd))


sql="SELECT * FROM dbo.MyTableName" #dbo is the schema here
df <- sqlQuery(db,sql)
Ibo
  • 3,351
  • 6
  • 33
  • 51
1

Try to use RSQLS package: https://github.com/martinkabe/RSQLS

Very fast pushes data from data.frame to SQL Server or pulls from SQL Server to data.frame.

Example:

library(devtools)
install_github("martinkabe/RSQLS")
library(RSQLS)

cs <- set_connString("LAPTOP-USER\\SQLEXPRESS", "Database_Name")
push_data(cs, dataFrame, "dbo.TableName", append = TRUE, showprogress = TRUE)
df <- pull_data(cs, "SELECT * FROM dbo.TableName", showprogress = TRUE)

This solution is much faster and more robust than RODBC::sqlSave or DBI::dbWriteTable.

mateskabe
  • 249
  • 1
  • 13
0

First You have to Create/configure DSN (ODBC connection with specific DB)

Then install RODBC library.

library(RODBC)
myconn <-odbcConnect("MyDSN", uid="***", pwd="*******")

fetchData<- sqlQuery(myconn, "select * from tableName")
View(fetchData)
close(myconn)
Moody_Mudskipper
  • 39,313
  • 10
  • 88
  • 124
Mukesh Pandey
  • 121
  • 2
  • 1