August 19, 2008 at 1:03 pm
Right now I am working on a very messed up Access DB frontend for data-entry/reports what have you going to a SQL server. I want to capture all queries to see what data is being updated, selected, deleted, inserted, and stored procedures executed (with what params?). I have a trace running, showing RPC:Starting, SQL: Batch Starting, StmtStarting events, with a filter on Application Name like '%Access%'. However, I get alot of lines like: exec sp_execute 1,123, 2344, 2555, 3555,3666, etc, sp_unprepare 2, Select Config, nvalue FROM Msysconf, If @@TRANCOUNT > 0 COMMIT TRAN and other oddball queries that is filling up way to much space...
I would like to filter the messages to only the database I am working on (the seemingly real sql queries don't have the DatabaseID listed?), show only queries based on user tables, and show any user created stored procs being called. Is there an easy way to get this? I would like to run it for a couple days to see the most used queries without having to dig through 50 access forms, buttons, reports, etc...
Am I even using the right tool for this?
August 19, 2008 at 1:25 pm
Profiler is the right tool for the job.
You need to add either the DatabaseID or DatabaseName column to your Profiler output, then you can filter on it. I prefer to use DatabaseID. You can get that for your database by running Select DB_ID([dbname]).
Also, if you already have a trace running and want to examine it later you can save it to a file and then apply filters on the columns you have in the trace.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply