Using R With Rodbcext And Rodbc To Execute A Sql Stored Procedure

I am using the RODBCext package and the RODBC package to execute a stored procedure in SQL server 2008 using R. If I use this code the stored procedure works. query <- 'EXEC [d

Solution 1:

Seems like you want to use only the first column of the us11_12_00_school - which you pass in the loop-version of the function with us11_12_00_school[i,]. In the vectorised version though, you pass in the whole dataframe!

I havent tested it out, but i guess passing the dataframe as us11_12_00_school[1,] in the vectorised version would give you the expected results?

Solution 2:

I use the code below, and seems to work well. Hopefully it helps you too.

require(RODBC)
require(RODBCext)

ExecuteQuery <- function(query, arguments){

  dbhandle <- odbcDriverConnect('driver={SQL Server Native Client 11.0};server=SereverName;database=DbName;trusted_connection=yes')
  if(missing(arguments))
    result <- sqlExecute(dbhandle, query = query, fetch = TRUE)  
  else
    result <- sqlExecute(dbhandle, query =query, data=arguments, TRUE)  
  close(dbhandle)
  return(result)
}

CallProcWithSomeParams <- function(param1, param2){

  ExecuteQuery('exec dbo.procName ?, ?', data.frame(param1, param2))
}