Sql Server Connection in R

techanswers88

youtube video link with explanations for these examples https://youtu.be/gA0jYDP6XhA

library(odbc)
library(DBI)
library(ggplot2)


# Check what odbc drivers are installed and available
unique(odbcListDrivers()[[1]])




# When using the trusted connection 
con <- odbc::dbConnect(odbc(), Driver = "SQL Server", 
                 Server = "yourserverName", 
                 Database = "NorthWind", 
                 Trusted_Connection = "True")



# When you have got a login and password and the server name 
con <- odbc::dbConnect(odbc(), Driver = "SQL Server", 
                       Server = "yourserverName", 
                       Database = "NorthWind", 
                       uid = "yourlogin", 
                       pwd="yourpassword")


# Reading the data from 
df <- DBI::dbGetQuery(con,"Select Country, count(*) as n  from  Customers group by Country")
df <- DBI::dbReadTable(con, 'Customers')


pl <- ggplot(data = df, aes(x = Country, y = n))
pl <- pl + geom_bar(stat= "identity")
pl


# Writing to the database
DBI::dbWriteTable(con, 'df', df)

# Delete data
DBI::dbExecute(con, "Delete from df")

# Appen data
DBI::dbAppendTable(con,'df',df)

# Remove the table
DBI::dbRemoveTable(con,'df')
  
# Execute stored procedures
DBI::dbCallProc(conn,'Ten Most Expensive Products')
DBI::dbExecute(con, 'EXEC [Ten Most Expensive Products]')

# When you want to get the results output from your stored procedures

DBI::dbGetQuery(con, 'EXEC [Ten Most Expensive Products]')

# When you have to provide the parameters to the store procedure
# in this example we are supplying the beginning and ending date

DBI::dbGetQuery(con, "EXEC  [Employee Sales by Country]
        @Beginning_Date = '01/01/1995',
        @Ending_Date = '01/01/2010'")

Add a new chunk by clicking the Insert Chunk button on the toolbar or by pressing Ctrl+Alt+I.

When you save the notebook, an HTML file containing the code and output will be saved alongside it (click the Preview button or press Ctrl+Shift+K to preview the HTML file).

The preview shows you a rendered HTML copy of the contents of the editor. Consequently, unlike Knit, Preview does not run any R code chunks. Instead, the output of the chunk when it was last run in the editor is displayed.