Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 I HATE LEGACY CODE

Author  Topic 

X002548
Not Just a Number

15586 Posts
Posted - 2004-10-18 : 13:47:55
Well...there's no limit to the amount of seriously bad database designs and code out there...

And I seem to be the lucky loser who gets stuck with it...since they sold the securities wing...there are no bodies left...

There is no RI in this piece of crap....just try and infer the RI from this sql...I think I hurt myself...


select distinct EmpNo, 
       EMPNMl as 'Last Name', 
       EMPNMf as 'First Name',
       a.EhaELEMENT_VALUE as 'AEDesignator',
       a.EhaCHANGE_DATE as 'AEDesignator Start Date' ,
       a.EhaEND_DATE as 'AEDesignator End Date' ,
       dptdeptcde as 'Dept Code' ,
       dptdesc as 'Dept Description',
       ttlcd as 'Historical Title' , 
       z.ttlDesc as 'Historical Title Desc'    
  from dbremphistarchive a,
       dbremphistarchive a1,
       dbrempkey b,
       dbremphistsnapshot c, 
       dbremployeeeffective d,
       dbrdept e, dbvtitle z   
 where keyempid = ehsempid
   and c.ehschange_ind = 'A'
   AND c.EhsUSPF2_CHANGE_DATE = a.EhaCHANGE_DATE
   and a.ehaelement_id = 'USPF2'
   and (a.EhaELEMENT_CODE = 'BM') 
   AND c.EhsTTLCD_CHANGE_DATE = a1.EhaCHANGE_DATE
   and a1.ehaelement_id = 'TTLCD'
   and a1.EhaELEMENT_VALUE = z.ttlTitleCde
   and keyempid = a.ehaempid
   and keyempid = a1.ehaempid
   and keyempno = EmpNo
   and ehsorgn1 = dptdeptcde
   and (dptdesc like '%salt%' or dptdesc like '%tyson%') 
order by dptdesc, a.EhaCHANGE_DATE




Here's a clue...they use the EmpKey thing it seems as a "dynamic" key. So that all "entities" (department, person, ect) share the "key"

Never seen anything like this before...

DDL..the view is the same as the table



DROP TABLE DbrDept
go

CREATE TABLE DbrDept (
       DptDeptCde           char(4) NOT NULL,
       DptDesc              char(30) NOT NULL,
       DptState             char(2) NOT NULL,
       DptLocDsgntr         char(3) NOT NULL,
       DptOfficeTyp         char(2) NOT NULL,
       DptDomInd            char(1) NOT NULL,
       DptZip               char(9) NOT NULL,
       DptEffDt             datetime NOT NULL,
       DptEndDt             datetime NOT NULL,
       DptCurrInd           int NOT NULL
)
go


DROP TABLE DbrEmpHistArchive
go

CREATE TABLE DbrEmpHistArchive (
       EhaEMPID             int NOT NULL,
       EhaCHANGE_DATE       smalldatetime NOT NULL,
       EhaEND_DATE          smalldatetime NOT NULL,
       EhaCREATION_DATE     smalldatetime NOT NULL,
       EhaREASON_CODE       char(2) NOT NULL,
       EhaELEMENT_ID        char(5) NOT NULL,
       EhaELEMENT_CODE      char(6) NOT NULL,
       EhaELEMENT_VALUE     char(40) NOT NULL
)
go


DROP TABLE DbrEmpHistSnapShot
go

