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?

Forsake

I think you only need "SQL Server" as your driver. That's all I use to connect to SQL Server 2012. – Hyperacidity

I take it you have (in your actual implemetation) replaced hostname in server=hostname with your actual hostname....\ – Keikokeil

Answer 1

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)

Ailurophile

Which search prompt is it the windows one which then takes you to the odbc administration? – Forsake

Yes. Then setup a named connection to your database – Ailurophile

@Forsake Did this not work for you? If you need instruction on ODBC connections - google.com/… – Ailurophile

I got an error indicating odbcConnect function is not known. Should I install a package first? – Recapitulate

@Recapitulate - Likely that is the case. install.packages('RODBC') should work. – Ailurophile

Answer 2

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/

Cowrie

Answer 3

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.

Passover

Answer 4

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.

Potamic

It appears RSQLServer has been removed from CRAN – Guck

Answer 5

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>")

Kizzie