Problem with Stored Proc as a DataSource

[Dmitry]

Our .NET application uses parametrized stored procedures (on MS SQL Server 2008 R2.) to create reports.
It seems to me, this approach is not well supported in StimulSoft.
As far as I can see here
http://www.stimulsoft.com/en/videos?tag ... e=3&id=130
We shall
1) Point out the name of stored proc to be used
2) Create manually all the parameters for stored proc
3) Provide correct types for each parameter
4) Provide initial values for each parameter
5) Press button "Retrieve columns"
Ignoring steps 3) and 4) leads to criptic exceptions on step 5), like this "Can not convert nvarchar to bigint" without any explanation - what user can do. This is because Stimulsoft tries to call your stored proc with "null" parameters, but it is not always possible

From my point of view, steps 2-4 are annoing - it seems to me, there are ways to automate those steps. At least Microsoft's ReportBuilder is able to retrieve stored proc's parameters automatically (with types) and bounds all stored proc's parameters to report's parameters (variables in Stimulsoft)

At the moment, I'm still unable to retrieve columns from such a stored procedure:

Code: Select all

ALTER PROCEDURE [dbo].[FixNisReport]
    @ID_Task bigint 
  , @DataType int = 1
as
begin
  if object_id( 'tempdb.dbo.#PointMeter' ) is not null
    drop table dbo.#PointMeter
  create table dbo.#PointMeter
    ( ID_Point       int not null primary key )

  insert into dbo.#PointMeter ( ID_Point ) VALUES (8)
  select @ID_Task as TaskId, @DataType as TypeId
  union
  select @ID_Task + 1 as TaskId, @DataType +1 as TypeId
end
GO

I have an exception,

retrieve.jpg (138.14 KiB) Viewed 18572 times

The reason is SQL query from Stimulsoft:

Code: Select all

exec sp_executesql N' SET FMTONLY OFF; SET FMTONLY ON;execute FixNisReport @ID_Task, @DataType',N'@ID_Task nvarchar(1),@DataType nvarchar(1)',@ID_Task=N'2',@DataType=N'1'

SQL Server answers with exception:
"Msg 208, Level 16, State 0, Procedure FixNisReport, Line 11
Invalid object name '#PointMeter'."
So, I filled the list of columns by hands - VERY annoyng task, my SP returns about 20 columns.
When I try to run a report, I have an exception "Could not find stored procedure 'execute FixNisReport @ID_Task, @DataType'"

execute1.jpg (57.63 KiB) Viewed 18572 times

The only way I've made report to show data, I changed "Query Text" to just name of stored proc: "FixNisReport" (without "execute" and parameters names). Shouldn't demo video be modified to reflect this fact?
In this case, reports works, but I'm still unable to retrieve column names automatically

[Alex K.]

Hello,

We need some additional time for check the issue.

Thank you.

[Dmitry]

From my point of view, better solution for this problem is
1. Extract stored procedure's parameters automatically
2. When user hits "Retrieve columns" button, Stimulsoft could show a dialog "enter stored proc parameters", then call SP directly, without "SET FMTONLY ON"

[Dmitry]

It seems to me, use case "Stored procedure as a Report Datasource" was not tested in Stimulsoft.
E.g., my stored proc (SP) has 3 parameters, each is bound to report's variable

parameter.jpg (78.15 KiB) Viewed 18550 times

variable.jpg (96.56 KiB) Viewed 18550 times

When I tried to preview a report, I've missed to provide a parameter and pressed a mistical unnamed checkbox next to variable name.
I suppose this check box means "pass null to SecurityToken report's variable", but this must be stated in a clear manner, I think.
This causes a fatal error in Stimulsoft's designer, with application crash (thank you for autosave option, at least)

Crash.jpg (128.05 KiB) Viewed 18550 times

I do believe, such a condition can not be a reason for application crash

[Dmitry]

Short version of message above: I'm unable to pass "null" to SP's parameter, designer crash with exception

[Alex K.]

Hello,

Can you please send us a sample report with test data which reproduce the issue for analysis.

About the stored procedure in which used the temporary table ('#PointMeter'), in this case you need use the following option:
StiOptions.Engine.RetriveColumnsMode == StiRetrieveColumnsMode.KeyInfo

Thank you.

[Dmitry]

Hello.
Attached report uses this stored proc (SP):

Code: Select all

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

In Stimulsoft's desiner, SP's parameter "@SecurityToken' is bound to report's variable named "SecurityToken".
When I preview report in desiner, I check an unnamed checkbox next to "SecurityToken" parameter on preview page.
Then press "Submit" button - this causes application crash.

Could you please explain, how do I init "StiOptions.Engine.RetriveColumnsMode" parameter, while I'm developing report in Designer?
I don't see Options property for the Report object.
Shall I create some event handler? Which event shall I handle?

Also please note, in report attached, I'm unable to "Retrieve columns" from SP.
Where is my error?
Exception is "Incorrect syntax near 'ReportHeaderInfo2'."

[Alex K.]

Hello

In your report in the connection string does not define a database name. Please try to set the full name of stored procedure:
[Northwind].[dbo].[ReportHeaderInfo2]

Could you please explain, how do I init "StiOptions.Engine.RetriveColumnsMode" parameter, while I'm developing report in Designer?
I don't see Options property for the Report object.
Shall I create some event handler? Which event shall I handle?

In this case you can use the OptionsHelper.exe utility, set the necessary option, save as Options.xml and put this file in the folder with designer.exe

Thank you.

[Dmitry]

Part one:
Your suggesion to give full stored proc name in connection string is not clear.
1) connection string is in "SQL Connection" object, and it has no knowledge about stored proc.
2) Our database server has a lot of databases, e.g.: production one, testing one, staging one. So, database name is a parameter for an application. It would be very bad to change all the stored procs definitions in each .mrt file while deploying our application.
3) I've changed my .mrt file a bit, and I found side effect: It seems to me, datasource name must be equal to stored proc name. In this case, I can use "Retrieve columns" button. When Datasource's name (field "Name" in your screenshot) differs from stored proc name, "Retrieve columns" button does not work.
Part one question: Taking this into account, could you please refine, is it a MUST to include database name? in connection string? or in stored proc name?

Part two:
I've saved options.xml to folder with desiner.exe. Now each time designer.exe is started, I see this error:

access.jpg (59.29 KiB) Viewed 18538 times

When I run designer.exe as Administrator, error is not shown.
But I do believe desiner.exe must be run as standard user, doesn't it?
When I run designer.exe as Admin, "Retrieve columns" button does not work - same exception here:
"Incorrect syntax near 'ES_COMMIT_7_0'."
Part two question: how do I use "Retrieve columns" button?

Part three
How do I pass "null" to SP's @SecurityContext parameter?
With options.xml modfied, desiner still crashes.

crash2.jpg (187.18 KiB) Viewed 18538 times

Please note, @SecurityContext is a parameter, it is not always null

[Alex K.]

Hello,

Part one:
You can specify the database name in connection string or in full procedure name, it your case.

Part two:
Can you please clarify, our Demo application is working if run as standard user.

Part three:
Can you please send us a test data for your report.

Thank you.