How to execute Snowflake Stored Procedure with multiple parameters via Microsoft SSRS Report Builder3 2012 using SSRS expression in single statement.

Question

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

1. 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)
2. 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?
3. 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

Answer 1

Hi @Anuj1 Singh ,

You are trying to use multiple statements in one time.
Can you use a single query?
This may be a Snowflake issue. In Snowflake, there seems to be no way to set the nocount option.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi

Thanks for your response . No it did not solve my query .

I am able to execute a query with multiple statements. However there is another issue .

Actually SQL SPs have been rebuilt on Snow Flake, I am working on a mechanism where Snowflake Stored Procedure(In Java script) can be called via the SSRS expression builder.

The call to the SP would like this:

CALL DatabaseName.SchemaName.StoredProcedureName(Par1,Par2,Par3,Par4...);
SELECT DatabaseName.SchemaName.TableName;

Reason of add SELECT statement after CALL : 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 .

Example :

="call ods_bi.csp_UserQuery(" + "'" + Parameters!I_NetworkId.Value + "');" + "SELECT I_UserId FROM ODS.ODS_BI.CSP_USERQUERY_"+Parameters!I_NetworkId.Value+";"

Issue : Although the expression being built by SSRS expression builder is correct , and sometimes query gets executed successfully , but we are not able to get result set from Snowflake side. While previewing report it keeps spinning and shows "Loading" message before Timeout message appears. As per my research , from snowflake side we get a single row after multi statement execution . The message is " Multi Statement executed successfully" .

Answer 3

Hi @Anuj Singh ，

It seems that you are using another account. The previous account is Anuj1Singh-9709.

Although the expression being built by SSRS expression builder is correct , and sometimes query gets executed successfully , but we are not able to get result set from Snowflake side.

According to your discription, this may be a Snowflake issue.
I find their support&Services from google: https://www.snowflake.com/support/
hope your problem can be solved. If there are other questions about SQL server later, please feel free to let us know.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

To execute a Snowflake stored procedure with multiple parameters via Microsoft SSRS Report Builder 3.0 using SSRS expressions in a single statement, you can follow these general steps:

1. Create a Data Source: Connect your SSRS report to the Snowflake database by creating a data source. This involves providing the necessary connection details such as the Snowflake account URL, username, password, and other relevant information.
2. Define Dataset: Create a dataset that will be used to execute the stored procedure. In the dataset query, you will construct the SQL statement to execute the stored procedure with parameters.
3. Construct the SQL Statement: You will need to construct a SQL statement that calls the Snowflake stored procedure with the provided parameters. You can use SSRS expressions to dynamically build the SQL statement. Here's an example of how the SQL statement might look:

sqlCopy code
EXECUTE PROCEDURE YourStoredProcedureName(:Parameter1, :Parameter2, :Parameter3);

In this example, YourStoredProcedureName is the name of your Snowflake stored procedure, and :Parameter1, :Parameter2, and :Parameter3 are placeholders for the parameter values.

Enroll for Snowflake Training. Online training by real-time experts.

Map Parameters: Map the parameters used in the SQL statement to SSRS parameters. Create the necessary SSRS parameters and associate them with the corresponding Snowflake stored procedure parameters. This can be done in the parameters section of the dataset properties.

Use Expressions: Now, you'll use SSRS expressions to dynamically construct the SQL statement based on the selected parameter values. You can use SSRS expressions in the dataset query text box or in the parameter default values, depending on your needs. Here's an example of how you might use expressions to construct the SQL statement:

sqlCopy code
= "EXECUTE PROCEDURE YourStoredProcedureName('" + Parameters!Parameter1.Value + "', '" + Parameters!Parameter2.Value + "', '" + Parameters!Parameter3.Value + "');"

Replace YourStoredProcedureName, Parameter1, Parameter2, and Parameter3 with your actual names.

Preview and Test: Once you've set up the dataset, parameters, and expressions, preview the report and test it by selecting various parameter values. Make sure that the constructed SQL statement is valid and properly executes the Snowflake stored procedure.

Please note that Snowflake's integration with SSRS might have evolved since my last training data in September 2021. Always refer to the latest documentation for Snowflake and SSRS to ensure you're using the most accurate and up-to-date information.