Spotfire Analyst: Cannot create a procedure element for a Snowflake stored procedure using Information Designer

Problem:

Cannot create a procedure element for a Snowflake stored procedure using Spotfire Information Designer.

Solution:

When creating a data source for Snowflake in 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.

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 this. Unfortunately, Information Services in Spotfire is not designed to handle such functions:

Snowflake Stored Procedures and User defined functions are not supported in Spotfire

Documentation reference: Overview of Procedures