asked on

How to get the sql statement which is updating table?

Hi,

I would like to ask you maybe you could help me to find out how I can get what is updating a table in MS SQL 2000.

I have a table, and something is updating it based on some events, and I would like to know what is doing this.

Please if you could help.

thanks in advance

best regards,
csaba

Bardobrave

Try adding a text field to your table and making your sql sentences to insert themselves into this field when firing... then you'll see a record and it's inserting/updating query.

eesdil

ASKER

thanks a lot, but the problem is that I don't know what is updating, so I don't know which procedure is updating my table...

ASKER CERTIFIED SOLUTION

Bardobrave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eesdil

ASKER

see, doesn't seem promising :(

no, unfortunately i don't know what procedures I have (theoretically yes, but practically not) and where it is, i know just that something is updating my table...

thanks for the help

mdagis

Create a table named xTrack with two column: Creation Date(datetime) and ProcessName (varchar(200))

Then Go to your table and create a trigger like:

CREATE TRIGGER TR_MyTable ON dbo.MyTable
FOR UPDATE
AS

Insert into xTrack
select getdate(), app_name()

End

Bardobrave

And there is no way you can trace your proccess behaviour? Don't you have at least a clue?

You could use a trigger to alert you when an update is made, or you could deny all write permissions to this table, then when the shadow procedure try to update it will pop an error and you should be able to locate him.

eesdil

ASKER

thanks a lot guys

mdagis, thanks a lot, i have tried, but it was not showing the procedure name unfortunately

Bardobrave, trigger i tried, but i don't have the info on the accessing procedure.
Maybe the removing right can be a good idea. I will check it later.

Actually I have found the procedure, so not just theoretically but practically it was there :)
(found it based on the field which is updated however shouldn't be)
Now just I have to find out out why it is updating... :)

thanks a lot for the help
regards,
csaba

eesdil

ASKER

Somtimes updating statement cannot be find like this, but in my case it was possible as it was stored procedure.