Sql Server 2008 Profiler

Hi I want to see only all the transactions on tables for insert,select, update, delete, storeprocedure on a particular database with all the columns and values, tablename, etc

should i use the Sql server 2008 profiler.and how do i configure it for above the SQL

or any code.. which can appreciated..
bsarahimAsked:
Who is Participating?
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.

rajvjaCommented:
Hi

   In the sql server profile window, File->Templates->New templae

EventsSelection tab, Column filters...

you can set all filters required
0
bsarahimAuthor Commented:
how to set a column filter for all the above and

any external interface or a program for easy viewing.. thanks
0
jogosCommented:
Not sure if it's a ocasional attempt, to filter for a certain database see http://www.developer.com/db/article.php/3482216/Introduction-to-SQL-Profiler.htm
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

keyuCommented:
0
bsarahimAuthor Commented:
still the comments are very basic.. looking for a full solution

on tables for insert,select, update, delete,  with filter option.. if anybody can guide me to set the options for one database..

or any other auditing log software?
0
jogosCommented:
For occasional monitoring you do it by the profiler interface. It's not mentioned to be an 'auditing'-monitoring you want.

As shown in the link I posted earlier you can select a template (after mondification you can store it as your own template), the standard will do.
Then you have to select the events, the standard template will do for you. For your sql-code to monitor it's about the events 'RPC:Completed' and 'SQL:BatchCompleted' for the level of calling a stored procedure. If you want in detail the steps in that procedure addidional SP:Completed has to be selected
You want to see what is executed , that you can find in the column 'TextData', which is already selected in the standard template. Columns that are intresting to add to the standard template are: DatabaseID and HostName (from where is sql comming).

Filtering there is your question a little blur.
Filtering on database is shown in the link, that is filtering on a column of the data that Profiler has in its resultgrid.
You can also filter with a like statement on the TextData-field. But filtering in the sql text on which object is not so easy like '%Person%'  for a table-name  looks fine but gives you a lot more than you want to and also a lot less when you work with stored procedures (and no detail selected) and certainly when its by use of views and triggers.

Remember tracing has an impact on performance of the server.
If its more an auditing thing read http://www.appsecinc.com/presentations/Security_Auditing_MSSQL.pdf
and http://www.codeproject.com/KB/database/AuditsOnMSSQLServer.aspx
0
bsarahimAuthor Commented:
how to configure in the textdata filter i tired
%insert%Update%Select%

but it is not working

0
jogosCommented:
No that means  that only textdata with  in order the words insert, update and select must be presend with inbetween 0 to unspecified other characters (%) and .
You cannot do that in one LIKE-statement

0
bsarahimAuthor Commented:
you mean to say the textdaa should include the below

insert0select0update

0
bsarahimAuthor Commented:
any help will be appreciated..
0
jogosCommented:
I you filter on LIKE '%insert%Update%Select%'
It must contain those 3 words in that order, but if you want to filter on those 3 statements you also want truncate, merge, delete....  so most of the action on that database.

For more help you best can help us to know what you want. I repeat some phrases from my previous comments.

"Not sure if it's a ocasional attempt,
For occasional monitoring you do it by the profiler interface. It's not mentioned to be an 'auditing'-monitoring you want.
Filtering there is your question a little blur.
Remember tracing has an impact on performance of the server.
If its more an auditing thing read ....
"


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
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 2008

From novice to tech pro — start learning today.

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.