.net sql query logging using SSMS Tools pack

Question

I have just installed SSMS tools pack from http://www.ssmstoolspack.com everything seems to work except I cant view queries from my .net application. If I run a query through management studion SSMS correctly displays the query. but when running my application no queries are shown in the local history window.

There is mention of enabling logging in the connection string but I can find no reference to this anywhere, the closest I got was QueryLog_On="yes" but I get an exception when I try to use it Unrecognized attribute 'QueryLog_On'.

Has anyone used SSMS and knows how to set up query logging for a .net application?

Answer 1

SSMS Toolpack isn't a general purpose query logger, it is a plug-in that caches a query history for the application into which it is "plugged" (SSMS in this case).

If this is a .NET application that you wrote, you will need to have the application to cache its own query history - maybe something as simple as a text box to which you append each query before you send it to the database server.

Even though the SSMS Toolkit won't do what you'd like, you can list recently executed queries from the dynamic management views, with the caveat that it will only list queries that haven't yet been flushed from the cache. Here is a sample that lists the last 100 queries that are still in cache - based on a query by Michelle Ufford:

SELECT TOP 100
      DB_NAME(dest.[dbid]) AS 'database_name'
    , OBJECT_NAME(dest.objectid, dest.[dbid]) AS 'object_name'
    , dest.text AS 'query_text'
    , MAX(deqs.last_execution_time) AS 'last_execution'
FROM sys.dm_exec_query_stats AS deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.[dbid])
    , OBJECT_NAME(dest.objectid, dest.[dbid])
    , dest.text
ORDER BY MAX(deqs.last_execution_time) DESC
OPTION (MaxDop 1);

Answer 2

anjlab's sql profiler sqlprofiler.googlepages.com does what I need. its quite powerful with good filtering so you can limit by user database etc