CREATE TABLE DbrEmpHistSnapShot (
       EhsEmpID             int NOT NULL,
       EhsEFFDT             smalldatetime NOT NULL,
       EhsEND_DATE          smalldatetime NOT NULL,
       EhsCHANGE_IND        char(1) NOT NULL,
       EhsORGN1             char(4) NOT NULL,
       EhsORGN4             char(4) NOT NULL,
       EhsEMPNO_CHANGE_DATE smalldatetime NOT NULL,
       EhsSTAIN_CHANGE_DATE smalldatetime NOT NULL,
       EhsEMPNM_CHANGE_DATE smalldatetime NOT NULL,
       EhsHSTAD_CHANGE_DATE smalldatetime NOT NULL,
       EhsHSTA2_CHANGE_DATE smalldatetime NOT NULL,
       EhsHCITY_CHANGE_DATE smalldatetime NOT NULL,
       EhsSTATE_CHANGE_DATE smalldatetime NOT NULL,
       EhsZIPCD_CHANGE_DATE smalldatetime NOT NULL,
       EhsSEXCD_CHANGE_DATE smalldatetime NOT NULL,
       EhsMARST_CHANGE_DATE smalldatetime NOT NULL,
       EhsHMTEL_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRTHD_CHANGE_DATE smalldatetime NOT NULL,
       EhsCITZN_CHANGE_DATE smalldatetime NOT NULL,
       EhsREICD_CHANGE_DATE smalldatetime NOT NULL,
       EhsCNTYC_CHANGE_DATE smalldatetime NOT NULL,
       EhsMRGDT_CHANGE_DATE smalldatetime NOT NULL,
       EhsOHRDT_CHANGE_DATE smalldatetime NOT NULL,
       EhsDTLHR_CHANGE_DATE smalldatetime NOT NULL,
       EhsCNTSD_CHANGE_DATE smalldatetime NOT NULL,
       EhsSTCMC_CHANGE_DATE smalldatetime NOT NULL,
       EhsEMPCC_CHANGE_DATE smalldatetime NOT NULL,
       EhsTRMDT_CHANGE_DATE smalldatetime NOT NULL,
       EhsLOASD_CHANGE_DATE smalldatetime NOT NULL,
       EhsLOARD_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSSD1_CHANGE_DATE smalldatetime NOT NULL,
       EhsBASRT_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRTCR_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRTEX_CHANGE_DATE smalldatetime NOT NULL,
       EhsCBRTE_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRTAD_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRTFR_CHANGE_DATE smalldatetime NOT NULL,
       EhsADJRT_CHANGE_DATE smalldatetime NOT NULL,
       EhsADJRC_CHANGE_DATE smalldatetime NOT NULL,
       EhsADJEX_CHANGE_DATE smalldatetime NOT NULL,
       EhsCADJR_CHANGE_DATE smalldatetime NOT NULL,
       EhsADJRF_CHANGE_DATE smalldatetime NOT NULL,
       EhsSCHRS_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSCD1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSCF1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSCF2_CHANGE_DATE smalldatetime NOT NULL,
       EhsTTLCD_CHANGE_DATE smalldatetime NOT NULL,
       EhsPYGRD_CHANGE_DATE smalldatetime NOT NULL,
       EhsFLSAC_CHANGE_DATE smalldatetime NOT NULL,
       EhsEEOJC_CHANGE_DATE smalldatetime NOT NULL,
       EhsEEOJG_CHANGE_DATE smalldatetime NOT NULL,
       EhsORGN1_CHANGE_DATE smalldatetime NOT NULL,
       EhsORGN4_CHANGE_DATE smalldatetime NOT NULL,
       EhsPYRCD_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSPD1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSPD2_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSPF1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSPF2_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSDT1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSDT2_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSDT3_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSDT4_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL1_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL2_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL3_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL4_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL5_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSFL9_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSF10_CHANGE_DATE smalldatetime NOT NULL,
       EhsALRGN_CHANGE_DATE smalldatetime NOT NULL,
       EhsSOCIN_CHANGE_DATE smalldatetime NOT NULL,
       EhsPRSSN_CHANGE_DATE smalldatetime NOT NULL,
       EhsANNSL_CHANGE_DATE smalldatetime NOT NULL,
       EhsANNSU_CHANGE_DATE smalldatetime NOT NULL,
       EhsBRUSE_CHANGE_DATE smalldatetime NOT NULL,
       EhsKOINS_CHANGE_DATE smalldatetime NOT NULL,
       EhsADNM1_CHANGE_DATE smalldatetime NOT NULL,
       EhsHTEL1_CHANGE_DATE smalldatetime NOT NULL,
       EhsADNM2_CHANGE_DATE smalldatetime NOT NULL,
       EhsHTEL2_CHANGE_DATE smalldatetime NOT NULL,
       EhsSAMT1_CHANGE_DATE smalldatetime NOT NULL,
       EhsSAMT3_CHANGE_DATE smalldatetime NOT NULL,
       EhsSAMT4_CHANGE_DATE smalldatetime NOT NULL,
       EhsSUPF1_CHANGE_DATE smalldatetime NOT NULL,
       EhsSUPF3_CHANGE_DATE smalldatetime NOT NULL,
       EhsSUPF4_CHANGE_DATE smalldatetime NOT NULL,
       EhsUSPF4_CHANGE_DATE smalldatetime NOT NULL,
       EhsMCLAG_CHANGE_DATE smalldatetime NOT NULL,
       EhsUNREP_CHANGE_DATE smalldatetime NOT NULL,
       EhsHTELA_CHANGE_DATE smalldatetime NOT NULL,
       EhsHSCHC_CHANGE_DATE smalldatetime NOT NULL,
       EhsMNSSN_CHANGE_DATE smalldatetime NOT NULL,
       EhsSRVAW_CHANGE_DATE smalldatetime NOT NULL,
       EhsASSSN_CHANGE_DATE smalldatetime NOT NULL,
       EhsPRFNM_CHANGE_DATE smalldatetime NOT NULL
)
go


