Execute SQL Server Stored Procedure in R

Question

I have a problem when I execute a SQL Server 2008 stored procedure in R. The execution in R follows as:

---

library(RODBC)

dbhandle <- odbcDriverConnect('driver={SQLServer};server=xxx;database=xxx;trusted_connection=true') 

data <-sqlquery(dbhandle, "EXEC ihshsf35.FPR_GET_PF10  @tipo_pes='F' , @date_process='2016-11-30'") 

odbcCloseAll() 

---

The error that appears in R:

Error in as.POSIXlt.character(x, tz, ...) : 
 character string is not in a standard unambiguous format

---

The stored procedure calls a characteristic and a date to extracts information from the database:

Procedure [ihshsf35].[FPR_GET_PF10] 
( 
        @tipo_pes varchar(1), 
        @date_process as datetime 
) 
AS 
BEGIN 

---

I don´t have problems executing this stored procedure from other systems like SAS and SPSS, but when I try in R brings me a error. Note: In SQL server the data has the format "aaaa-mm-dd" the same I asking in R

Can anyone can help me with the problem in R?

Thanks in advance

Answer 1

Finally, I see what the problem was and how to solve it. Need to use the parameter as.is=T with the function sqlExecute:

data <- sqlExecute(dbhandle, "exec FPR_GET_PJ  @tipo_pes='J', @date_process='2016-12-31'", 
                   fetch = TRUE, as.is=T)

Answer 2

Would you be wiling to give the following a try?

@date_process is expecting a date-time value, but I don't expect that would make much difference. I chose to use RODBCext because 1) safer from SQL Injection, and 2) Using single quotes (') has led me into more headaches with SQL than I care to recount.

library(RODBCext)

dbhandle <- odbcDriverConnect('driver={SQLServer};server=xxx;database=xxx;trusted_connection=true') 

data <-sqlExecute(dbhandle, 
                  "EXEC ihshsf35.FPR_GET_PF10  @tipo_pes=? , @date_process=?",
                  data = list(tipo_pes = 'F',
                              date_process = '2016-11-30 00:00:00'),
                  fetch = TRUE) 

odbcCloseAll() 

On the single quote issue, it's also worth trying a reversal of your single and double quotes. So 'EXEC ihshsf35.FPR_GET_PF10 @tipo_pes = "F", @date_process = "2016-11-30"'.