Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2011-04-19 : 07:37:24
|
| When it comes to SQL I am a real Novice so please bear with me as I try to explain things and when replying please use very novice explainations. Thanks.We are running MS Sql 2005. I have a table called PROD_DATE. In this table are two columns. One named New_date and one named Old_date. In the application that I am running I have two queries that affect these columns in this table. One query is self triggered at 13:30 and the other is triggered at 4:30. yes this is 24hr time listed to make it easier for all to understand. Anyway in the application I have audit logs that show what query was ran, when it was ran, who ran it, etc.. A user can not alter these self ran queries.Anyway the problem has started a week ago monday. The problem is for some reason the column Old_date is updated between 5:00 and 5:15. Never the same time. So there is something somewhere that is causing this update. I have been thru my code over and over again. I had another programmer look at my code. Everything seems good with code. The two self triggered queries are running at their respective times and doing exactly what they are desinged to do.So my big question is what can I do to find out what is causing the column named Old_date to change between the time listed above? Is there some sort of log or something in SQL Manager Studio where I can look and find out what is causing this change to occur?I dont know what occured or what could have occured because up until a week ago monday everything was working fine. Welcoming any and all comments. Thanks and have a great day. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-19 : 07:42:42
|
| You can use the profiler to record everything that alters the table or create a trigger to log the system_user, host and command executed.You could also use a trigger to reject any updates to that value between those times and see what fails.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-04-19 : 07:42:57
|
The first thing I would do is to have a look in sql server agent and see if there is a job (or some jobs?) running at this time.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2011-04-19 : 12:20:50
|
| I am in the process of learning about profiler. I am also looking at the trigger items. As I said I am a real novice in this area.I was wondering if someone could post a sample script of a trigger that would lock a table at a given time. I am unfamiliar with both items but feel that at least I have profiler running correctly. Only think is it appears to be watching all transactions not just the one table. |
|
|
|
|
|
|
Please Help