Post reply

Need to be alerted to disallowed email addresses being entered - Admin cleaning up after App programmers! :-)

  • May 22, 2013 at 6:08 am

    #1617378

    I would expect so.

    Create an Agent job, make the step a "T-SQL" step, and put your T-SQL in the box. I would suggest perhaps either restoring a copy of the vendors DB to another server to test against, or create a "bogus" temporary DB and table to work against. DON'T work against the production data!

    Possibly even consider, if you've got DBMail configured, having the job e-mail you (or an appropriate person(s)) a list of the data that was found and changed (possibly including the original information.)

    I'd also look at splitting this into either two jobs, or multiple steps, one for each table you need to go through. That way, if one fails, the other can still run.

    Jason

  • June 3, 2013 at 4:11 am

    #1620453

    Hi Jason,

    I found the cure - and the details are below. However, prevention is best and I'm stumped. What would be most handy is a means by which I could prevent *@disallowedcomain.com from being entered in the first place. Any thoughts?

    Thanks for the reply. With a little further research I figured out a quite nice way to perform the task by creating a 4 step Job.

    I first created 2 SSIS packages to run the queries and dump the results to CSV with overwrite so as not to grow the file overtime. Each SSIS package created its own file ie 'App.csv' then 'Cont.csv'.

    The Job could then be built, Step 1 - Query and Gen app.csv file, Step 2 - Query and Gen cont.csv file, Step 3 (using SQL mail) send message and corresponding file as an attachement to x@email.com, Step 4 - (using SQL mail) send message and corresponding file as an attachement to y@email.com.

    Then simply schedule the job - and Bob's your Uncle and Roberts your Father's brother!

    Cheers S

  • June 3, 2013 at 5:47 am

    #1620479

    Could you not create a DML trigger(s) on the table to stop any inputs that you don't require? There would be an overhead but you would need to weigh that up.

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply