You can query data from a SQL Server database directly from R using the RODBC package.
install.packages("RODBC")
First you need to form a connection
library(RODBC) ##connection string cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost; database=SSRSTraining;trusted_connection=yes;")
We use the odbcDriverConnect() function. Inside we pass a connection = value
Driver = {SQL Server Native Client 11.0}; — this is based on the version of SQL Server you have
server=localhost; — I used localhost because the SQL Server was on the same computer I was working with. Otherwise, pass the server name
database=SSRSTraining; — name of database I want to work with
trusted_connection=yes; — this means I am able to pass my Windows credentials.
If you don’t have a trusted connect pass the user Id and password like this
uid = userName; pwd = Password;
Note each parameter is separated by a semicolon
Query the database
> ##passes query to SQL Server > df <- sqlQuery(cn, "select * FROM [SSRSTraining].[dbo].[JobDataSet]") > head(df) Name Job Hours Complete 1 Sally Predictive Model 1 n 2 Philip Maintanence 10 n 3 Tom Ad-hoc Report 12 y 4 Bob SSRS 3 y 5 Philip Tableau 7 n 6 Tom Tableau 9 n
using sqlQuery() – pass through the connection string (cn) and enclose your query in ” ”