tracing sql from code

[foldface@yahoo.co.uk]

Hi
I want to trace all the selects/deletes/modifys whatever on a database
in an application that are performed in a seperate application.
I need to look into this, any ideas?

- Can triggers do this kind of thing
- Can you somehow access the profiler via OLE or similiar to do this?
- Anything else?

Ta
F

[Erland Sommarskog]

Re: tracing sql from code

(foldface@yahoo .co.uk) writes:[color=blue]
> I want to trace all the selects/deletes/modifys whatever on a database
> in an application that are performed in a seperate application.
> I need to look into this, any ideas?
>
> - Can triggers do this kind of thing
> - Can you somehow access the profiler via OLE or similiar to do this?
> - Anything else?[/color]

It would probably help if you told us what you want to achieve with this.
If you are into auditing, check out Entegra from Lumgient
(www.lumigent.com.)

If you want to write your Profiler-sort-of-thing, you can use server-side
traces. Look up the sp_trace_xxxx procedures in Books Online.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[foldface@yahoo.co.uk]

Re: tracing sql from code

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns946DEF C87C2C6Yazorman @127.0.0.1>...[color=blue]
> (foldface@yahoo .co.uk) writes:[color=green]
> > I want to trace all the selects/deletes/modifys whatever on a database
> > in an application that are performed in a seperate application.
> > I need to look into this, any ideas?
> >
> > - Can triggers do this kind of thing
> > - Can you somehow access the profiler via OLE or similiar to do this?
> > - Anything else?[/color]
>
> It would probably help if you told us what you want to achieve with this.
> If you are into auditing, check out Entegra from Lumgient
> (www.lumigent.com.)
>
> If you want to write your Profiler-sort-of-thing, you can use server-side
> traces. Look up the sp_trace_xxxx procedures in Books Online.[/color]

Its my my own debugging use. I've got a little utility that shows a list
of tables and the records of that table in a DataGrid on the right. I would
like to be able to press a button on that tool, do some database stuff,
then press some other button and see all the tables that have been modified
turn red.
Again, this is just for my own debugging use and I can completely reck the
database if need-be so anything goes.

Thanks for the response, the sp_trace_xxx stuff sounds like what I need. As
I'm new to (relational) databases it would be nice to know what else is
out there that can do this kind of thing. Can you make the database call
a program via some kind of Observer pattern, something else?
I only just learnt about the sysobjects table the other day and thats proved
to be dead useful.

[Erland Sommarskog]

Re: tracing sql from code

(foldface@yahoo .co.uk) writes:[color=blue]
> Thanks for the response, the sp_trace_xxx stuff sounds like what I
> need. As I'm new to (relational) databases it would be nice to know what
> else is out there that can do this kind of thing. Can you make the
> database call a program via some kind of Observer pattern, something
> else?[/color]

You can call external code in two ways: OLE objects or extended stored
procedures (XP). Writing your own XPs is certainly an advanced feature,
and requires C/C++ skills. Once they are there, calling them is more
or less like calling any other SP. To talk with OLE objects you use
the system procedures on the family sp_OAxxxx.

But I cannot see than any of these has any place in what you want to do
for debugging.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[foldface@yahoo.co.uk]

Re: tracing sql from code

> You can call external code in two ways: OLE objects or extended stored[color=blue]
> procedures (XP). Writing your own XPs is certainly an advanced feature,
> and requires C/C++ skills.[/color]

c#? Doing mainly web stuff at the mo but can do Windows Forms stuff as well
[color=blue]
> Once they are there, calling them is more
> or less like calling any other SP. To talk with OLE objects you use
> the system procedures on the family sp_OAxxxx.
>
> But I cannot see than any of these has any place in what you want to do
> for debugging.[/color]

just thinking from an mvc type of view, table changes, view is informed.
Probably not the way to go though.

[Erland Sommarskog]

Re: tracing sql from code

(foldface@yahoo .co.uk) writes:
[color=blue][color=green]
>> You can call external code in two ways: OLE objects or extended stored
>> procedures (XP). Writing your own XPs is certainly an advanced feature,
>> and requires C/C++ skills.[/color]
>
> c#? Doing mainly web stuff at the mo but can do Windows Forms stuff as
> well[/color]

Don't know if you can write unmanaged code in C#, but if you can maybe.
But you are certainly not in the sheltered realms of the CLR, but you
are responsible for keeping track of your pointers yourself. And this is
even more nervous in the context of an extended stored procedure. Tt's
not only your small little DLL that perishes in case of an access
violation - you will bring the entire SQL Server with you in the fall.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp