[AccessD] Stored Procedure not producing results

artful at rogers.com artful at rogers.com
Wed Nov 22 06:14:50 CST 2006


Right on. That's why I suggested the default GetDate(). 

There is a whole other subject on this, about which I have written, but I googled it and it didn't come up, so perhaps I wrote it and forgot to sell it to somebody.  The gist is this: it's called PITA, which doesn't mean pain in the arse, but rather Point In Time Architecture. Without PITA, the central problem with relational databases is that they don't provide an instant "roll back to August 1" capability. With PITA, they do. It's not all that complicated, but it does require a detailed walk-through so you can understand all the implications, the most critical of which is, "Nothing is ever updated. An updated row is actually replaced, and the updated row's EndDate column is updated to reflect the datetime on which the row was "changed". Thus it becomes possible to issue a query that reflects the state of the database on August 1, 2005. Obviously this increases the size of the db significantly, but in certain environments (such as medical), this is critical -- who was
 JWC's physician on that date, and what tests were performed, and by which medicos, and so on.

So. Today's job is to dig out that PITA article and pitch it to somebody. 

Arthur

----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 6:16:35 AM
Subject: Re: [AccessD] Stored Procedure not producing results

>This assumes of course that the scope is expandable so easily. (A good
reason to have a "DateEntered" column in every table, which defaults to
GetDate()). 

Amen!  I do that regularly now.  It juts makes managing data so much easier
when you can see when it was entered.  I actually use the date + time so
that I can see things like how long an append query takes to run (time of
last entry in the "batch minus time of first entry in the "batch").


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Wednesday, November 22, 2006 6:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure not producing results

If the structures haven't changed, then the data is the villain. But you
have a concrete clue to work from. Devise some scope that will include only
the data from two months ago and verify your assertion. Then expand the
scope to "two months ago plus a day" and run it again. Repeat until failure.

This assumes of course that the scope is expandable so easily. (A good
reason to have a "DateEntered" column in every table, which defaults to
GetDate()). 

----- Original Message ----
From: David Emerson <newsgrps at dalyn.co.nz>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>; Access Developers discussion and problem
solving <accessd at databaseadvisors.com>
Sent: Tuesday, November 21, 2006 10:53:58 PM
Subject: Re: [AccessD] Stored Procedure not producing results

Worse - my database from two months ago works fine, but the latest version
is the one that is causing the problem.  This may indicate a data problem
perhaps?

David




--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com







More information about the AccessD mailing list