Last Updated datetime for 100+ tables
-
Hello,
We have a large number of Intermediate and Aggegate tables that are populated daily. We have been using a product called Lotus Enterprise Integrator to transfer Lotus Notes data daily to our SQL-Server 2000 base tables. I can see the date and time of each of these processes no problem.
Next, we still use LEI to run lots of jobs that are just some SQL of the form:
BEGIN TRAN
--delete intermediate /aggeregate table data
--insert fresh data from base table
END TRAN
If the job fails, then the transaction rolls back. We have over 100 of these daily jobs are many more are due next year. I thought that there would be a way to find the last time that a table was successfully updated but it seems not so easy. the only was is to either find a free tool to study the historical log file or write some triggers that write to an audit table. (but my boss thinks that there must be an easier way, and no triggers are to be used)?
Is the best way to do this to just check the Error Code in each job. Then call a stored procedure that accepts the table name and error code and places the value tablename, ErrorCode and getdate() into some new audit table. Then just check the audit table daily or write some job to email a list of tables that have a daily error?
Any ideas?
Thanks in advance
Chris
-
- doesn't Lotus Enterprise Integrator have any error-reporting feature ??
- you could create a parameter-data containing Table_name and timestamp_last_update and then add as last statement of each table-handling block :
update T_TableLoadData
set timestamp_last_update = getdate()
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update 
values( 'Mytable', getdate())
end
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks, in this case we are using LEI to just hold some SQL and run them daily, 1 job for each datawarehouse table insert/update.
OK, I could use what you have done and include the error code if a transaction fails, so something like
BEGIN TRAN
--run some intermediate/agregate table transaction
IF @ERROR=0
BEGIN
COMMIT TRAN
update T_TableLoadData
set timestamp_last_update = getdate()
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', getdate(),'none')
end
ELSE
BEGIN
update T_TableLoadData
set last_update_error = @SQL_Error
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', '', @SQL_Error)
end
END
Is this a reasonable enough solution? Boss wants an answer today, and no buying any products. Also, with this, I can implement quickly enough
-
sorry, forgot the rollback TRAN and should be @@Error not @Error.
BEGIN TRAN
--run some intermediate/agregate table transaction
IF @@ERROR=0
BEGIN
COMMIT TRAN
update T_TableLoadData
set timestamp_last_update = getdate()
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', getdate(),'none')
end
ELSE
BEGIN
--some failure, rollback to last successful data ROLLBACK TRAN
update T_TableLoadData
set last_update_error = @SQL_Error
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', '', @SQL_Error)
end
END
-
It will be more interesting when :
1) make your timestamp_last_update a datetime column !
2) always update the last_update_error (make it INT)
set @SQL_Error = @@error
if @SQL_Error = 0
begin
update T_TableLoadData
set timestamp_last_update = getdate()
, last_update_error = @SQL_Error
where Table_Name = 'Mytable'
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', getdate(),@SQL_Error)
end
ELSE
BEGIN
--some failure, rollback to last successful data ROLLBACK TRAN
update T_TableLoadData
set timestamp_last_update = getdate()
,last_update_error = @SQL_Error
where Table_Name = 'Mytable'
if @@rowcount = 0
begin
Insert into T_TableLoadData (Table_Name , timestamp_last_update, last_update_error
values( 'Mytable', getdate(), @SQL_Error)
end
END
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks again alzdba
Ok, my boss can be quite quarrelsome. So, you would agree that what we have come up with is a good enough solution for very basic table audit. I guess i was just wondering what other companies do. As said already, we don't need row level audit, just table level, i.e all tables updated successfully in the last 24 hours, and if not why not
Some great code snippets on this site. I found a way to always avoid cursors using temp table objects to store 'cursorable' fields. Wish I'd found it sooner
-
We use this T_TableLoadData in a way that we record if data is being loaded (loading_bit = true) and all applications querying the data will use a view which has an inner join with T_TableLoadData on table_name = 'mytablename' and loading_bit = false (not loading).
IMO avoiding access to data (using this kind of views) and keeping up records containing datetime_last_load/refresh is a good enough and simple audit-like mechanism.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply