Search code examples
asp.netrvisual-studio-2015sql-server-2014rodbc

RODBC Connection Failed

I'm aware that this has been asked before, but none of the solutions have worked for me.

I'm trying to create a connection between R and a SQL Server '14 Database that I'm developing with Visual Studio 2015 for an ASP.NET project. I've searched through all the suggested solutions to this and have tried everything I can think of, but I continue to get error messages that say the connection has failed.

This is my latest attempt:

cn<-odbcDriverConnect('driver={SQL Server};server="B8Q4VLD,MSSQLSERVER,1433";database=Northwind;trusted_connection=true')

> cn<-odbcDriverConnect('driver={SQL Server};server="B8Q4VLD,MSSQLSERVER,1433";database=Northwind;trusted_connection=true')

Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;trusted_connection=true") :
  [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;trusted_connection=true") :
  [RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;trusted_connection=true") :
  ODBC connection failed
Server Name: DESKTOP-B8Q4VLD\Tracey
Instance Name: MSSQLSERVER
Port Number: 1433

I've also tried including my username and ID instead of the trusted connection part. i.e. uid=Tracey;pwd=****

   > cn<-odbcDriverConnect('driver={SQL Server};server="B8Q4VLD,MSSQLSERVER,1433";database=Northwind;uid=Tracey;pwd=****')

Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;uid=Tracey;pwd=****") :
  [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;uid=Tracey;pwd=****") :
  [RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=\"B8Q4VLD,MSSQLSERVER,1433\";database=Northwind;uid=Tracey;pwd=Tracey") :
  ODBC connection failed

I've set the ENV PATH to R correctly.

Found a recent article that says something about sep=" "... though not sure what this is.

Any suggestion is welcome!

Solution

  • The problem was with the server. Because it's hosted locally, it needed to be 'localhost'.

    Checking the connection via cmd helped me work it out. C:\Users\Username>SQLCMD -L

    And I used this tutorial to find the correct syntax.

    cn<-odbcDriverConnect(connection="driver={SQL Server};server=localhost;database=Northwind;Trusted_Connection=true;")
dataFetchEUR <- sqlFetch(cn, 'Customers', colnames=FALSE,
                         rownames = TRUE)