Error When Running Data Function With "Force Server" On Run Location

Question

I'm having a constant error whenever I try to execute my data function forcing it to run on Spotfire Server.

The script works fine on R Studio and also on Spotfire if the Run Location configuration is set to "Force Local" or "Default".

When I use the Force Server, I get the following error message when executing a query:

Could not execute function call. TIBCO Spotfire Statistics Services returned an error: 
'Error in sqlQuery(myconn, mappingQuery, errors = TRUE, rows_at_time = : first argument is not an open RODBC channel'.   
 at Spotfire.Dxp.Data.DataFunctions.Executors.RemoteFunctionClient.OnExecuting(FunctionClient funcClient)    
 at Spotfire.Dxp.Data.DataFunctions.Executors.AbstractFunctionClient.<RunFunction>d__0.MoveNext() 
 at Spotfire.Dxp.Data.DataFunctions.Executors.SPlusFunctionExecutor.<ExecuteFunction>d__0.MoveNext() 
 at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.<ExecuteFunction>d__6.MoveNext()

Even if I have a straightforward script and query like the one below, the results are the same:

require(RODBC)

myconn   <- odbcDriverConnect("Driver={SQL Server};Server=MY_SERVER;Database=MY_DATABASE;Trusted_Connection=True")
# myconn <- odbcDriverConnect("Driver={SQL Server};Server=MY_SERVER;Database=MY_DATABASE;UID=MY_USER;Pwd=MY_PASSWORD") ## Same result with trusted connection or user/password
query    <- "SELECT * FROM MY_TABLE"
df       <- sqlQuery(myconn, query)

print(df)

Have anyone ever seen this?

Thanks!

Answer 1

The error seems to suggest that the "RODBC" package's 'odbcDriverConnect()' function might not be finding the ODBC drivers it needs on the server that TIBCO Spotfire Statistics Services (TSSS) is installed on.

Try installing the required odbc drivers on the machine where the TIBCO Spotfire Statistics Services is installed.