DROP TABLE DbrEmpKey
go

CREATE TABLE DbrEmpKey (
       KeyEmpNo             char(12) NOT NULL,
       KeyEmpID             int NOT NULL,
       KeyEffDt             smalldatetime NOT NULL,
       KeyName              char(30) NOT NULL,
       KeySex               char(1) NOT NULL,
       KeyDOB               smalldatetime NOT NULL
)
go


DROP TABLE DbrEmployeeEffective
go

CREATE TABLE DbrEmployeeEffective (
       EMPNO                char(12) NOT NULL,
       STAIN                char(1) NOT NULL,
       EMPNMl               char(25) NOT NULL,
       EMPNMf               char(25) NOT NULL,
       EMPNMm               char(25) NOT NULL,
       EMPNMt               char(10) NOT NULL,
       EMPNMs               char(10) NOT NULL,
       HSTAD                char(24) NOT NULL,
       HSTA2                char(24) NOT NULL,
       HCITY                char(24) NOT NULL,
       STATE                char(2) NOT NULL,
       ZIPCD                char(9) NOT NULL,
       SEXCD                char(1) NOT NULL,
       MARST                char(1) NOT NULL,
       HMTEL                char(15) NOT NULL,
       BRTHD                smalldatetime NULL,
       CITZN                char(3) NOT NULL,
       REICD                char(1) NOT NULL,
       CNTYC                char(3) NOT NULL,
       MRGDT                smalldatetime NULL,
       OHRDT                smalldatetime NULL,
       DTLHR                smalldatetime NULL,
       CNTSD                smalldatetime NULL,
       STACT                char(2) NOT NULL,
       STCMC                char(2) NOT NULL,
       EMPCC                char(2) NOT NULL,
       TRMDT                smalldatetime NULL,
       LOASD                smalldatetime NULL,
       LOARD                smalldatetime NULL,
       USSD1                smalldatetime NULL,
       CMPAC                char(2) NOT NULL,
       BASRT                float NOT NULL,
       BRTCR                char(3) NOT NULL,
       CBRTE                float NOT NULL,
       BRTAD                smalldatetime NULL,
       BRTFR                char(1) NOT NULL,
       ADJRT                float NOT NULL,
       ADJRC                char(3) NOT NULL,
       CADJR                float NOT NULL,
       ADJRF                char(1) NOT NULL,
       SCHRS                float NOT NULL,
       USCD1                smalldatetime NULL,
       USCF1                char(5) NOT NULL,
       USCF2                char(10) NOT NULL,
       POSAC                char(2) NOT NULL,
       TTLCD                char(6) NOT NULL,
       PYGRD                char(5) NOT NULL,
       FLSAC                char(1) NOT NULL,
       EEOJC                char(2) NOT NULL,
       EEOJG                char(2) NOT NULL,
       ORGN1                char(5) NOT NULL,
       ORGN4                char(5) NOT NULL,
       PYRCD                char(5) NOT NULL,
       USPD1                smalldatetime NULL,
       USPD2                smalldatetime NULL,
       USPF1                char(3) NOT NULL,
       USPF2                char(3) NOT NULL,
       USDT1                smalldatetime NULL,
       USDT2                smalldatetime NULL,
       USDT3                smalldatetime NULL,
       USDT4                smalldatetime NULL,
       USFL1                char(5) NOT NULL,
       USFL2                char(10) NOT NULL,
       USFL3                char(10) NOT NULL,
       USFL4                char(3) NOT NULL,
       USFL5                char(5) NOT NULL,
       USFL9                char(10) NOT NULL,
       USF10                char(10) NOT NULL,
       ALRGN                char(12) NOT NULL,
       SOCIN                char(12) NOT NULL,
       PRSSN                char(12) NOT NULL,
       ANNSL                float NOT NULL,
       ANNSU                float NOT NULL,
       BRUSE                float NOT NULL,
       KOINS                float NOT NULL,
       EFF_ADDR             smalldatetime NULL,
       EFF_EMPCC            smalldatetime NULL,
       EFF_STAIN            smalldatetime NULL,
       EFF_MARST            smalldatetime NULL,
       EFF_TTLCD            smalldatetime NULL,
       EFF_PYGRD            smalldatetime NULL,
       EFF_FLSAC            smalldatetime NULL,
       EFF_ORGN1            smalldatetime NULL,
       EFF_PYRCD            smalldatetime NULL,
       EFF_USPF1            smalldatetime NULL,
       EFF_USPF2            smalldatetime NULL,
       EFF_BASRT            smalldatetime NULL,
       EFF_BRTCR            smalldatetime NULL,
       EFF_BRTFR            smalldatetime NULL,
       EFF_USCF1            smalldatetime NULL,
       ADNM1                char(40) NOT NULL,
       HTEL1                char(15) NOT NULL,
       ADNM2                char(40) NOT NULL,
       HTEL2                char(15) NOT NULL,
       SAMT1                float NOT NULL,
       SAMT3                float NOT NULL,
       SAMT4                float NOT NULL,
       SUPF1                char(5) NOT NULL,
       SUPF3                char(2) NOT NULL,
       SUPF4                char(2) NOT NULL,
       USPF4                char(5) NOT NULL,
       STAED                smalldatetime NULL,
       CMAED                smalldatetime NULL,
       PSAED                smalldatetime NULL,
       MCLAG                char(12) NOT NULL,
       BRTEX                char(1) NOT NULL,
       ADJEX                char(1) NOT NULL,
       UNREP                char(4) NOT NULL,
       HTELA                char(15) NOT NULL,
       EFF_HTELA            smalldatetime NULL,
       HSCHC                char(2) NOT NULL,
       EFF_HSCHC            smalldatetime NULL,
       MNSSN                char(12) NULL,
       SRVAW                char(2) NULL,
       ASSSN                char(12) NULL,
       PRFNM                char(30) NULL,
       EmpNextReviewDt      smalldatetime NULL,
       EmpFACommIntranetInd char(1) NOT NULL
)
go


DROP TABLE DbrTitle
go

CREATE TABLE DbrTitle (
       TtlTitleCde          char(6) NOT NULL,
       TtlDesc              char(30) NOT NULL,
       TtlEffDt             datetime NOT NULL,
       TtlEndDt             datetime NOT NULL,
       TtlCurrInd           int NOT NULL
)
go




And there's like 30 million rows in this...




Oh, no question really...



Brett

8-)

Kristen
Test

22859 Posts
Posted - 2004-10-18 : 14:11:06
So ... 

Table                ColumnNamePrefix
DbrDept              Dpt
DbrEmpHistArchive    Eha
DbrEmpHistSnapShot   Ehs
DbrEmpKey            Key
DbrEmployeeEffective (None)
DbrTitle             Ttl

Take your pick ... abbreviation, achronym, or nothing

There are NO varchar columns - I'm sure there's a good reason for that.

There is not a single column that allows NULLs ...

There's a Y2.079 problem (in the DbrEmpHistSnapShot table it effects 95% of the columns)

It appears that all the JOINs are INNER JOINS.

So quite a lot of good news really. What was the question again? Should you "fire or promote" the DBA that oversaw this development?? ;-)

Kristen
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2004-10-18 : 14:50:21
Gotta love the descriptive column names! Sure makes maintenence for others super easy!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-18 : 14:56:48
nice design

