Somebody in the tech support department of our ERP vendor has to be hating me right now. I’m kind of okay with that.
In a recent audit, the auditors wanted to demonstrate the integrity of our ERP system’s audit log and prove it hasn’t been tampered with. Considering the primary key on the table is an IDENTITY column, this should be really simple, right? Nope.
Considering that this is an IDENTITY column, the only way we should have a gap in the sequence is if someone is deleting records or perhaps a deadlock. If someone or something is deleting out of an audit table, I’m going to have a fit. I’m the only person in the company who has both the access to the database back end AND the skills to delete a record from a table.
So how did I find the gaps?
where CreateDate BETWEEN @p_startdate and @p_enddate
and id + 1 NOT IN
(select id from tablename
WHERE CreateDate BETWEEN @p_startdate and @p_enddate)
It may not be optimal code, but it served the purpose. With a little more tweaking, I was able to calculate the number of records in the gap, the time of the gap, and even provide a variable number of records before and after the gap. What I found is that it’s mostly the same four processes causing gaps in our audit log and these gaps are rarely more than one second.
Either I’m off my rocker, or somebody has some explaining to do.
I just ran into an odd little problem. And of course, solving it turned out to be kind of fun. Since I don’t do a ton of development work, this little challenge took some thinking.
We’re building a little datamart, and I want to pull a few tables from the source database onto a different server. This shouldn’t be a problem, right? SSMS will generate the scripts to recreate the tables for me. That’s when I ran into a snag. This database uses a user defined data type for every column in the database. Literally every column. 1501 user defined data types. This is the same database that names the customer orders table CO and the purchase orders table PO. Yeah, that one.
The answer hit me in the shower this morning. UDTs are stored in system tables just like anything else. With a little bit of spelunking through the system tables, I had my answer.
select so.name, sc.name, st.name, sc.max_length
from sys.objects so
inner join sys.columns sc
on so.object_id = sc.object_id
inner join sys.types ut
on sc.user_type_id = ut.user_type_id
inner join sys.types st
on ut.system_type_id = st.user_type_id
where so.name IN (‘co’, ‘po’)
order by so.name, sc.column_id
By throwing this into Crystal Reports with a little formatting, I saved myself hours of work. It’s not pretty, but it certainly works.