Friday, July 21, 2017

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and Rstudio.  These are my learnings as part of this exercise.

The following are the steps that need to be taken for successful connection to the SQLExpress database

  1. Ensure that the SQL Server database has permission for the user that you are using as shown below.

  2. Ensure that the NamedPipes and TCP/IP protocols are Enabled using the SQL Server Configuration Manager. 

  3. Otherwise you will receive an error as -- 
    • [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
  4. When you are enabling the TCP/IP protocol ensure that you specify the port 1433 at the appropriate location.  Otherwise you will receive an error as -- 
    • [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. 
    • [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
  5. Restart the SQL SERVER service after configuring the above step.
  6. Ensure that the firewall is open to the port 1433.
  7. Ensure that you can run a Telnet session to your sql server IP address.
  8. There is a default user group created named SQLRUserGroupSQLEXPRESS when you install SQLEXPRESS with R   Ensure that the user connecting to the SQL server from R Studio belongs to this group.  Otherwise you will receive an error as --  Error in doTryCatch(return(expr), name, parentenv, handler) :   Could not open data source.

Now coming to the code I have used in R Studio to import the data from the SQL Server Database into R is as follows:

#Connection string to connect to SQL Express
connStr <- font=""> paste("Driver=SQL Server; Server=", "SQLEXPRESS",
                 ";Database=", "TestDB", ";Trusted_Connection=true;", sep = "");

#Get the data from the Table
SQL_testdata <- font=""> RxSqlServerData(table = "dbo.rental_data",
                                  connectionString = connStr, returnDataFrame = TRUE);

#Import the data into a data frame
testdata <- font=""> rxImport(SQL_testdata);

#See the structure of the data 

#See the top rows

Hope this is useful for those who would like to connect to sql server using RStudio.

No comments:

Two ways to reset the identity of a table in sql server.

I know of the following two ways to reset the identity of a table in sql server. You often tend to use dummy data during the course of y...