logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error
Working with Stored procedure - How to add dataset from stored procedure?
Options
Go to last post Go to first unread
Previous Topic Next Topic
nithin  
#1 Posted : Friday, April 12, 2019 12:10:38 PM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
I've connected to MS SQL database and need to create a data set by executing a stored procedure. Couldn't find a beginner friendly solution for this. Any help on how to do this is greatly appreciated.

thank you.
Back to top

Wanna join the discussion?! Login to your Seal Report Forum forum accountor Register a new forum account.
Back to top  
epf  
#2 Posted : Friday, April 12, 2019 4:28:01 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Did you check the sample 21-SQL Server Stored Procedure with parameters.srex,
it selects a MSSQL sp_who with a parameter...
Back to top
nithin  
#3 Posted : Tuesday, April 16, 2019 4:55:16 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Thank you for the response. I've tried executing the sample procedure but it fails.
Back to top
epf  
#4 Posted : Tuesday, April 16, 2019 5:35:06 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Did you configure your SQL Server first ?

This report demontrates the use of the result set returned by a stored procedure in a local SQL Server.
Note that to use OPENROWSET to define your table, you must configure your SQL Server with the following commands:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
Back to top
thanks 1 user thanked epf for this useful post.
nithin on 4/16/2019(UTC)
nithin  
#5 Posted : Tuesday, April 16, 2019 7:18:27 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Yes. SQL Server is configured properly and everything is fine. tried filling a table with select query and it works. Problem is with procedure. I'm unaware of how to use procedure to fill table. I found this - https://sealreport.org/f...?g=posts&t=15#post29 and tried the last method with pre sql and post sql, but its not working.

it would be a great help if you provide the steps to follow for executing procedure.
what i've done so far is:

--> configured sql server and database and checked the connection
--> created a new table under data source
--> pre sql statement of the table is :

CREATE TABLE #tmpCatalogTable
(
col1 datatype,
col2 datatype
)
INSERT into #tmpCatalogTable
EXECUTE procedurename 'servername'

--> post sql statement is :

drop table #tmpCatalogTable


do i need to add any sql statement also?

thank you.

Edited by user Tuesday, April 16, 2019 11:39:06 AM(UTC)  | Reason: Not specified
Back to top
nithin  
#6 Posted : Tuesday, April 16, 2019 12:19:48 PM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Originally Posted by: nithin Go to Quoted Post
Yes. SQL Server is configured properly and everything is fine. tried filling a table with select query and it works. Problem is with procedure. I'm unaware of how to use procedure to fill table. I found this - https://sealreport.org/f...?g=posts&t=15#post29 and tried the last method with pre sql and post sql, but its not working.

it would be a great help if you provide the steps to follow for executing procedure.
what i've done so far is:

--> configured sql server and database and checked the connection
--> created a new table under data source
--> pre sql statement of the table is :

CREATE TABLE #tmpCatalogTable
(
col1 datatype,
col2 datatype
)
INSERT into #tmpCatalogTable
EXECUTE procedurename 'servername'

--> post sql statement is :

drop table #tmpCatalogTable


do i need to add any sql statement also?

thank you.


I've added SELECT from #tmpCatalogTable statement and it worked. Table is filled and report executed.
Now, i'm trying to handle parameters.. how do i pass parameters to the procedure?

Back to top
epf  
#7 Posted : Wednesday, April 17, 2019 5:48:37 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
You can inject your restrictions/parameters in the Pre SQL using C# through razor script.

Pre/Post SQL can be like:

Code:

@using Seal.Model
@{
    ReportModel model = Model;
    List<ReportRestriction> restrictions = model.Restrictions;
    string result = string.Format("exec sp_procedure '{0}'", model.GetRestrictionByName("RestrictionName").Value1);
}
@Raw(result)


where the SQL is built using the value of a restriction of the model.

Edited by user Wednesday, April 17, 2019 5:49:20 AM(UTC)  | Reason: Not specified
Back to top
thanks 1 user thanked epf for this useful post.
nithin on 4/18/2019(UTC)
nithin  
#8 Posted : Thursday, April 18, 2019 9:18:48 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Originally Posted by: epf Go to Quoted Post
You can inject your restrictions/parameters in the Pre SQL using C# through razor script.

Pre/Post SQL can be like:

Code:

@using Seal.Model
@{
    ReportModel model = Model;
    List<ReportRestriction> restrictions = model.Restrictions;
    string result = string.Format("exec sp_procedure '{0}'", model.GetRestrictionByName("RestrictionName").Value1);
}
@Raw(result)


where the SQL is built using the value of a restriction of the model.



Is it possible to execute a procedure with parameters from the report designer itself so that the data source can be populated with the procedure output and then this table can be used to create the model..?
Back to top
epf  
#9 Posted : Sunday, April 21, 2019 8:20:09 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Yes, that is exactly what we are talking about:
The restriction value prompted at execution are used to execute your SP.
Back to top
thanks 1 user thanked epf for this useful post.
nithin on 4/22/2019(UTC)
nithin  
#10 Posted : Monday, April 22, 2019 6:02:21 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
I'm sorry, i need bit more clarification. How do i add restrictions without having a data table initially..? I guess the razor script you mentioned above will work only if the restrictions are already defined, so that they can be referred by their name.

I've created a procedure without any parameters and filled the data table by executing this by creating temporary table in the pre sql statement and then added restrictions to the model by dragging and dropping restriction elements from this data table. Then i was able to add filters and successfully return the result applying the filters/restrictions provided at run time.
Back to top
nithin  
#11 Posted : Monday, April 22, 2019 7:47:13 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Originally Posted by: nithin Go to Quoted Post
I'm sorry, i need bit more clarification. How do i add restrictions without having a data table initially..? I guess the razor script you mentioned above will work only if the restrictions are already defined, so that they can be referred by their name.

I've created a procedure without any parameters and filled the data table by executing this by creating temporary table in the pre sql statement and then added restrictions to the model by dragging and dropping restriction elements from this data table. Then i was able to add filters and successfully return the result applying the filters/restrictions provided at run time.


This is the pre sql statement of my data table :

CREATE TABLE #tmpCatalogTable
(
CustomerID bigint,
CustomerName nvarchar(100),
City nvarchar(100),
InvoiceNumber nvarchar(100),
InvoiceDate date,
TotalAmount float,
PackingCharges float
)
INSERT into #tmpCatalogTable
EXECUTE proc_OrderDetailsWithParameters 'server_name'

And the SQL Statement of the data table be like 'SELECT * From #tmpCatalogTable'

city is a parameter and how can I pass the value of city chosen by the user from the UI while running the report? This data table should be the source for my model.

I referred sample 21-SQL Server Stored Procedure with parameters and tried following that. Pre load script of my model is like:

Code:
@using Seal.Model
@using System.Data
@{
    ReportModel model = Model;
    ReportExecutionLog log = model.Report;
    List<ReportElement> elements = model.Elements;
    List<ReportRestriction> restrictions = model.Restrictions;
	var report = model.Report;
	
	string pattern = "EXEC proc_OrderDetailsWithParameters";
	
	var table = report.Sources[0].MetaData.Tables[0];
	
	if (model.Tag == null) {
		model.Tag = table.Name;
	}
	else {
		table.Name = (string) model.Tag;
	}
	
    table.Name =  table.Name.Replace(pattern, pattern + string.Format(" ''{0}''", model.GetRestrictionByName("City").Value1));
	log.LogMessage("New name=" + table.Name);
}


This is not working. Table is not loaded. The error message is :
Error in model 'model2': Error when executing 'Pre Load Script'.
Object reference not set to an instance of an object.

and also value1 contains the pre set value. how to use value chosen by the user from the dropdown list instead?

Edited by user Tuesday, April 23, 2019 8:38:56 AM(UTC)  | Reason: Not specified
Back to top
epf  
#12 Posted : Tuesday, April 23, 2019 8:50:04 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Difficult to debug this through a forum...
The Error may come from the Model.GetRestrictionByName("City") -> are you sure you have a restriction named City in your model ?
You have to debug this in your environment...
Back to top
thanks 1 user thanked epf for this useful post.
nithin on 4/23/2019(UTC)
nithin  
#13 Posted : Tuesday, April 23, 2019 9:30:26 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Originally Posted by: epf Go to Quoted Post
Difficult to debug this through a forum...
The Error may come from the Model.GetRestrictionByName("City") -> are you sure you have a restriction named City in your model ?
You have to debug this in your environment...


Yes, I've added the restriction. But i think that doesn't make any sense. I wrote that restriction manually in the restriction field because table rows are not loaded and hence can not drag and drop it.

In sample 21 there is no sql statement and no pre/post sql statements. I have no idea how the table is loaded there.

The issue can be solved if the data table could be filled somehow, i think.

Edited by user Tuesday, April 23, 2019 9:31:31 AM(UTC)  | Reason: Not specified
Back to top
nithin  
#14 Posted : Tuesday, April 23, 2019 10:48:06 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
I've tried this:

Pre sql statement of DataTable is like:
Code:
CREATE TABLE #tempTable
(
   CustomerID bigint,
   CustomerName nvarchar(500),
   City nvarchar(100),
   InvoiceNumber nvarchar(100),
   InvoiceDate date,
   TotalAmount float,
   PackingCharges float
)
INSERT into #tempTable
exec proc_OrderDetailsWithParameters
@city = 'CityName'


Sql statement is - SELECT * FROM #tempTable

This works fine. So, is there any way the value of parameter can be stored on a variable and use that variable in the pre sql statement, like

@city = model.GetRestrictionByName(...)

or something like that?
When i use @using Seal.Model in the Pre sql statement, the following error is shown :

Errors while compiling a Template.
Please try the following to solve the situation:
* If the problem is about missing/invalid references or multiple defines either try to load
the missing references manually (in the compiling appdomain!) or
Specify your references manually by providing your own IReferenceResolver implementation.
See https://antaris.github.i...e/ReferenceResolver.html for details.
Currently all references have to be available as files!
* If you get 'class' does not contain a definition for 'member':
try another modelType (for example 'null' to make the model dynamic).
NOTE: You CANNOT use typeof(dynamic) to make the model dynamic!
Or try to use static instead of anonymous/dynamic types.
More details about the error:
- warning: (0, 0) The predefined type 'System.Collections.Generic.IReadOnlyList' is defined in multiple assemblies in the global alias; using definition from 'c:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll'
- warning: (0, 0) The predefined type 'System.Collections.Generic.IReadOnlyCollection' is defined in multiple assemblies in the global alias; using definition from 'c:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll'
- error: (40, 7) The name 'city' does not exist in the current context


I have also tried putting the pre load script of the model, which is quoted in post #11, into the pre sql statement of the table. Then the error is :


- warning: (0, 0) The predefined type 'System.Collections.Generic.IReadOnlyList' is defined in multiple assemblies in the global alias; using definition from 'c:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll'
- warning: (0, 0) The predefined type 'System.Collections.Generic.IReadOnlyCollection' is defined in multiple assemblies in the global alias; using definition from 'c:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll'
- error: (27, 26) Cannot implicitly convert type 'Seal.Model.MetaTable' to 'Seal.Model.ReportModel'

Edited by user Wednesday, April 24, 2019 5:09:54 AM(UTC)  | Reason: Not specified
Back to top
epf  
#15 Posted : Wednesday, April 24, 2019 6:01:13 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
In the 21 samples, the work is done in the Pre Load Script of the model, this change the SQL of the table with the parameter value.

Your approach to use PreSQL of table may work but you cannot reference User restrictions at this level, however you can check the workaround described in this post:
https://sealreport.org/f...t.aspx?g=posts&t=278

This will be simpler with the incoming 5.0 that will have the Common Restriction feature.
Use the {CommonRestriction_AName} in your SQL to get and configure the restriction named 'AName' in your model (there is a new property 'Common Restrictions').


You can download a beta build at https://ariacom.com/download/sealsetup5.0.exe or sealsetup5.0_x64.exe

Edited by user Wednesday, April 24, 2019 6:03:52 AM(UTC)  | Reason: Not specified
Back to top
thanks 1 user thanked epf for this useful post.
nithin on 4/24/2019(UTC)
nithin  
#16 Posted : Wednesday, April 24, 2019 12:32:17 PM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Hi epf. Thank you for the support!

