Prev: Error Sorting Strings
Next: partition wizard
From: SnapDive on 25 May 2010 15:09

I have a table with a varchar column and datetimeoffset column.
SQL Server 2008 (not R2) Change Tracking is enabled on the database
and the table and column-tracking is enabled.

On one DB connection, I inserted 3 rows.
On a different connection, I updated 1 row's varchar and
datetimeoffset.

I want to know which row was updated so I can take some action. The
below SQL shows all 3 rows as "updated', I expected I guess 3 inserts
and 1 update.

SELECT
CT.ItemsId, P.Stamp, P.ItemName,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
Items P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES Items , 0) AS CT
ON
P.ItemsId = CT.ItemsId


help?
From: Erland Sommarskog on 26 May 2010 17:45
SnapDive (SnapDive(a)community.nospam) writes:
> I have a table with a varchar column and datetimeoffset column.
> SQL Server 2008 (not R2) Change Tracking is enabled on the database
> and the table and column-tracking is enabled.
>
> On one DB connection, I inserted 3 rows.
> On a different connection, I updated 1 row's varchar and
> datetimeoffset.
>
> I want to know which row was updated so I can take some action. The
> below SQL shows all 3 rows as "updated', I expected I guess 3 inserts
> and 1 update.
>
> SELECT
> CT.ItemsId, P.Stamp, P.ItemName,
> CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
> CT.SYS_CHANGE_CONTEXT
> FROM
> Items P
> RIGHT OUTER JOIN
> CHANGETABLE(CHANGES Items , 0) AS CT
> ON
> P.ItemsId = CT.ItemsId

When I run the below (even if put the UPDATE statement in a second
window), all rows display as I. Maybe you could develop a similar
script that demonstrates the issue you are seeing?

CREATE TABLE mytable (a int NOT NULL PRIMARY KEY,
b int NOT NULL,
c int NOT NULL)
alter table mytable enable change_tracking
with (TRACK_COLUMNS_UPDATED = ON)
go
insert mytable (a, b, c)
values (1, 2, 3),
(4,6,7),
(8,9,19)
go
update mytable
set b = 188
where a = 1
go
SELECT
CT.a, P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
mytable P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES mytable , 0) AS CT
ON
P.a = CT.a
go
drop table mytable


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: SnapDive on 2 Jun 2010 09:48
I had an external-forces issue happening that was throwing things off.
Thanks for the help!


On Wed, 26 May 2010 23:45:17 +0200, Erland Sommarskog
<esquel(a)sommarskog.se> wrote:



>When I run the below (even if put the UPDATE statement in a second
>window), all rows display as I. Maybe you could develop a similar
>script that demonstrates the issue you are seeing?
>
>CREATE TABLE mytable (a int NOT NULL PRIMARY KEY,
> b int NOT NULL,
> c int NOT NULL)
>alter table mytable enable change_tracking
> with (TRACK_COLUMNS_UPDATED = ON)

 | 
Pages: 1
Prev: Error Sorting Strings
Next: partition wizard