For context we have a huge MS Access environment and we are trying to get rid of it. We have removed the previous data table locations and provided an SQL DB for them to connect to instead. One of the goals of this is to find out who in the organization is using what tables so that we can get a picture of the breadth of the issue. This is why I am trying to audit.
The audit is set up to look for any SELECT statements run against all tables in the DB. The audit works with one issue. It is creating an entry for every execution from the SELECT. So if the SELECT returns 1000 results there are 1000 entries in the log. Now from what I understand this is working as intended.
Now what I think is happening that because the queries are originating from Access it is doing the results one at a time causing this issue. In profiler the Access queries look like this before they start:
declare u/p1 int
set u/p1=8
exec sp_prepexec u/p1 output,N'@P1 varchar(4),@P2 varchar(16)',N'SELECT "something" FROM "something" WHERE ("stuff" = u/P2)','things'
select u/p1
With that being said is there a specification or filter I can add to the audit that will just get the declaration and not all the executes? Or is there a way to change the behavior altogether so it is only creating one entry?
Well, if you execute a query to pull 10 rows, or 10,000 rows, you most certainly don't get 10,000 rows or entries in the profiler.
So, say a sql query pulls 3,000 rows, then a profiler will say show this:
So, about 10 rows can get generated, and that is the case if the query returns 2 rows, or 10,000 rows. So, I'm not aware that for "each row" that the profiler returns excess data, only that the profiler tends to produce a lot of chatter.
To be fair, Access is quite "chatty".
However, I would NOT turn on ODBC logging, since that not only produces excess data in the odbc log, it also tends to slow performance down SIGNIFICATLY, and thus you losing the benefits of moving your data from Access to SQL server.
So, it not clear if you using ODBC logging here, but I would recoomend that you don't setup connections from Access with ODBC logging turned on.
That is this dialog when you link a table from Access.
You don't quite mention what tools, or if you have the above ODBC logging turned on, but I not aware that "general" queries sent to the database produces ONE row for every record returned.
You might want to expand on what tools, and what kind of logging you setup.
It's possible you using a tool we not aware of but you can say export the profiler to a csv, and filter in Excel, or even better import such data into Access and use that to filer such data.