May 22, 2013 at 1:06 am
Hi all
I think I may be on the right track when considering creating a scheduled job. I need to capture and correct (probably by a simple delete) instances where Application users enter a disallowed email address like '*@specificdomain.com' into the database. We're supporting a SQL2008r2 Server that is used by a third party application developer. They, in their wisdom, have created a nasty side effect that I won't bore anyone with here.
We are unable to alter an stored procedures etc in the DB as they're encrypted, so instead of being able to stop the problem occurring at source we need to cure the issue after the event. I want to be able to generate a job that will scan 2 of the DB's tables for any occurrence of '*@specificdomain.com' that exist in the table that would be identified thus:
SELECT FIRST_NAME, LAST_NAME, EMAIL
FROM Candidates
WHERE EMAIL LIKE '%@specificdomain.com%'
SELECT FIRST_NAME, LAST_NAME, EMAIL
FROM CONTACTS
WHERE EMAIL LIKE '%@specificdomain.com%'
Can a job be created and scheduled to check the DB and either replace or just delete the disallowed email addresses?
Thanks in anticipation of someone enlightening me!
S
May 22, 2013 at 6:08 am
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
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
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply