Does anyone have a connection string example for using RODBC and connecting to MS SQL Server 2005 or 2008.
Thank you.
- 9Most connection string questions can be answered at connectionstrings.comJoe Stefanelli– Joe Stefanelli2011-04-19 18:00:17 +00:00Commented Apr 19, 2011 at 18:00
Add a comment |
5 Answers
library(RODBC) dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true') res <- sqlQuery(dbhandle, 'select * from information_schema.tables') 
6 Comments
pssguy
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
Henrico
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.
Seb
actually for me
username and password did not work - i had to provide the parameters uid=...; and pwd=...;.
Matt Johnson
NOTE I had to use the IPV6 Ip address instead of the server name to get this to work.
|
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) 
2 Comments
mikebmassey
How would you do this from Mac OSX? Same way? Is that possible, with the windows login requirement?
IRTFM
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.
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) 
Comments
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.
Comments
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) 
