Error when creating procedure elements for Snowflake stored procedures in TIBCO Spotfire Information Services

Product:TIBCO Spotfire Analyst
Versions:All

Summary:
This article explains why you get errors when creating Snowflake stored procedures elements in TIBCO Spotfire. In short, Snowflake stored procedures and user defined functions are not supported in TIBCO Spotfire.

Details:
When creating a data source for Snowflake in TIBCO Spotfire Information Designer, even though the stored procedure is visible under the tree view it fails to create the procedure element with following error

"The previously selected source procedure was taken from a data source that can no longer be found on the server. Please select a new source"

System.Net.WebException: The remote server returned an error: (500) Internal Server Error <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Error retrieving metadata: Invalid column index: 7</faultstring><detail><ns3:InformationModelWebServiceException xmlns:ns3="http://spotfire.tibco.com/ws/2008/11/faults.xsd" xmlns:ns2="http://spotfire.tibco.com/ws/2008/11/im.xsd"><errorCode>Server.InformationModelService.DataSourceError</errorCode><message>Error retrieving metadata: Invalid column index: 7</message><stackTrace xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/></ns3:InformationModelWebServiceException></detail></soap:Fault></soap:Body></soap:Envelope>. at Spotfire.Dxp.Framework.HttpClient.NativeWebResponse..ctor(NativeHttpClient httpClient, NativeWebRequest request) at Spotfire.Dxp.Framework.HttpClient.WinINet.WinINetWebRequest.GetResponseCore() at Spotfire.Dxp.Framework.HttpClient.NativeWebRequest.GetResponse() at Spotfire.Dxp.Services.SessionEnabledSoapClient.GetWebResponse(WebRequest request) 2019-09-25T13:26:12,195-06:00 2019-09-25 19:26:12,195 sf_admin [Main Thread] WARN Spotfire.Dxp.Services.WebServiceBase`1[[Spotfire.Dxp.WebServices.ElementManagerService, Spotfire.Dxp.Services, Version=28.0.10709.3627, Culture=neutral, PublicKeyToken=789861576bd64dc5]] [(null)] - WebServiceCall failed, giving up.

Resolution:
In short, Snowflake Stored Procedures and User defined functions are not supported in TIBCO Spotfire.

Additional details:
In Snowflake, stored procedures returns a scalar value  and not a record set/table but the procedure element in the Information designer expects a procedure that returns a table

Example Stored Procedure (which fails in Spotfire):

CREATE or replace PROCEDURE proc3()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  rs = snowflake.execute( { sqlText: 
      `INSERT INTO table1 ("column 1") 
           SELECT 'value 1' AS "column 1" ;`
       } );
  return 'Done.';
  $$;

Snowflake supports user defined functions that can return a table but that uses a special keyword/function called 'table' to achieve that and Information Services in Spotfire is not designed to handle such functions.