How do I connect to an SQL server database in R

Question

I'm trying to connect to the SQL Sever database using R but not sure on the details for the query string. I normally use SQL server management studio on SQL Server 2008 and connnect using single sign on. I found the below example

myconn <- odbcDriverConnect(connection="Driver={SQL Server 
Native Client 11.0};server=hostname;database=TPCH;
trusted_connection=yes;")

I get the below warning message

Warning messages:
1: In odbcDriverConnect(connection = "Driver={SQL Server \nNative Client 11.0};server=hostname;database=TPCH;\ntrusted_connection=yes;") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(connection = "Driver={SQL Server \nNative Client 11.0};server=hostname;database=TPCH;\ntrusted_connection=yes;") :
  ODBC connection failed

How do I go about finding the specifics i need?

Answer 1

The problem is simpler than this. The big clue is the \n in the error message. Something has re-flowed your connection string such that there is now a new-line character in the driver name. That won't match any registered driver name. Pain and suffering then ensues. Make sure your whole connection string is on a single line!

I often use: driver={SQL Server Native Client 11.0}; ...

and it works really well. Much better than having to rely on pre-defined connection names.

Answer 2

I have done this in the past with an odbc named connection that I've already had in place. In case you don't know, you can create one in windows by typing into the search prompt 'odbc' and selecting "set up data sources". For example - if you named an odbc connection 'con1' you can connect the following way:

con<-odbcConnect('con1') #opening odbc connection


df<-sqlQuery(con, "select  *
                         from ssiD.dbo.HOURLY_SALES
                         ") #querying table


close(con)

Answer 3

This works for me.

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

Also, see these links. RODBC odbcDriverConnect() Connection Error

https://www.simple-talk.com/sql/reporting-services/making-data-analytics-simpler-sql-server-and-r/

Answer 4

Try another ODBC driver. In windows press the "windows" button and then type "odbc". Click the "Data sources (ODBC)" link. Go to the "Drivers" tab to see the available drivers for SQL Server. Also - remove the " " spaces after the semicolons in your connection string. Note - the database property should point to a database name rather than a table name.

This worked for me:

odbcDriverConnect("Driver=SQL Server Native Client 11.0;Server=<IP of server>;Database=<Database Name>;Uid=<SQL username>;Pwd=<SQL password>")

Answer 5

First, you need to install the package 'RSQLServer', and all its dependencies. Then execute the following command in RStudio, with relevant parameters:

conn <- DBI::dbConnect(RSQLServer::SQLServer(),
                 server = '<server>', 
                 port = '<port>',
                 properties = list(
                   user = '<user>',
                   password = '<password>'
                 ))

Finally, db_list_tables(conn) gives you the list of tables in the corresponding database.