Click here to Skip to main content
15,903,540 members
Home / Discussions / Database
   

Database

 
AnswerRe: Error while declaring a cursor Pin
R. Giskard Reventlov22-May-12 6:23
R. Giskard Reventlov22-May-12 6:23 
QuestionHow to get delete/Update row + SQL Pin
jojoba201118-May-12 22:00
jojoba201118-May-12 22:00 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen19-May-12 0:42
professionalEddy Vluggen19-May-12 0:42 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201119-May-12 4:38
jojoba201119-May-12 4:38 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen19-May-12 5:05
professionalEddy Vluggen19-May-12 5:05 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201120-May-12 2:42
jojoba201120-May-12 2:42 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen20-May-12 3:03
professionalEddy Vluggen20-May-12 3:03 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201120-May-12 17:38
jojoba201120-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 :
SQL
/****************************************************/
/* 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 :<the red="" color="" item="" is="" the="" one="" that="" i="" wanna="">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],
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen21-May-12 4:30
professionalEddy Vluggen21-May-12 4:30 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201121-May-12 20:11
jojoba201121-May-12 20:11 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen22-May-12 0:52
professionalEddy Vluggen22-May-12 0:52 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201122-May-12 3:22
jojoba201122-May-12 3:22 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen22-May-12 8:50
professionalEddy Vluggen22-May-12 8:50 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201122-May-12 23:24
jojoba201122-May-12 23:24 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen23-May-12 0:16
professionalEddy Vluggen23-May-12 0:16 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201123-May-12 3:37
jojoba201123-May-12 3:37 
AnswerRe: How to get delete/Update row + SQL Pin
Eddy Vluggen23-May-12 8:23
professionalEddy Vluggen23-May-12 8:23 
AnswerRe: How to get delete/Update row + SQL Pin
Mycroft Holmes20-May-12 12:55
professionalMycroft Holmes20-May-12 12:55 
AnswerRe: How to get delete/Update row + SQL Pin
Corporal Agarn21-May-12 0:49
professionalCorporal Agarn21-May-12 0:49 
GeneralRe: How to get delete/Update row + SQL Pin
Eddy Vluggen21-May-12 3:45
professionalEddy Vluggen21-May-12 3:45 
GeneralRe: How to get delete/Update row + SQL Pin
Corporal Agarn21-May-12 3:51
professionalCorporal Agarn21-May-12 3:51 
GeneralRe: How to get delete/Update row + SQL Pin
Eddy Vluggen21-May-12 4:27
professionalEddy Vluggen21-May-12 4:27 
AnswerRe: How to get delete/Update row + SQL Pin
JohnPayton21-May-12 13:04
JohnPayton21-May-12 13:04 
QuestionRe: How to get delete/Update row + SQL Pin
jojoba201121-May-12 20:08
jojoba201121-May-12 20:08 
AnswerRe: How to get delete/Update row + SQL Pin
JohnPayton21-May-12 20:25
JohnPayton21-May-12 20:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.