Stored Procedure from Redshift to Report builder

N, Rubini 0 Reputation points
2024-10-17T15:31:12.26+00:00

Am trying to call a stored procedure from AWS Redshift DB to Report builder. But not able to execute the stored procedure when i give the procedure name under stored procedure section in report builder.

Let me know how to achieve this.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,935 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 54,646 Reputation points
    2024-10-17T15:37:38.38+00:00

    We're going to need more information here.

    1. Did you create the sproc in your SQL database? If not then there is nothing to select in SSRS.
    2. Can you run the sproc in SQL directly? If not then it won't work in SSRS either.
    3. Does the sproc have any parameters that are complex (e.g. UDTs)? If so then you might not be able to call it in SSRS.
    4. In the designer are you connecting to the correct DB that has the sproc and did you restart the designer? It is likely the designer is caching the sprocs.
    5. Have you confirmed that the sproc is in the right database and that you have permissions to view and call it from the user account that you're connecting as in the designer?
    6. What version of SQL Server, SSRS and the Report Designer are you using?
    7. Do other sprocs show up?
  2. Michael Taylor 54,646 Reputation points
    2024-10-18T14:04:01.7866667+00:00

    SSRS can talk to anything that has a database driver including anything using ODBC. I know nothing about Redshift but a quick scan of their docs indicates that what they call a stored procedure isn't a database sproc as we know it. In order to call their "sproc" you have to use the CALL command.

    Behind the scenes tools like Report Designer rely on the commonly implemented support for getting schema information about a database including tables, columns and stored procedures. If a database provider doesn't implement this, or they don't implement it completely, then tools will be unable to display anything useful about them. That is what I believe is going on here. As such I don't think you're going to be able to use the built in support for sprocs in the designer. Replace your sproc call with the direct query to CALL sproc and ensure you are specifying the parameters. Then see if the call works. You won't get any help from the designer here because it has no schema to bounce off of. You're making an adhoc query. You can still use parameters (I believe) since they are independent of the sproc call but you'll need to pass them as part of your call directly.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.