Who can help me with some slightly complex MSSQL? I am looking for some back-and-forth communication for a while regarding: DTS packages cursors best practices..... So - here's my dilemma. I need to build something in the next couple of days. some of the details are as follows: The process for the setup of the various employee information records is as follows:1. a manager contacts the helpdesk and requests that the new employee be "set up" 2. the helpdesk analyst (among other things,) creates a "person" record for the new employee in the incident tracking software used by the helpdesk. This person record has incomplete data, that may stay incomplete forever. 3. The helpdesk analyst then pushes a button on the form in the incident tracking software. This fires some VB code that creates a record in Active Directory for this new employee. The data is still incomplete. 4. Every night, *something* runs (DTS?) that copies everything new or changed into the human resources database that is used by HR through some third party proprietary software. 4.b - also, everything new or changed is copied from Active Directory into the "Common User Database" - more on that later. 5. The HR staff then updates the incomplete information that they received from AD with information that they care about. 5.b - new or changed records from the proprietary third party HR database are copied into the "Common User Database" - (From now on, this may be referred to as the CUDB)There are a few problems here. One of the big ones is that the information from HR never makes it back to AD or into the incident tracking software. Also, when a person changes name (marriage,) job, or location within the company, HR updates it in their system, but it rarely goes outside their system. Another big problem I discovered - the CUDB has a column listing when that row was last modified and whether the data in that row was modified by AD or by the HR software, but unfortunately, I learned that those columns weren't being consistently updated by the processes that put the data into the CUDB. The CUDB was created X years ago with the intention of using it for all kinds of things within the company, but the project sort of fell apart, and it's not really being used by anyone except for a few people as a phone directory. So, to fix *all that, I'm going to write software so that the incident tracking system refreshes itself from the CUDB. (* By "all" I am referring to the part that affects me - the stuff related to Incident Management.) Here's my overall plan - 1. start with a table containing Yesterday's Data 2. Set the status in every record in Yesterday's Data to "inactive" 3. copy everything from CUDB to a blank table (Today's Data) in my system (DTS Package?) 4. (Cursor?) for each record in Today's Data: (Using LOGIN id as the key,)a. look for a record in Yesterday's Data with a matching ID. If a record is found:i. If all the data is the same, set status to "No Change" ii. If any of the data has changed, set status to "Refresh"b. If no record is found, create one with a status of "Create" c. Any records that exist in yesterday's data that aren't found in today's data will keep their status of "Inactive" that was set at the beginning of the process. I would then take the fresh information from the Yesterday's Data table and use it to update my people table. (Probably with another cursor...) so - A: How does my plan look and B: who can help me with DTS and cursors? I've learned a bunch of SQL by needing to do it and learning how the hard way, but before I worked in an office with great DBAs that I could go to for help. I'm not there anymore, so I was hoping that a BT member could nudge me in the right direction. --Thanks
What is the database for the Incident Tracking System and CUDB? Are they both SQL Server (2000, 2005)? Out of curiosity, why do you think you need a cursor? I believe 2-3 SQLs could take care of it. DTS does sound like a necessity.
Does it have to go VB >> AD, or can it go AD >> SQL Server? Have you tried a dedicated SQL forum or Experts-Exchange? I fear you'll get limited help here in the time frame you have.
Any records in AD that have been changed are copied into the HR system database is MSSQL 2000 for everything except HR system, but since CUDB gets its information from HR, I don't have to care about that one. As far as I'm concerned, everything is MSSQL. 'Cause of my lack of experience with SQL.... How do I push changes from each different record? Well, the part from VB into AD is already written and has been in place for this company for years; and the part that goes pushes from AD to the CUDB is already existing - All I care about is bringing in the fresh data from the last bit. -Thanks for your input everyone - Any fresh ideas? nachofault - could you give me some pseudocode for the SQL that would do the pushes of todays data into yesterdays data?
Ok - I didn't get much in the way of response, so here's my plan: I've set up DTS so that every night, the columns that I care about in the old table are copied over to a table in my system. My plan at this point, though it may not be the most efficient, is to set up a trigger on that inbound table that compares it to my existing people table. Based on the results of that, a column in the table for the data that has been copied over will be set to: - 'Data Matches' - 'Data Does Not Match' - 'Record Does Not Exist' I will have another trigger that does stuff based on the results of the first trigger - but, maybe I can do it all in one. Anyway - I'd love to hear an idea on a better plan - anyone? I have the ability to build this right now.... So, I guess I will.
So - Here's my psuedocode: Trigger psuedocode – On insert into new table Declare @CountMatch integer Declare @InboundLoginID Set @InboundLoginID = (select account_id from inserted) Set @CountMatch = (select count * from RemedyPeopleFormImport where RemedyPeopleFormImport.status < 3 and RemedyPeopleFormImport.loginID = @InboundLoginID) If @CountMatch < 2 If @CountMatch = 0 Insert into peopleimport Insert into peopleimport.history update NewTable set importstatus = ('People Record Created') If @countmatch = 1 If ((last_name != last_name) or (first_name_prefered != first_name_prefered) or (first_name_formal != first_name_formal) or (email != email) or (work_location != work_location) or mnger != mngr or phone or fax or mobile or pager or acct_unit or gl_company or address or city or state or country or postal code or region or site group or site) Update peopleimport update NewTable set importstatus = ('People Record Modified') else update NewTable set importstatus = ('Data matched – no changes made.') Else update NewTable set importstatus = ('Duplicate Login IDs Found') ------------ Edit: It pulled my indents out.... They made things a lot clearer. I will edit them back in later.
