The SQL Zone

18 May '12 - 22:00

Hi, How can i get the full record that is delete/Update via `exec sp_trace_setevent @TraceId` I mean: I have table with this columns `Id int ,Name nvarchar(max) ,Family nvarchar(max)` with the values: `1,jojoba,alin 2,babk,babyi` ... now i when i update like this: UPDATE tblName SET Name='okki', Family='koki' where Id=2 now I wanna this in my trace: `2,babk,babyi Deleted 2,ooki,koki Inserted`
Sign In·Permalink

19 May '12 - 0:42

Still working on that audit? Smile | :)

Stop wasting time, if your customer really needs the old values of the record, then you'd be best of with doing only inserts; somebody wants to update a record? Mark the current one as outdated (datetime) and insert a new one.

Yes, that'll make your database huge, but logging every action would result in yet even more data, since you're not only storing the new values (ignoring the old) - but also the command.

You best rethink your requirements here.

Bastard Programmer from Hell Suspicious | :suss:

19 May '12 - 4:38

thanks for your attention! but its the end of story. `is it possible to get that info via sp_trace_setevent .` i know that it is in ldf file. plz give the help to close the question.
Sign In·Permalink

19 May '12 - 5:05

jojoba2011 wrote:
is it possible to get that info via

Not as far as I can see.

MSDN[^] mentions:

Text value dependent on the event class captured in the trace. However, if you are tracing a parameterized query, the variables will not be displayed with data values in the TextData column.

jojoba2011 wrote:
i know that it is in ldf file

How did you know?

Bastard Programmer from Hell Suspicious | :suss:

20 May '12 - 2:42

Thanks for reply! `i have got the row update old value when i go to table and manually change the value` "via sp_trace_setevent (Transact-SQL)" but cannot get the old value when using SP for update. Plz help to get the old value when runing SP
Sign In·Permalink

20 May '12 - 3:03

A trace shows the commands written to the server; not the values that the server holds. A trace will not show the current values. Executing a SP with some values might even require different permissions than reading those values.

As said, this is the wrong approach. If your customers need the old and the new values, then you should not update or delete, just insert.

Bastard Programmer from Hell Suspicious | :suss:

20 May '12 - 17:38

I get Old Value and New Value u can see the red color part:OldName,OldFamily--->UpdatedName,UpdatedFamily
via this trace :

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 16/04/2009 12:29:20 */
/****************************************************/
 

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500 
declare @OutputFileName nvarchar(200)
declare @EndTime datetime
 
SET @OutputFileName = 'C:\STrace' + 
CONVERT(VARCHAR(20), GETDATE(),112) + 
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
 
SET @EndTime = DATEADD(mi,30,getdate())
 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTime 
if (@rc != 0) goto error
 
-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
 
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
 
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on
 

 
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
 

 
exec sp_trace_setevent @TraceID, 28, 1, @on
exec sp_trace_setevent @TraceID, 28, 6, @on
exec sp_trace_setevent @TraceID, 28, 9, @on
exec sp_trace_setevent @TraceID, 28, 10, @on
exec sp_trace_setevent @TraceID, 28, 11, @on
exec sp_trace_setevent @TraceID, 28, 12, @on
exec sp_trace_setevent @TraceID, 28, 13, @on
exec sp_trace_setevent @TraceID, 28, 14, @on
exec sp_trace_setevent @TraceID, 28, 16, @on
exec sp_trace_setevent @TraceID, 28, 17, @on
exec sp_trace_setevent @TraceID, 28, 18, @on
 

 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft Dynamics NAV client'
 
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
 
-- display trace id for future references
select TraceID=@TraceID
goto finish
 
error: 
select ErrorCode=@rc
 
finish: 
go

and the result is :i get this result when i manually change the item in SQL Management by hand .but i wanna to get this result when running SP.

[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK_tblNames',@_msparam_3=N'dbo',@_msparam_4=N'tblNames'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)exec sp_executesql N'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)',N'@Name nvarchar(11),@Family nvarchar(13),@Param1 int,@Param2 nvarchar(7),@Param3 nvarchar(9),@Param4 int',@Name=N'UpdatedName',@Family=N'UpdatedFamily',@Param1=2,@Param2=N'OldName',@Param3=N'OldFamily',@Param4=1select col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idselect col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idSET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'SELECT
db_name() AS [Database_Name],

21 May '12 - 4:30

If you run it manually, you'll see the text as sent to the server. A trace is meant to "trace" what commands are being executed. AFAIK, it wouldn't even remotely interested in the "current" values. Repeating the question will not change the answer. Bastard Programmer from Hell
Sign In·Permalink

21 May '12 - 20:11

thanks for you reply for both C# and Database pages. But isnt it possible to have when running update SP. Its very important to do that . if possible then its end of story. Q2)is it possible to have the OLD trace if the user delete the .trc file from SQL.
Sign In·Permalink