Stored Procedure then display data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Stored Procedure then display data.

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-01-16 : 08:59:51

So the report I have been writing uses a stored procedure that has parameters that need filled first then runs and the results are placed in a temp table. Then call that data to be displayed after the stored procedure is finished. The issue I am having is when I put the info in and hit view report it does not let the store procedure finish and just displays what was pulled or was already in the temp database. Is their a way to put a wait or a do this first then run dataset 2 table?

1st datasource goes to our live database and uses the parameters to pull the data and then outputs that data to another Database.
the second Datasource connects to that Temp Database.

So Dataset1 is connected to Datasource1 and runs the parameters for the stored procedure.

Then Dataset2 is connected to Datasource2 and pulls that data that was gathered from the stored procedure in the Temp database to be displayed.

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-01-16 : 09:39:25

Or can I use dataset1 that runs.

ksa_PrintFuelholddeliveries (stored Procedure) then run this.<--------- This stored procedure runs in Service.dbo and as you can see the select runs in HBTEMP.dbo.

SELECT RecordId, CustNo, [Charge-Cust], [Unit-No], DeliveryStatus, Status, Heating, WaterHeating, RouteZone, Gallons, Usable, FillPercent, DelPrice, CreditLimit, ArBalance,
OpenOrders, Critical, DelMessage, BudgetSA, CASE WHEN BudgetSeq = 0 THEN '' ELSE CONVERT(varchar, BudgetSeq) END AS BudgetSeq,
CASE WHEN BudgetYear = 0 THEN '' ELSE CONVERT(varchar, BudgetYear) END AS BudgetYear, BudgetPrd,
CASE WHEN PaidAmount = 0 THEN '' ELSE CONVERT(varchar, PaidAmount) END AS PaidAmount, CASE WHEN BudgetStart = '' THEN '' ELSE CONVERT(varchar,
BudgetStart, 101) END AS BudgetStart, CASE WHEN BudgetEnd = '' THEN '' ELSE CONVERT(varchar, BudgetEnd, 101) END AS BudgetEnd
FROM HBTEMP.dbo.FO_FuelHold
ORDER BY RouteZone, FillPercent DESC

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-01-16 : 10:28:49

ok I figured it out. What needed to be done was create one DataSource that just held the Server name and each dataset you specify the the database.dbo. Then you check box the Use single transaction when processing the queries. In the Datasets you specify the database.dbo.store procedure. Then your query dataset you put in the database.dbo.table. Order does matter at that point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources