Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

How to initiate an UPDATE to a table for SSRS and an ASP.Net web application

Posted on 2015-01-14
4
Medium Priority
?
142 Views
Last Modified: 2015-01-20
I'm sure this is a simple problem.  The explaining it will be the hardest part.  ;-)  

#1.  On our SQL Server is a database (Gazelle) used by a 3rd party software that we use for customer tracking.  We can not make any changes to the structure of that database.  We can't add stored procedures or triggers or new tables or fields to existing tables.  We can't touch Gazelle.

#2.  We have a Youth Employment Program that consists of a set of records from the Gazelle database.  We created a SQL View to pull in that set of records.

#3.  We need to enter dates to track milestone completions.  For that, I've created a Table in the same database as the View that's pulling data from the Gazelle database.  Those Table and View are in our own in-house database so we can modify it as needed.  I've added new fields for those milestones and fields for completion dates.  Staff will enter completion dates as necessary in a ASP.Net Web Application (VB.Net).

Okay.  The View, of course, stays up to date with changes to the Gazelle database.  But, I've got the Table for entering the milestone dates which needs to be "refreshed" or updated periodically from the View so it has the same set of records.

I think for the web application, I should be able to just put this MERGE query into the Page_Load or a "Refresh" button or both.  
MERGE [TempTables].[dbo].[WIAYouthITADates] da
USING [TempTables].[dbo].[vw_WIAYouthITA] vw
ON da.PID = vw.ParticipantId

WHEN MATCHED AND
  da.ActualEndDate IS NOT NULL THEN
  DELETE
  
WHEN MATCHED THEN
  UPDATE
  SET 
	da.Client = vw.client,
	da.SSN = vw.last4ssn,
	da.ProgramStart = vw.ProgramStart,
	da.AnticipatedEndDate = vw.AnticipatedEndDate,
	da.ActualEndDate = vw.ActualEndDate

WHEN NOT MATCHED BY TARGET THEN
  INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)
  VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate);
   
 
SELECT * 
--DELETE
FROM [TempTables].[dbo].[WIAYouthITADates]

Open in new window


Sorry for the long background information...
Here's my Question...  

I also have an SSRS Report to show the status of the milestone dates that have been entered into my in-house table.  

How can I update the table for the SSRS user(s) if the ASP.Net application hasn't been accessed in a while to update the table?

Thanks.

TLDR:  How do you update a Table from a View in SSRS?  SSRS displays the Table.  The table needs to be "refreshed" or updated from an up to date View.
0
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 40550798
I'm not sure I get what you're asking.  If the view returns the up-to-date information and the SSRS dataset uses the view then the report should also display the up-to-date info.

Are you asking how you can have SSRS execute an update (or merge) statement so that the data gets updated before it's returned to the report user?  In that case I believe it should be possible by adding the update statement to your dataset that's returning the data for the report, basically goes like this:

<UPDATE/MERGE statement>

<SELECT statement>

The SSRS engine will execute the update before the select and should thus return updated information.
0
 
LVL 1

Author Comment

by:megnin
ID: 40551413
Your second assumption is correct.  SSRS is using my custom Table, not the View as the data source, so I need to do the UPDATE/MERGE statement.

I wasn't sure you could include an UPDATE in the SSRS dataset.  That will make it much easier.

Thank you!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40553340
Another option is to move all the T-SQL code to a stored procedure and then call the stored proc from the dataset, could be more interesting in terms of maintenance :)
0
 
LVL 1

Author Comment

by:megnin
ID: 40559771
Oh, yes.  Thank you for that suggestion.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question