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
eesdilAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BardobraveCommented:
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.
0
eesdilAuthor Commented:
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...
0
BardobraveCommented:
As far as I know you can't capture wich proccess is updating you DB from SQL SERVER (although I think ORACLE allow it in some way...). So probably the best option is to identify the possible sources by forcing them to let a trace.

So you don't know wich procedure is, but you know your procedures and can add them code to save in the register a message saying "eh, it's me!".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

eesdilAuthor Commented:
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
0
mdagisCommented:
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
0
BardobraveCommented:
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.
0
eesdilAuthor Commented:
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
0
eesdilAuthor Commented:
Somtimes updating statement cannot be find like this, but in my case it was possible as it was stored procedure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.