I've downloaded version 5 and checked the common restrictions property and it works! But the custom enum list for restrictions, which used to work in the previous version is not working now. The list is getting loaded but nothing is showing up in the drop down. I've added the select query in the 'select sql statement field' and 'Select SQL for prompted restriction'. neither of them works. What might be the issue here...?

Also i have a query - Is there any new workaround available in Version 5 for executing procedures in a more handy way other than using temporary table or OPENROWSET...??

Back to top
epf  
#17 Posted : Wednesday, April 24, 2019 3:32:55 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Perhaps it is a regression as it is a beta version.
Yes, CommonRestriction allows you to inject values prompted to the end user at different level (table, pre/post SQL, etc...)

Can you explain what is wrong with the enum list ? It has been reworked as well to allow dynamic/dependant enums...
A sample with Northwind would be great...
Back to top
epf  
#18 Posted : Wednesday, April 24, 2019 9:08:15 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
Finally, I made a report with the 5.0 that shows how to query a SQL Server SP in 3 different ways:

Test 1 is using OPENROWSET: a table is defined with the name:
O
Code:
PENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC sp_testSR '{CommonRestriction_Label1}'')


Test 2 is using SQL Model with a raw call of
Code:
exec dbo.sp_testSR {CommonRestriction_Label2}

Note the property 'Use Raw SQL' must be set to true.

Test 3 is using a #Temp Table with PRE and POST SQL.
Code:
CREATE TABLE #testSR
(
   id int,
   label nvarchar(50)
)
INSERT into #testSR
exec sp_testSR {CommonRestriction_Label3}

DROP TABLE #testSR


For the 3 models, the common restriction is set to ValueOnly.

Note that the report include Tasks that create the SP.
You have to download the last build at https://ariacom.com/download/sealsetup5.0.exe or sealsetup5.0_x64.exe

In attachment is the report Test SQL Server Stored Procedure.srex (17kb) downloaded 13 time(s).
I let you try...

Edited by user Wednesday, April 24, 2019 9:11:11 PM(UTC)  | Reason: Not specified
Back to top
nithin  
#19 Posted : Thursday, April 25, 2019 5:41:36 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
Hi epf. Thank you for your great support!

I've downloaded the test report and it seems the Raw SQL Model approach is more handy for my requirement. Successfully executed a report with a procedure having 2 parameters, following this approach.
But when i tried to execute a bit complex procedure having more number of parameters (14, to be precise) it fails. Dataset is not being generated. Seems the query is not getting executed. No error messages are shown. The procedure is working fine in Sql Server, there is no issue with the procedure.

the Sql statement in the sql model is like :

exec procedurename
@parameter1 = value,
@parameter2 = value,
.
.
.
.


Use Raw source sql is set to true.

Edited by user Friday, April 26, 2019 12:18:31 PM(UTC)  | Reason: Not specified
Back to top
nithin  
#20 Posted : Thursday, April 25, 2019 7:17:19 AM(UTC)
nithin

Rank: Member

Groups: Registered
Joined: 4/12/2019(UTC)
Posts: 14
India
Location: Kerala

Thanks: 6 times
The issue with enum list was that when i chose custom enum list to prompt for a restriction, nothing was showing up in the drop down list of the restriction. I created the enum list in the data source and when i refreshed the list by pressing F9, success message was shown but during execution of the report, the drop down list was blank.
Somehow this issue is solved now, in the latest build.

Now the issue is the value i chose from the drop down is not passed to the procedure.

Im sorry, i can't use northwind because it shows error - "microsoft.jet.oledb.4.0 not regestered in the machine".

This is the pre sql statement of my table :

Code:
CREATE TABLE #tmpCatalogTable
(
   CustomerID bigint,
   CustomerName nvarchar(100),
   City nvarchar(100),
   InvoiceNumber nvarchar(100),
   TotalAmount float
)
INSERT into #tmpCatalogTable
EXECUTE proc_OrderDetailsWithParameters @city = {CommonRestriction_City}


In the model, operator of common restriction City is set to 'value only' and the enumerated list is selected.

This is working fine when enum is not used and the restriction value is typed during execution.
Back to top
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF.NET 2.2.4.11 | YAF.NET © 2003-2025, Yet Another Forum.NET
This page was generated in 0.322 seconds.