Connect to SQL Server RODBC

Question

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

Thank.

Answer 1

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

Answer 2

Taken from the publication in 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) 

Answer 3

Try using the RSQLS package: https://github.com/martinkabe/RSQLS

It transfers data very quickly from data.frame to SQL Server or 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 reliable than RODBC :: sqlSave or DBI :: dbWriteTable .

Answer 4

First you need to create / configure a DSN (ODBC connection to a specific database).

Then install the RODBC library.

 library(RODBC) myconn <-odbcConnect("MyDSN", uid="***", pwd="*******") fetchData<- sqlQuery(myconn, "select * from tableName") View(fetchData) close(myconn) 

Answer 5

If you need to specify a 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)