emm... whats RI ?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

surefooted
Posting Yak Master

188 Posts
Posted - 2004-10-18 : 16:03:30
quote:
Originally posted by MichaelP

Gotta love the descriptive column names! Sure makes maintenence for others super easy!


I'd bet that someone very familiar with mainframe db2 made those table names.

-Jon
Now a "Yak Posting Veteran".
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-10-18 : 16:25:04
quote:
Originally posted by spirit1

nice design

emm... whats RI ?

Go with the flow & have fun! Else fight the flow



Ummmmmm....Relational Integrity....Foreign Keys....

Brett

8-)
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts
Posted - 2004-10-19 : 03:46:31
At least you have relational integrity!!

And no I'm not joking. Our supplier not only 'invents' integrity and dumps it in the client, he does the same with record locking and autoincrementing ID's too.

I think I might change my sig to something like: I have a dream.. that one day I'll find a database that is well designed (or maybe that should read just designed) and that I'll find network support that does. Oh, and be allowed a domain account with local admin priveliges to run the agents under (nearly forgot that one)

steve



To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-19 : 04:54:02
ha ha ha, you're being bitter again elwoos...

--------------------
keeping it simple...
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-19 : 06:46:16
brett thanx. i alwaya have a problem with a few of english abbreviations. like RI... never heard it before

Go with the flow & have fun! Else fight the flow
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-10-19 : 08:59:47
No sweat...lot of people don't know what RI is....or they think it's a concept that doesn't require to be paid attention to...

er go my sample of garbage....it just seems everytime we take on stuff from other departments that we have sold, All I get is more garbage....

Now if I were a consultant, this would be a good thing....

Maybe I should post this on WTF

http://thedailywtf.com/archive/2004/09/01/1511.aspx



Brett

8-)
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-10-19 : 14:02:23
" i alwaya have a problem with a few of english abbreviations. like RI... never heard it before"

RI... is short for religion.

Kristen

SQL Team: "Never in the field of debugging was so much owed by so many muppets to so few guru-nerds"
Go to Top of Page

tuenty
Constraint Violating Yak Guru

278 Posts
Posted - 2004-10-19 : 15:59:04
quote:
At least you have relational integrity!!

Well I was hired to convert different systems with programming languages and different databases
to 1 (vb + sql) for the first phase I created 4 tables with a one to many relationship between
tables 1=>2=>3 and a relationship of one to zero or one between tables 3=>4

Other programmers made the programs to load data and sometimes they try adding data in the third table
before adding in the first two or try deleting from the first before deleting from 4,3, and 2, the
relationship constraints were eliminated (everybody has access don't know who did it) and when I tried
to put them back I was not able cuz there are lots of child records without the parent record.
I'm planning on making some tables for those cases insert all incomplete transactions into those
tables and DELETE it from the tables they are now to implement the Relational Integrity again.

and on top of that my boss links to SQL through Access and performs whatever she wants. sometimes she
deletes the content of entire table and then she asks why the table is empty

There is no DBA (and there is no hope that some day soon there will be one) so I try to be but I'm still
in diapers for all that of RI and security I'm waiting for the day that I eliminate the sa account

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-10-19 : 16:53:40
quote:
Originally posted by tuenty

and on top of that my boss links to SQL through Access and performs whatever she wants. sometimes she
deletes the content of entire table and then she asks why the table is empty


Excellent!

quote:

There is no DBA (and there is no hope that some day soon there will be one) so I try to be but I'm still
in diapers for all that of RI


tag..you're it

quote:

and security I'm waiting for the day that I eliminate the sa account


Well....you can't.....you can change the password and not tell anyone....

Is anyone doing backups?





Brett

8-)
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts
Posted - 2004-10-19 : 17:27:04
Jen, you forgot twisted - bitter and twisted

To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2004-10-20 : 04:43:29
quote:

Well....you can't.....you can change the password and not tell anyone....

Is anyone doing backups?


Brett

8-)


<hint> trouble... </hint>

--------------------
keeping it simple...
Go to Top of Page

tuenty
Constraint Violating Yak Guru

278 Posts
Posted - 2004-10-20 : 09:52:35
quote:
Originally posted by X002548


Is anyone doing backups?





Brett

8-)


Well another programmer scheduled the backups as part of a maintanance plan, and a transaction log
every half hour and I always (daily) make a backup of the data before using it to update the other
two systems. But usually the tables my boss deletes are tables that I create by uploading some txt
so that it is not the worst, except for the fact that she calls me as if it was my fault or another
programmers fault and nobody else uses those tables but her and EDI program (that only selects data
from those tables), I have seen her doing worst things:
At the begining I was going to make a log (programmatically) in which I post the user, date and
action performed in a record but she disagree because it was too abstract for her (she does not know
how to join tables) and told me to add three fields to each table to store user, date, and an action
flag so that is the way I did it and guess what...no, wrong that is not it you ar not even close
I have seen her using the application I built to alter data and then going to the database and
replacing her username to another user username...
Can you imagine...I have to check the production to make sure she did not use my name to cover up
what she did and believe me this is only the begining but I'm not going to tell you the whole story
because talking about it gets me in a bad mood.
oh but one last thing She was recommended by a friend of the company's president who is a very kind
man and will never fire her

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-20 : 10:07:14
use triggers on the tables to see who's doing what. pile up the evidence patiently and
when the time comes simply show them to your boss with comment that she's wasting hours
of your work to clean her mess up...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

tuenty
Constraint Violating Yak Guru

278 Posts
Posted - 2004-10-20 : 10:51:20
quote:
Originally posted by spirit1

use triggers on the tables to see who's doing what. pile up the evidence patiently and
when the time comes simply show them to your boss with comment that she's wasting hours
of your work to clean her mess up...

Go with the flow & have fun! Else fight the flow


That sounds great
Now I have to make a new post on how do I make a trigger that identifies who's doing what when
every body uses the SA account to access the database except the application I built

I have an idea, I would store some information of the record worked on and account used to access
the DB, the NT user, the machine and datetime the action was made. But I don't know how do I
determine the NT user, the machine and if I can detect that the modification is being done from
access

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2004-10-20 : 11:09:47
well if she's logging in with sa, you can forget it....I wonder if you can capture the machine name of the spid....



Brett

8-)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-10-20 : 11:20:07
change the sa password. justify it with untolerable security risks...
assign everyone their username and problem solved.



Go with the flow & have fun! Else fight the flow
Go to Top of Page

surefooted
Posting Yak Master

188 Posts
Posted - 2004-10-20 : 11:57:35
select hostname from sysprocesses where spid = ?

-Jon
Now a "Yak Posting Veteran".
Go to Top of Page
  Previous Page&nsp;  Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site