在R中执行SQL Server存储过程

Question

当我在R中执行SQL Server 2008存储过程时，遇到了问题。R中的执行方式如下:

---

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() 

出现在R中的错误：

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

存储过程调用特征和日期从数据库中提取信息：

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

---

我在其他系统（如SAS和SPSS）中执行这个存储过程时没有问题，但是当我在R中尝试时会出现错误。

注意：在SQL Server中，数据的格式为“aaaa-mm-dd”，我要求在R中使用相同的格式。

有人可以帮我解决R中的问题吗？

提前感谢。

- jareva

---

检查您存储过程返回的查询结果。在任何日期时间列中是否存在非日期值？您是否在查询中创建或转换日期时间？错误不是存储过程调用，而是尝试从具有问题日期时间字段的查询构建数据框架。 - Parfait

存储过程在所有系统中都可以工作：SPSS、SQL Server、Visual Basic，但在R中无法工作。任何日期时间列中都没有非日期值。唯一可能有问题的数据是1753-01-01。我没有在查询中创建或转换日期时间。还有其他建议吗？谢谢。 - jareva

Answer 1

最后，我看到了问题所在并且知道了如何解决它。需要在函数sqlExecute中使用参数as.is=T：

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

- jareva

Answer 2

你愿意尝试以下内容吗？

@date_process 需要一个日期时间值，但我不认为这会有太大的区别。我选择使用 RODBCext，因为 1）更安全免于 SQL 注入，2）使用单引号（'）比我想象的更容易导致 SQL 问题。

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() 

在单引号问题上，也值得尝试反转您的单引号和双引号。所以'EXEC ihshsf35.FPR_GET_PF10 @tipo_pes = "F", @date_process = "2016-11-30"'。

- Benjamin

---

我尝试了你的建议，但结果相同。错误在于as.POSIXlt.character(x, tz, ...) : 字符串不是标准且明确的格式。此外：警告消息：关闭未使用的RODBC句柄14。还有其他建议吗？ - jareva

我也尝试过： oe <- as.POSIXct(strptime("2016-12-31 23:00:000", "%Y-%m-%d %H:%M:%S")); data <- sqlQuery(dbhandle, 'exec ihshsf35.FPR_GET_PF10 @tipo_pes="F", @date_process="oe"'); 但结果相同。请注意，所有日期时间都没有任何NA。 - jareva