none
Connect Sql server from R Studio / RTVS failed RRS feed

  • Question

  • I am trying to connect SQL server from R studio / RTVS using the string below:

    connStr <- "Driver=SQL Server;Server=<SQL_instance_name>;Database=<database_name>;Uid=<user_name>;
    Pwd=<user password>"

    but then I get the error msg like this, I am pretty sure I sued the right connection string on server / DB / Username / Password, it is just not getting through.

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    Could not open data source.
    [Microsoft][ODBC Driver Manager] Connection not open
    ODBC Error in SQLDisconnect
    Error in doTryCatch(return(expr), name, parentenv, handler) :
      Could not open data source.

    Anyone who can offer a help will be really appreciated.

    Thursday, November 17, 2016 2:56 PM

All replies

  • Is your SQL Server running locally or remote? If remote can you confirm that you have set up the connectivity appropriately. If using TCP for example you should open port 1433 via the firewall wf.msc. If not please provide additional information. Are you able to connect to SQL Server remotely via SSMS.

    Thursday, November 17, 2016 4:48 PM
  • Hi thanks for the quick reply.

    Yes, I have tried both locally and remotely, they are both able to be connected via SSMS but just not from R environment. BTW, 1433 port is open.

    Friday, November 18, 2016 12:08 AM
  • This works for me.


    library(RODBC)
    dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=Your_Server_Name\\SQLEXPRESS; Database=TestDB;Uid=; Pwd=; trusted_connection=yes")
    initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
    odbcClose(channel)

    Check out these links.

    http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error

    https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/


    MY BOOK

    Friday, November 18, 2016 3:37 AM
  • Also, under Tools > Options > R Tools . . .make sure you are pointing to the correct R engine.

    Also, under SQL Server Configuration Manager, you should see something very similar to this . . . withthe Default Port set to 1433.  I think you have this already . . .


    MY BOOK

    Friday, November 18, 2016 3:49 AM
  • This is the right way to create the connection for ODBC:

    conn <- odbcDriverConnect('driver={SQL Server};
    server=SOVITHOME-PC\\RAY;
    database=EmergencyRMDb;
    uid=sa;
    pwd=myPwd***')

    Thanks.
    Tuesday, April 18, 2017 12:24 AM