SQL R服务(jsonlite、curl)

Question

概述

我想使用SQL R Services调用API并将数据直接提取到SQL Server中。我一直在尝试在R中使用jsonlite和curl，在R GUI中使用成功，但在通过SQL Server T-SQL进行调用时失败。

R脚本

library(jsonlite,curl);
citibike <- fromJSON('http://citibikenyc.com/stations/json');
stations <- citibike$stationBeanList;
stations[,c(2,10)];

SQL脚本

DECLARE @Rscript NVARCHAR(MAX);
SET @Rscript = N'library(jsonlite, curl);
    citibike <- fromJSON('+''''+'http://citibikenyc.com/stations/json'+''''+');
     stations <- citibike$stationBeanList;
     OutputDataSet <- subset(stations, select=c("stationName", "stAddress1"))';
EXEC sp_execute_external_script
     @language = N'R',
     @script = @Rscript
    WITH RESULT SETS(([stationName] VARCHAR(500), [stAddress1] VARCHAR(500)));

SQL错误

Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 0
An external script error occurred: 
Error in open.connection(con, "rb") : Couldn't connect to server
Calls: source ... fromJSON_string -> parseJSON -> parse_con -> open -> open.connection
In addition: Warning message:
package 'jsonlite' was built under R version 3.3.2 

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) : 
  Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

问题

有没有我应该考虑的另一种方法，或者是否有一些简单的错误正在阻止它在SQL R Services中工作？

Answer 1

问题原来是一个防火墙规则，它只影响SQL R服务，但不影响在同一台计算机上通过R GUI或R CLI运行相同的脚本。