Is a Job an Acceptible Solution? 1

[JeremyNYC]

I'm an experienced Access developer working on my first SQL Server back-ended project (Access adp front end). It's a large, open-source (well, except for Access and SQL Server <g>) database for non-profits. There are some parts of the application that we expect people will play with, and some parts that we expect they won't. Parts in that latter category we don't want to mess with too much.

One of those don't-mess-with parts is a form that's bound a table. Well I've just run into a situation that is just begging to have a calculated field displayed in that form. The field would reflect the Contact's current Membership status. It would need to be accurate within a day, not in real time. So my thinking is to create a job to update the status field based on some other fields, and run that every night.

Simple enough to do. But is this kind of thing normally done? Is this an acceptible practice?

Thanks much.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org

 

[ProdAir]

you said it's a calculated field so what determines the status?

 

[ProdAir]

oh, one other thing. Jobs, typically for this event-driven day and age, is typically not the ideal since that's better suited to mainframe apps. It adds another variable to the equation (what if the job fails for one reason or another)...

 

[SQLSister]

Why not just make it a calculated field?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[EdwinGene]

Is there some reason the calculation result needs to be stored and held in the database? If not, it would probably be more efficient just to calculate and display it right in the Access form. Even though it needs only to be accurate within a day, I can't imagine anyone being upset if it reflects real-time status.

 

[vongrunt]

Without extra info about scheduled job, I'd also go real-time. Membership status typically changes explicitely with last historical entry, and time dependency (e.g. expiration date) is easy to put in a calculated field or something.

 

[JeremyNYC]

Sorry. Clearly I didn't include enough details. The reason it can't be calculated on the fly is that the database is very customizable, and one of the things that gets customized is where this bit of information shows up--there are a bunch of controls in which someone might decide to display this information.

The interface in question lets the admin pick which fields from which tables to display. Currently you can't display fields from a view or stored procedure. Is there a way to store a calculated field in a table (man, it hurts to type that)? That would be simplest.

Barring that, I was thinking to use a Job to update the values on a nightly basis.

I'm quite well versed on normalization, and I know this is breaking normalization. We just have not found a way to do this in real time, and I don't think we will, short of a major rehaul of this chunk of the application, which we just don't have time to do at this point.

The calculation is relatively simple, and relatively irrelevant to the decision of whether or not to do this. But the risk of the job not running is the kind of thing I'm looking to get more information about. How reliable are jobs--clearly someone can turn off the server or there can be some collision that keeps the job from running. But what I'm looking for is whether or not SQL Server jobs are known as something to be avoided. Or whether or not people's allegiance to normalization keeps them from doing such things (I know I have a lot of resistance to this idea from that perspective).

Thanks for all the input.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org

 

[EdwinGene]

You could create a view which returns the calculated value.

 

[SQLSister]

No need for a view, you can put calculated fields in tables. Depending on the complexity of the calculation you can either directly calculate it or use a trigger to come up with the value and insert it into the field. A simple create table script with a formula follows:

CREATE TABLE [dbo].[Table3] (
	[method_id] [int] NULL ,
	[test] AS ([method_id] * 2) 
) ON [PRIMARY]
GO

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[JeremyNYC]

SQLSister,

Hmm. I just looked through BOL for this, as I thought that's what you were hinting at, but I couldn't find it. Can you point me to the right search terms or pages so I can read up on it? And, can I refer to a function in one of these calculations?

Much thanks.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org

 

[JeremyNYC]

SQLSister,

Ahhhhhhhh. You've just made things SO much easier for me. I just went and played around with this some, and this will do exactly what I want it to do. Thank you so much.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

http://metrix.fcny.org

 

[SQLSister]

I see you figured it out, but for anyone else searching for info on this subject, computed columns would be the search term to look for in BOL.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief