0

I have a Windows batch file which extracts some data from a remote system and then imports it into an instance of MS SQL Server 2014. The batch file runs SQLCMD and uses SQL files to bulk insert from the text files into a staging table, tidy the fields up and move the data to a production database.

Having finished the development of this, I would like to test the stability of the process by easily knowing when it has run successfully.

  • I could check the batch file error log, but it is part of a larger process which means it takes a while to find the SQL Server part.
  • I could send an email to myself when the process has finished, but this does not tell me if it worked properly. By this, I mean if the INSERT INTO statement from the staging table to the production table did not produce any errors.

Does anyone have any suggestions? My gut feeling is that I need to include something in the SQL statements I use to import the data. Thanks!

Improve this question
3
  • 2
    How do you measure "properly"? We have no idea. Also, this seems like a weird thing to alert on - imagine if the banks sent the police a message every hour saying "not getting robbed"? Your code should be built to determine its own success and to detect and handle errors - get an alert when it breaks, not when it works.
    – Aaron Bertrand
    Commented Jul 31, 2015 at 10:07
  • @Aaron: This is only temporary, as I would like to see when it has worked each day, so I can go in and analyst the data. You are quite right in that I should be getting an alert when the process hasn't worked, but I can surely use a similar approach to accomplish this.
    – Xangrim
    Commented Jul 31, 2015 at 10:28
  • Detecting errors is pretty easy using TRY/CATCH. Have you looked into error handling at all?
    – Aaron Bertrand
    Commented Jul 31, 2015 at 10:40

1 Answer 1

Reset to default
0
  1. count lines/records in your textfile
  2. import
  3. count records after import
  4. if count1=count2 then continue
  5. tidy up fields (update blablabla set changefield='new' where myfield='badinfo')
  6. test tidy up : select count(*) from blablabla where myfield='badinfo' and changefield<>'new'
  7. if testTidyUp-count = 0 then cleanup = correct ... continue
  8. count records after tidy up
  9. export to production
  10. count records in production
  11. if count3=count4 then ... let mysql know all is 'ok'

All the else ... mail that something is wrong

Improve this answer
1
  • 3
    Surely using TRY/CATCH is more straightforward then all this counting work...
    – Aaron Bertrand
    Commented Jul 31, 2015 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.