SQL Server stored procedures
SQL Server stored procedures
I have a dashboard with 3 Stored Procedures on it.
The first stored procedure is going to get a value passed to it via the handler.php file.
The other 2 stored procedures use the store number and date values from two variables but when I run the report the report gives an error saying that the stored procedure expects a parameter.
The first stored procedure is going to get a value passed to it via the handler.php file.
The other 2 stored procedures use the store number and date values from two variables but when I run the report the report gives an error saying that the stored procedure expects a parameter.
-
- Posts: 6636
- Joined: Tue Mar 20, 2018 5:34 am
Re: SQL Server stored procedures
Hello,
Please send us a sample report with test data that reproduces the issue for analysis.
Thank you.
Please send us a sample report with test data that reproduces the issue for analysis.
Thank you.
Re: SQL Server stored procedures
I had not setup a default for the values in the stored procedure. I now default to Null, however even though the report works in Preview when I run it thru the php files I get no error, but also don't get any data.
Alan
Alan
Re: SQL Server stored procedures
How can I send the test report? I can't send here as it contains proprietary data.
Re: SQL Server stored procedures
I have sent test to support. It looks to me as though no parameters are being sent when the stored procedures are being run.
Any idea what I'm doing wrong>
Alan
Any idea what I'm doing wrong>
Alan
Re: SQL Server stored procedures
Ok, here is what I know. Been trying to get this to work for the last 6 hours. I think I know what the issue is, just not sure how to fix it.
In the Designer, it works when I have the following setup.
I have sp_Report1 as the stored procedure that I am running. It has two parameters. They are named Parm1 and Parm2, and again in designer they work. I can see in the sql server profiler that the two parameters are being added when the stored procedure runs.
In the report, I get no data and I can see that the parameters are NOT being added to the execution line of the stored procedure when it's run.
In designer, I don't have anything after the sp_Report1, in other words, no parameters, the designer is adding those for me automatically when it runs.
I can't find any pattern of stuff that I can try that works in both designer and once deployed,
Alan
In the Designer, it works when I have the following setup.
I have sp_Report1 as the stored procedure that I am running. It has two parameters. They are named Parm1 and Parm2, and again in designer they work. I can see in the sql server profiler that the two parameters are being added when the stored procedure runs.
In the report, I get no data and I can see that the parameters are NOT being added to the execution line of the stored procedure when it's run.
In designer, I don't have anything after the sp_Report1, in other words, no parameters, the designer is adding those for me automatically when it runs.
I can't find any pattern of stuff that I can try that works in both designer and once deployed,
Alan
-
- Posts: 6636
- Joined: Tue Mar 20, 2018 5:34 am
Re: SQL Server stored procedures
Hello,
Please clarify what version are you use? Parameters in the JS version were added in last build.
In old versions, you can use variables in the query, like:
exec procname {variable1}, {Variable2}
Thank you.
Please clarify what version are you use? Parameters in the JS version were added in last build.
In old versions, you can use variables in the query, like:
exec procname {variable1}, {Variable2}
Thank you.
Re: SQL Server stored procedures
I am using 2022.4.3 and php.
When I remove the parameters and use exec sp_Report1 {parm1}, {parm2} and try to Preview I get an exception.
Alan
When I remove the parameters and use exec sp_Report1 {parm1}, {parm2} and try to Preview I get an exception.
Alan
Re: SQL Server stored procedures
I had to put quotes around the bracket variables. Might be making progress.. I'll get back to you soon.
Re: SQL Server stored procedures
With the quotes around the variables like '{parm1}' it works in the designer but not the php script. In the PHP script you doesn't replace the {parm1} with the value when it executes the stored procedure.
But if I leave the quote off, I get an exception in the designer.
But if I leave the quote off, I get an exception in the designer.