The lady vanishes...(her row!!)
-
Hi all,
I've got a database that serves a web telephone book. A user cannot see her name in this book - sure enough, when I check, she isn't listed in either of the tables that house the data (staffdetails, staffdetails2). I insert her details explicitly in QA, highlight my 'select' statement which shows the insert succeeded...but when I return half an hour later, the entry is no longer there.
There is a DTS job that is capable of pulling data in from Active Directory, deletes the rows in Staffdetails, cleans, moves to Staffdetails2, repopulates Stafftables yada yada, although there doesn't seem to be a corresponding job scheduled that would run it periodically - so I don't know even how, let alone when, it runs.
So the issues are,
1) whether a job is running against this database that finds the user is 'invalid' somehow and deletes here,
2) if so when it's running?
3) if not, is it possible to schedule a DTS package to execute periodically without a job?
4) if so, how do I check for sure whether it's been running?
Thanks in advance for your help,
Jaybee.
-
No I'd assume that there's a job linked to that dts.
Check out the jobs and try to find the string dtsrun with a whole bunch of numbers... that'll be the link the the dts id.
-
Hi Ninj, happy new year!
What's this "dTSRUN" string and where do I find it? Never heard of it, but sounds like a promising lead!
Cheers,
Jaybee.
-
Ya create a new DTS, save it. Then in EM / Local packages right click on the new package, schedule... Create any new schedule for the package and save. You'll see what you are looking for.
-
One step better. This should lead you directly to the packages using dtsrun :
Select J.Name As Job_Name, Step_name, * from msdb.dbo.SysJobs J inner join msdb.dbo.sysjobsteps JS ON J.job_id = JS.job_id where js.command like '%dtsrun%'
-
If you're alowed, you could also quickly create a delete-trigger to log all deletes from that table (maybe even narrowing the rows to the ones you want to) into a audit-table. There you could e.g. also collect the connection's properties,...
just keep in mind this trigger is "in transaction", if the trigger fails, the delete fails !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
you try deny delete on the table and wait for error to occur - depends upon your level of authority of course, but this would be one of my routes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
You might also want to check you don't have implicit transactions on when you do your insert ...
-
Well - the good news is that it turned out to be an AD issue (although the boys seem to feel I'm a SQL whizz - who am I to disillusion them?!?!?)
The lady has resurfaced, apparently the 'distinguishednames' column is monitored for those users who have 'expired' attributes, and she was one of them.
Still, there are a lot of DB's I've inherited without docu in this contract from the previous manager, and the above steps will inevitably prove helpful.
Kudos to all,
Jaybee.
-
Just for future reference. You could also have put a delete trigger on the table and written out the deleted data and the time it was deleted. That helps in figuring out when the changes happen. The other thing is with the dtsrun utility it can be a job scheduled on a machine outside of the sql server. Makes it a little tougher to track down.
Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply