May 5, 2005 at 8:35 am
Background:
I have a DB that has 5 tables (USERS, COMPUTERS, GROUPS, OU) and is populated using DTS every a.m. from Active Directory with new and modified security princpals (ie USERS, COMPUTERS, GROUPS and Organizational Units).
My DTS order:
1. Copy security principal properties into a file.
2. Truncate a table (AD_temp).
3. Insert records from file into AD_temp.
4. Insert security principals that are not in AD_TEMP, but are in the security princpal tables into new temporary deleted tables (ie DELUSERS, DELCOMP, DELGROUP, DELOU).
5. Insert all the records from the temporary deleted tables into AUDITLOG table, marking them with a "D" for delete in the LOGTYPE field.
6. Insert new security princpals into AUDITLOG table that are in AD_TEMP but not in the security principal tables, marking them with an "I" for insert in the LOGTYPE field
7. >>>>>Insert security principals whose properties have been modified into AUDITLOG based on the difference between the security principal's table and AD_TEMP, marking them with an "U" for update in the LOGTYPE field. Example: If a user has changed positions within the company, the member and memberOf fields are modified.<<<<<<<<<<<<<
8. Truncate the tables (USERS/COMPUTERS/GROUPS/OU)
9. Populate the tables (USERS/COMPUTERS/GROUPS/OU) by partitioning data from AD_TEMP
10. Drop temporary deleted tables
====================================================================
My problem:
The memberOf and member fields in some of the tables can be 60K characters, but if I use a data type of "text", step #7 fails, because comparing text fields is prohibited (as per the errors I get). If I use varchar for memberOf and member then the data is truncated and I am missing data.
======================================================================
What are my choices: varchar or text? Does any have a solution?
May 5, 2005 at 10:31 am
Do you need to companre the memberOf and Member fields or can you just replace what's there with the new data? If you did that everyday, that should work.
Alternatively, and a PIA, setup a child table for these fields, parse out the data in 8k chunks into a series of child rows for each parent.
May 5, 2005 at 12:25 pm
Thanks Steve. You're brave soul; others seem too be to scared to answer or I'm not clear enough.
You have a very good idea; but how will I know if member and or memberOf has been changed and how can I log the change?
May 6, 2005 at 7:09 am
Another potential angle:
Hash (such as MD5) the text field and keep that in a separate field. Calculate a hash the incoming value and compare it to the existing field, if different, replace.
You will either need to find or implement the hash function, it's not built in to SQL.
...
-- FORTRAN manual for Xerox Computers --
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply