Help with this error.(Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbReadTable’ for signature ‘"Microsoft SQL Server", "missing"’ > )

prateek26394 · November 29, 2018, 10:39am

I have a connection with Sql Server in which there is a table which I have to store in a data frame in R.
I am using this command

library(odbc)
library(DBI)
dbListTables(dbConn,table_name="Workdone")

(Till here it works fine.)

But,

dataset<-dbReadTable(dbConn,table_name="Workdone")

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbReadTable’ for signature ‘"Microsoft SQL Server", "missing"’ >

Error pops up.

reprex::reprex({work<-dbReadTable(dbConn,table_name="Workdone")})
#> Error in dbReadTable(dbConn, table_name = "CrewDuties"): could not find function "dbReadTable"

Please help with this error.

EconomiCurtis · November 29, 2018, 12:52pm

Some discussion earlier this year that suggest;

github.com/r-dbi/odbc

`dbWriteTable` fails to write to non-default schema

opened 10:46PM - 17 Jul 17 UTC

closed 06:23PM - 20 Mar 18 UTC

EthanTaft

### Issue Description and Expected Result -`dbWriteTable` does not write to non…-default schema. It instead writes to dbo.non-default schema.tablename -Would like functionality to specify what schema within `dbWriteTable()` ## Database SQL Server 2012 # Reproducible Example ```r CREATE TABLE guest.MikeTestTable( [a] [float] NULL, [b] [float] NULL, [c] [varchar](255) NULL) #Create a df to insert into guest.MikeTestTable df <- data.frame(a = c(10, 20, 30), b = c(20, 40, 60), c = c("oneT", "twoT", "threeT")) #Create a connection: con <- DBI::dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server}; server=localhost; database=SAM; trustedConnection=true;") #Try to write contents of df to the table using `dbWriteTable` DBI::dbWriteTable(conn = con, name = "guest.MikeTestTable", value = df, append = TRUE) #Create a query to read the data from `"guest.MikeTestTable"`: q <- "SELECT [a] ,[b] ,[c] FROM guest.MikeTestTable" ##Read the table into R to show that nothing actually got written to the ##table but that it recognizes `guest.MikeTestTable` does exist: DBI::dbGetQuery(con, q) [1] a b c <0 rows> (or 0-length row.names) ``` Upon further inspection, SSMS shows a table that exists with the schema and name of: `dbo.guest.MikeTestTable`

that error indicates you need to update to the devel version of DBI

plus additional suggstions

prateek26394 · December 5, 2018, 5:00am

I found an alternative solution.
I am using
dbGetQuery() function. In which I pass SQL query and extract desired records into a data frame.
The beauty of the function is it can accept complicated SQL queries (one which are long and have many functions) and can make the query "dynamic" in shiny app by initializing input variables.
For example:-

query<-paste0("SELECT E.ShortName, E.StaffID, C.FlightDate, C.DutyCode 
From ops.CrewDuties C Inner Join Com.EMPLOYEE E On C.EmployeeID = E.EmployeeID  WHERE FlightDate between '",input$date1,"'AND '",input$date2,"'")

Crew_duties<-dbGetQuery(dbConn,query)

Thank You for the help.

system · December 12, 2018, 5:00am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.