SQL Server Performance

Verify records

Discussion in 'General Developer Questions' started by jenny30534, Aug 19, 2003.

  1. jenny30534 New Member

    I don't know if I can explain what I am needing well, but here goes. The regular DBA is out on a medical leave and I am filling in or trying to that is.

    We are using a Repository package to pull data into a table nightly from a text file. I need a way to verify that (for example) the dollar amounts and units in the table match what is in the text file and if they do not match have an alert emailed to me. Any help is appreciated.

    JP
  2. vbkenya New Member

    If you suspect that the package is going to import the wrong values into the database, the first line of defence would be to test and thoroughly debug the package rather than to wait for errors and alerts.

    To be able to compare the text file with the imported values you will have to create a procedure to read the text file again row by row comparing the result with every matching row in the table and then trigger the execution of an alert. I would not encourage this - There is a high chance that you might send the regular DBA on permanent medical with a STROKE.

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  3. gaurav_bindlish New Member

    U can design a DTS package for this... If its worth the effort [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1">
  4. jenny30534 New Member

    The issue is that we have never had a problem with the fields not matching, only a problem with the text file not copying over from our Unix system correctly 1st before it is imported into the table, however my boss now wants this verification in place for some reason, so I don't have much say in the matter. I only want to compare 3 rows, and I just want to be alerted when the totals don't match (if that ever happens).
  5. gaurav_bindlish New Member

    After importing the file into database, run a shell script.

    1. BCP out the imported data into a text file.
    2. Comapre the files using 'comp'.
    3. Redirect the output to a file.
    4. Import the file into database to parse or use some command line utility for the same.
    5. If difference is found, generate alert.

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  6. bambola New Member

    > The issue is that we have never had a problem with the fields not matching, only a problem with
    > the text file not copying over from our Unix system correctly 1st before it is imported into the
    > table

    If so, you should varify the file copy before importing it into the database. Otherwhise to what would you compare it?
    I mean, if the file was not copied correctly, you will import incorrect values. And no matter what way you use to compare the imported data with the data in the file, it will match, wouldn't it? (as long as you read them correctly)

    Or am I missing something...

    Bambola.
  7. gaurav_bindlish New Member

    Right Bambola... great catch.

    I thought the requirement was to see
    if the file was imported correctly intead of understanding
    if correct file was imported.

    So much for shifting just one word...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page