Background : We have SSRS reports, which we used to run using T-SQL stored procedure from SSRS against SQL server, As per the new requirement, we now will be using Snowflake Stored Procedure (In Java script) replacing T-SQL stored procedure.
Problem Statement : Snowflake Stored Procs (In Java script) do not allow a tabular result set to be returned, only return scalars. Hence in order to execute the Snowflake Stored Procedure and get a result set in SSRS we need to add a SELECT statement after execution of the SP , we need to use below two statements .
EXAMPLE
CALL DatabaseName.SchemaName.StoredProcedureName(Par1,Par2,Par3,Par4...);
SELECT DatabaseName.SchemaName.TableName;
I have tried building an expression like above ,but unfortunately while preview report it fails with below error :
Error : Actual statement count 2 did not match the desired statement count 1.
Is there a way where we can execute two statements in SSRS
Questions
- Has anyone used Snowflake Stored Procedure(In Java script) with multiple parameters as data-source in Microsoft SQL SSRS report? (This is different from regular multi parameter , which we pass to SQL Server)
- How can we pass a parameter/variable to Snowflake Stored Procedure(In Java script) from SSRS, since SSRS will not identify parameters required by Snowflake (when we use refresh data field option) unlike when we use T-SQL Stored Procs?
- Snowflake Stored Procs (In Java script) don't allow a tabular result set to be returned, only return scalars. Hence in order to execute the Snowflake Stored Procedure and get a result set we need to add
a SELECT statement after execution of SP , we need to use below two statements .
CALL DatabaseName.SchemaName.StoredProcedureName(Par1,Par2,Par3,Par4...);
SELECT DatabaseName.SchemaName.TableName;
Is there a way where we can execute two statements in SSRS , Query Type- Text. For Example as below
I have tried building an expression like above ,but unfortunately while preview report it fails with below error :
Error : Actual statement count 2 did not match the desired statement count 1.
I have tried adding the following code, however I am still getting the same error.
alter session set MULTI_STATEMENT_COUNT=0;
Is there a way to run a query with multiple statements ?
As per our requirement we want to run the Snowflake Stored Procedure (In Java script) with multiple parameters in SSRS against Snowflake DB data source.
Concerns: Even if we convert all our old stored procedure(T-SQL) to snowflake stored procedure (in java script), still it cannot return a tabular result set. Using Stored procedure as a data source for a SSRS is not supported by snowflake.
I have referred below link --A guide to migrating SSRS source to Snowflake
However here they are only passing a single SELECT statement but I need to pass two statements as I have already mentioned .
Link : https://servian.dev/a-guide-to-migrate-ssrs-source-to-snowflake-e51b62165c5a
Any guidance would be really appreciated
Thanks and Regards
Anuj Singh