1

I use the following specs to connect to db from Matlab and it works:

% Matlab code
spec.dbName = 'xxxyyy';
spec.login = 'uid';
spec.pwd = 'mypwd';
spec.driver = 'net.sourceforge.jtds.jdbc.Driver';
spec.url = 'jdbc:jtds:sqlserver://vmsqlprod7:1234/xxxyyy';
conn = database(spec.dbName, spec.login, spec.pwd, spec.driver, spec.url);

I'm almost sure this is all I need to connect from R. But I can't seem to make it work with the following code:

## R code
require(RODBC)
con <- odbcDriverConnect(connection=
                    "driver=net.sourceforge.jtds.jdbc.Driver;
                     server=jdbc:jtds:sqlserver://vmsqlprod7:1234/xxxyyy;
                     database=xxxyyy;
                     uid=uid;
                     pwd=mypwd")

It throws out this error:

[ODBC Driver Manager] Data source name not found and no default driver specified

I've read this and I suspect it's about the format of the url or driver string, but I don't know exactly how to make it recognizable by R.

I can really use some help here!

Environment: Windows 7, 64 bit.

R version: 3.3.3

Community
  • 1
  • 1
data-monkey
  • 1,535
  • 3
  • 15
  • 24
  • If you are in windows, I usually use the ODBC administrator to set up the connection and then call it by its name in R. I find debugging goes a bit smother that way. – Ian Wesley Jun 07 '17 at 14:35
  • @Ian Wesley I used to do that. It's only because there's a change on the DB admin end that the connection no longer works. I figured it's probably easier/safer to specify everything in the connection string and leave nothing to chance, just like what I did with Matlab. – data-monkey Jun 07 '17 at 14:57
  • IMHO you may regret that next time something moves and you have to open up all your scripts and edit them one by one. Much easier to manage it all in one place. – Ian Wesley Jun 07 '17 at 15:02
  • @Ian Wesley the Matlab/R connection script is used for all Matlab-/R-related projects. I know what you mean but it's because of the communication cost with DB team etc that leads me towards this solution. – data-monkey Jun 07 '17 at 15:20

0 Answers0