|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Monday, February 13, 2012 5:49 AM
Points: 368,
Visits: 159
|
|
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....
######################
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, September 21, 2011 1:19 PM
Points: 933,
Visits: 1,016
|
|
Hey ..
Your query is correct for selecting text but that text must be parsed properly to mean anything please look at the query below:
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
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.
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. 
How to ask for help .. Read Best Practices here.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 11,930,
Visits: 22,927
|
|
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
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
