capturing sql

Question

capturing sql

iain lastname

SSC Eights!

Points: 912
More actions
May 11, 2006 at 3:39 am

#113427

I've got a table that keeps getting duplicate entries added on to the end of it. It should be an almost 10% static table, but every few days I see another 8 or so rows added.
I set up a trigger to capture the username and date, but it only shows my account. The server isn't administered directly by me because it's a hosted server for a website.
Is there any way to capture the sql statement being used to add the rows, perhaps by adding something to the trigger? I can't use profiler.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

It depends if you can use SQL Server Client Tools with this SQL Server. If you can, than use SQL Profiler or set up a server trace for a day or two.

Regards,Yelena Varsha

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

What does your trigger look like? If it is set properly there should be no way to add new rows without capturing this.

iain lastname SSC Eights! Points: 912 More actions · Answer 3

Unfortunately, I can't use profiler as I'm not a sysadmin and I don't have ALTER TRACE.

create Trigger ChangedDevelopments_trig on dbo.Developments

for insert, update

as

insert into dbo.Audits

(userid, dated )

select SYSTEM_USER, getdate( )

from Inserted

there were some more added after I posted, all the results in the dbo.Audits table say it's my account doing the inserts. So either

a. I'm somehow triggering a process that inserts records

b. Someone else is inserting records or triggering a process that inserts records

c. Some other process is doing it.

I'm going to move webhosts in the next couple of weeks because they've become really unhelpful recently, so I can't rely on them to help. If I can get the code that does the inserts then perhaps I can track down who/what is doing it?

Mark Yelton SSCommitted Points: 1570 More actions · Answer 4

Since you've indicated you created a trigger to track this down, my suspicion is you have a trigger else where that is doing this from a different table. The tangled web that can develop when using triggers can get very big and make it very difficult to track activity on tables. I would get a list together of all your triggers and review the code to see if that's the problem. Personally, I avoid triggers as much as possible and only use them when there's no other way to solve the task at hand. I also make them very small in scope as to what they do, one function/purpose; that way I don't have to track a spaghetti situation. Just my humble opinion.

HTH,

Mark

iain lastname SSC Eights! Points: 912 More actions · Answer 5

I take your point on board, but I don't usually use any triggers. But just to check I ran a script to check for all the triggers in the database and I only have 1 other, and I checked it's code and it's not doing anything connected to this at all.

Thanks for trying to help though, I need it!

Mark Yelton SSCommitted Points: 1570 More actions · Answer 6

The only other things I can think of to check would be

1. Automated Jobs and their code

2. DTS Packages

3. Linked servers and any processes they may be running against your server

Other than that, yo no se mang!

Mark