• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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
0
eesdil
Asked:
eesdil
  • 4
  • 3
1 Solution
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now