March 16, 2009 at 12:18 am
Hi team,
I am in development of a Statistical data based reports retrieval application and i need to secure the data and also to maintain the log for each and every transaction.
This is a multi-user application.
Technology involved here is VB.Net, MS-Sql Server 2005, Crystal Reports & SSRS
End user can work in any system along the WAN. So i need to track who are all worked in the application and what are all done by them.
So i used the below query to view the transaction.
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
Its used to retrieve the DML statements executed against the DB.
But i couldn't able to get the Update query properly.
Table schema is specified below
ColumnName DataType
c1 int
c2 varchar
c3 int
Update Tst set c2 = 'Test'
Here i could n't able to get the Exact update query which i got executed..
It came like below
(@1 varchar(8000))UPDATE [Tst] set [c2] = @1
How to resolve this? May i able to retrieve the exact detail from the sql server transaction log?
If so.. Is it possible to get the Client machine details too(From where it is executed)?
Doubt: Is it possible to get the DDL statements log?
Please send me your comments and solution ASAP?
Regards
Ramkumar.K
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
March 16, 2009 at 1:00 am
Hey ..
Your query is correct for selecting text but that text must be parsed properly to mean anything please look at the query below:
[font="Courier New"] SELECT deqs.last_execution_time AS [Time],
SUBSTRING(dest.TEXT,
(deqs.statement_start_offset/2)+1,((CASE deqs.statement_end_offset WHEN -1 THEN
DATALENGTH(dest.TEXT)
ELSE
deqs.statement_end_offset
END - deqs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC[/font]
For updating I am not sure what you are trying to do but you got the general syntax right; but since you are strictly auditing from this table it might be better to do just an insert? Please note these DMV query plans can be phased out if they have not been used in a while.
As for DDL you can track them using DDL triggers; with performance impact so wonder consider how much you want to audit.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 16, 2009 at 6:19 am
The query was resolved through auto-parameterization because it was a trivial plan. A trivial plan is not kept in cache because there is almost no generation cost for them. That's why you can't see it in a query against the DMV.
The only way to capture this information is to have a server side trace enabled (you can build them through Profiler, but don't run them through Profiler).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply