2

In my Sql Server 2012 I have the following table in a database

CREATE TABLE [dbo].[HumanResource](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IsHired] [bit] NULL,
    [Address] [nvarchar](max) NULL,
    [Score] [bigint] NULL,
    [LastUpdate] [datetime] NULL,
 CONSTRAINT [PK_HumanResource] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

when I update the Address field (nvarchar, not null), it is captured as below

NULL 3 2 mahmood True here 111 2014-09-01 14:42:28.913
NULL 4 2 mahmood True there 111 2014-09-01 14:42:28.913

but when I update the Score field (nvarchar, null), this is captured

NULL 3 2 mahmood True NULL 111 2014-09-01 14:42:28.913
NULL 4 2 Mahmood True there 111 2014-09-01 14:42:28.913


**Edit (the update and select queries) :

update HumanResource set Address = 'blah blah blah' where id = 2
update HumanResource set Score = Score + 1 where id = 2

SELECT        TOP (10000) __$start_lsn, __$end_lsn, __$seqval, __$operation,  
__$update_mask, ID, Name, IsHired, Address, Score, LastUpdate
FROM            cdc.dbo_HumanResource_CT

enter image description here

Considering that sql server Update is a chained Delete and Insert, the first row is the delete row capture and the second one is the insert row capture.

But I don't understand why the address is tracked as NULL when I update other fields, I would appreciate any information on this issue.

2
  • 1
    (1) can you show the UPDATE statements instead of describing them? (2) how are you determining "first" and "second"? What query are you using to pull the CDC data? Does it have an ORDER BY? Sep 1, 2014 at 14:05
  • @AaronBertrand please check the updated question. Sep 2, 2014 at 8:11

1 Answer 1

6

Here is the MSDN

Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the Configure the max text repl size Server Configuration Option to specify a larger maximum size. For more information, see Configure the max text repl size Server Configuration Option.

You are using a nvarchar(max). I've got the same problem. I fix it by changing my varchar(max) with varchar(255). For an address 255 should be enough.

Hope this help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.