Hello
My team is currently investigating using Telerik Report Designer, both the web version and the standalone for reports planning. Right now I'm trying to get the report designer to work with stored procedures and it's not working at all.
First, I cannot get a query to run with an EXEC command, and secondly, the drop down that's supposed to have all the stored procedures on the database won't populate.
The connection string is good, I can make basic select queries work. But I cannot get stored procedures to work.
If I do "EXEC [schema].[storedprocedurename]" as a query, I get "Could Not Find stoed procedure 'schema/storedprocedurename'" though it is a valid stored procedure that I can run in SQL server no problem.
So I can neither access stored procedures from the drop down, or work around this by writing it in a query. I have this problem with both the standalone and the web designer. We're not doing anything weird in our database either, it's a basic SQL Server database with stored procedures stored in the Programmability/Stored Procedures folder.
Am I doing something wrong? Is there some configuration that needs to be set on my database for the report designer to recognize the stored procedures?
Thank you
Jonathan Kuhl
2 Answers, 1 is accepted
Rank 3
Iron
Iron
Iron
I have a basic MSSQL Server setup with installed AdventureWorks DB. I can access the SPs. I looks like a very strange problem. In the past I've had a couple of cases when I couldn't get the list of stored procedures in the drop down. The reason was that I was not granted the proper permissions for viewing and executing the procedures. Here are a few things I would try for troubleshooting the problem:
- Install the AdventureWorks DB (publicly available) and check if the SPs are accessible there
- Check if the user within the DB client and the connection string within Telerik Reporting are the same. If not - this could be a permissions problem.
- Recreate the DB Source and build the connection string again
I'm just trying to help with some ideas.
Hello Jonathan,
Thank you for the provided information about the issue!
I agree with Krasimir's conclusion that the reason for the stored procedures to not populate the dropdown or execute with the EXEC command may be due to the user not having permission to access the stored procedures.
For an example of how to check what users can execute which stored procedures, you may have a look at the sql server - Finding stored procedures having execute permission - Stack Overflow thread.
If it is confirmed that the user does not have permission to execute the stored procedure, you may have a look at the following links which contain examples of grating execute permissions to users:
- Grant Permissions on a Stored Procedure - SQL Server | Microsoft Learn
- sql - GRANT EXECUTE to all stored procedures - Stack Overflow
- sql server - Grant execute on all stored procedures using SSMS - Stack Overflow
If that does not help and you need further assistance, please attach a trace listener to the Standalone Report Designer - Standalone Report Designer Problems Troubleshooting - Telerik Reporting, then try using the EXEC keyword to execute a stored procedure. This should cause an exception and it will be written to the trace log which you may use to see the full stack trace and diagnose the root of the issue or attach the log here so that we can also inspect it and give advice.
I hope that the provided suggestions will help resolve the issue.
Regards,
Dimitar
Progress Telerik
Hello Jonathan,
When setting up the DB source, you need to check the "Stored Procedure" radio button. Then, you should be able to select the stored procedures. You would not be allowed to execute the procedure in the "Select Statement" box.
Moving forward, you'll be asked to provide any parameter values, etc.
Would this work for you?
Regards,
Krasimir Baylov
Yes of course I'm clicking the radio button, the drop down is grey otherwise.
The drop down does not populate.