Problem with Stored Proc as a DataSource

[Dmitry]

Hello.
Part one:
Thank you, I've modified my report a bit, now DB name is in connection string (SQL Connection object). Modified report is attached.

Part two.
As you said, I've created my custom "options.xml" file in folder "C:\Program Files (x86)\Stimulsoft Reports.Net 2014.1 Trial\Bin\"
Now, each time I run "Designer.exe" application, I have error:

access.jpg (59.29 KiB) Viewed 9528 times

Please note, I run "C:\Program Files (x86)\Stimulsoft Reports.Net 2014.1 Trial\Bin\Designer.exe" , not Demo application.
Demo application works fine.

Path three
My test data is a stored proc (SP) on MS SQL Server 2008 R2.
Here it is:

Code: Select all

CREATE PROCEDURE [dbo].[ReportHeaderInfo2]
    @SecurityToken bigint
as
begin
  select 'SomeName 1' as RootPointName, 'SomeName 2' as MainParameterName
end
GO

When I press "Preview" button in Stimulsoft's Designer application, preview works fine.
But the problem is, my real SP expects parameter "SecurityContext" to be null.
So, when I check some strange checkbox (marked red on the picture) and press "Submit" button, designer.exe is crashed.

crash3.png (108.09 KiB) Viewed 9528 times

Part four
I'm unable to press button "Retrieve Columns" in Datasource properties for attached report.
When I press this button, I have an error (please note, option "KeysOnly" is saved to "options.xml" file, as you suggested).

retrieve.png (52.18 KiB) Viewed 9528 times

[Alex K.]

Hello,

Part 2
Can you please clarify the options.xml was created as admin or standard user?

Part 3, Part 4
In this case you need create a stored procedure with optional parameters by specifying a default value for optional parameters.

Code: Select all

ALTER PROCEDURE [dbo].[ReportHeaderInfo2]
    @SecurityToken bigint = NULL
as
begin
  select 'SomeName 1' as RootPointName, 'SomeName 2' as MainParameterName
end

Thank you.

[Dmitry]

Part 2.
Of course, options.xml was saved to "C:\Program Files\..." as admin, because standard user has no write access to this directory.

Part 3,4
Thank you for a suggestion, at least I'm able now to call "Preview" function.
But I do believe crash while previewing is a bug in Stimulsoft's designer - null parameters shall not crash an application.

But "Retrieve columns" function in Stimulsoft's designer is not usable anyway.
Our reporting subsystem contains more than 100 Stored Procedures.
Our SQL programmers are not happy to re-write each procedure to add "default" value for every parameter.
I insist - Stimulsoft's designer must show "Enter parameters for stored proc" when user pressed a "Retrieve columns" button.

[Alex K.]

Hello,

But "Retrieve columns" function in Stimulsoft's designer is not usable anyway.
Our reporting subsystem contains more than 100 Stored Procedures.
Our SQL programmers are not happy to re-write each procedure to add "default" value for every parameter.
I insist - Stimulsoft's designer must show "Enter parameters for stored proc" when user pressed a "Retrieve columns" button.

In this case, you need set the default value for this parameter in the designer. If you try to run this procedure without set parameter's value in MSSQL manager you get the same exception.

Thank you.

[Dmitry]

So, my Stored Proc looks like:

Code: Select all

ALTER PROCEDURE [dbo].[FixNisReport]
    @ID_Task bigint
  , @DataType int = 1
as
begin
  select @ID_Task as TaskId, @DataType as TypeId
  union
  select @ID_Task + 1 as TaskId, @DataType +1 as TypeId
end
GO

My report file is attached. In this file, both parameters for stored proc are bound to Stimulsoft's variables.
Each variable has some default value.
When I try to "Retrive columns", I have an exception

incorrect.png (43.94 KiB) Viewed 9506 times

What can I do to "Retrieve columns"? It's impossible to make each parameter for 100 stored procs optional

[Alex K.]

Hello,

Issue with Options.xml is fixed. The solution will be available in the next prerelease build.

Retrieve columns. In your case, you set the option "KeysOnly". As a way you can select your procedure in database information window or try to set the full query for retrieve columns:

[Dmitry]

When I try to select my Stored Proc (SP) in Stimulsoft's designer, designer doesn't see SP's parameters.
It looks like this:

datasource.png (37.43 KiB) Viewed 9502 times

As you can see, only some SPs at the beginning have "plus" sign, all the others don't have one. But almost any SP in my DB has parameters.
I don't know why it is. Server is MS SQL 2008 R2.

Any way, I can write the query as you suggested. But it still doesn't work.
As I can see, you made a trick - in "Expression" for "@ID_Task" parameter you placed a constant, "1".
But this is not acceptable, from my point of view.
SP's parameters must be bound to variables, not constants. In my ReportWithError3, @ID_Task parameter is bound to ID_Task variable.
After all, user must provie SP's parameters when viewing report.
Do you suggest to rewrite queries for SP in .mrt files just to get columns list? It's annoying task.
When SP's parameters are bound to variables (as in my ReportWithError3), you'll get an error:

convert.png (39.46 KiB) Viewed 9502 times

[Alex K.]

Hello,

Setting the value "1" for parameters it is only for retrieve columns, after retrieve for using in report you can set for parameters any necessary value, in your case variables.

Thank you.

[Dmitry]

It seems to me, to "Retrieve Columns", one should:
1) Change SP's query from

Code: Select all

FixNisReport

to

Code: Select all

execute FixNisReport @ID_Task, @DataType

because latter form causes designer.exe to crash, when you pass null to any parameter.
2) Change "type" from "Stored Procedure" to "Table"
3) Assign some temporary values to each parameter for SP
After "Retrieve Columns", one should revert all those changes back.
It can be done, but it's a little bit tedious.
BTW, don't you plan to modify demo video http://www.stimulsoft.com/en/videos?tag ... e=3&id=130 to reflect this complication?

Could you please accept a request for improvement?
a. Designer.exe must read all the parameters for SP from MS SQL server, when user creates a Datasource. In my case, designer.exe does not support this function.
b. When SP's params are bound to variables, designer.exe shall ask user to provide values for those variables, when user pressed "Retrieve Columns" button. E.g. MS's ReportBuilder does it.

[Alex K.]

Hello,

Please set the
StiOptions.Engine.RetriveColumnsMode == StiRetrieveColumnsMode.SchemaOnly
in this case, all stored procedures(except which uses temporary tables) will be loaded automaticaly with columns and parameters.

If property set as
StiOptions.Engine.RetriveColumnsMode == StiRetrieveColumnsMode.KeyInfo
In this case, you can uses procedures with temporary tables (it is a ADO.NET limitations, not our), but all parameters must have a value for retrieve columns.

Thank you.