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

Notification

Icon
Error

Options
Go to last post Go to first unread
nithin  
#1 Posted : Friday, April 12, 2019 12:10:38 PM(UTC)
nithin

Rank: Newbie

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

Thanks: 3 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.
epf  
#2 Posted : Friday, April 12, 2019 4:28:01 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 622
Switzerland

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

Rank: Newbie

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

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

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 622
Switzerland

Thanks: 9 times
Was thanked: 98 time(s) in 95 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;
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: Newbie

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

Thanks: 3 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

nithin  
#6 Posted : Tuesday, April 16, 2019 12:19:48 PM(UTC)
nithin

Rank: Newbie

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

Thanks: 3 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?

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

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 622
Switzerland

Thanks: 9 times
Was thanked: 98 time(s) in 95 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

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: Newbie

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

Thanks: 3 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..?

epf  
#9 Posted : Sunday, April 21, 2019 8:20:09 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 622
Switzerland

Thanks: 9 times
Was thanked: 98 time(s) in 95 post(s)
Yes, that is exactly what we are talking about:
The restriction value prompted at execution are used to execute your SP.
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: Newbie

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

Thanks: 3 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.
Users browsing this topic
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.