SQL server 2012 CDC operation code for update are not correct

Question

Post reply

SQL server 2012 CDC operation code for update are not correct

ashish.shevale

Ten Centuries

Points: 1177
More actions
August 7, 2013 at 12:51 am

#276731

Hi
Has anyone tried CDC on SQL server 2012
The operation code for update is created as 1 and 2 but it should be 3 and 4
Any guess
Ashish Shevale
------------------------------------------------------------------------------------
Ashish

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

I would assume that this happens when an UPDATE is not implemented as an in-place update, but as INSERT + DELETE.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ashish.shevale Ten Centuries Points: 1177 More actions · Answer 2

but I am executing a normal update query on one column

For insert and delete the values are correct

problem is only with the update

The setup was done as per msdn documentation havent messed it up

------------------------------------------------------------------------------------

Ashish

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

Yes, but SQL Server may internally implement that UPDATE as INSERT+DELETE.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 4

Doesnt the bolfor cdc specifically mention something about columns of type text and maybe image are nnot updated but are deleted and inserted? Does the thanle in question have those datatypes?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ashish.shevale Ten Centuries Points: 1177 More actions · Answer 5

Erland Sommarskog (8/7/2013)
Yes, but SQL Server may internally implement that UPDATE as INSERT+DELETE.

Yes it does that sometimes but as per microsoft it used to do it til SQL server 2005

http://support.microsoft.com/kb/238254/en-us#appliesto

does this still happen

Have you faced same scenario maybe for trigger or such

Lowell (8/7/2013)
Doesnt the bolfor cdc specifically mention something about columns of type text and maybe image are nnot updated but are deleted and inserted? Does the thanle in question have those datatypes?

Its integer column and the data is coming in correctly (change is getting tracked) but not as update query

------------------------------------------------------------------------------------

Ashish

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

ashish.shevale (8/7/2013)

does this still happen

Yes. Sometimes it is more efficient that way.

Have you faced same scenario maybe for trigger or such

I have not investigated it, but it should covered in Kalen Delaney's book. Since I don't use CDC, I can't say I care that much.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

rajborntodare Hall of Fame Points: 3576 More actions · Answer 7

ashish.shevale (8/7/2013)
Hi
Has anyone tried CDC on SQL server 2012
The operation code for update is created as 1 and 2 but it should be 3 and 4
Any guess
Ashish Shevale

Hi Ashish,

I had tried enabling CDC in 2012.

Please check the code i have used,

Enabling CDC on production server database

XXXXXXX,1111

/***

Database Name: xxxxxx

Steps enabling CDC:

Step 1:

Checking if CDC is enabled on the Database already:

***/

Use xxxxxx

Go

Select [name], database_id, is_CDC_enabled

From Sys.databases

Go

/****Gives the detail Description about the tables and CDC details.

Up on checking need to proceed with further step

Step 2:****/

Use xxxxxxxx

Go

Exec sys.sp_cdc_enable_db

Go

/***This Generate the creation of new schema,

Also creates a bunch of system tables ***/

Step 3:

"Enabling CDC on all Tables"

Use xxxxxxxxxx

Go

Select [name], is_tracked_by_cdc

from Sys.tables

GO

/***

Step 4:

Enabling on all tables individually:***/

Exec sys.sp_cdc_enable_table

@source_schema= N'dbo',

@source_Name= N'RB_Table_name',

@role_Name= null

Go

/*** Creates a new table in the System Tables in the name of

[cdc].[dbo_RB_Table_Name] Does Contain all the altered Details.

It’s same for entire Database and for all tables

$operation column is formed in the process of enabling CDC, helps to recognize what kind of changes are done to the table.

It represents in code from number 1 to 4

The number depicts the kind of change done to the data in the table;

1 - On displaying 1 it does show that a row has been deleted from the table.

2- On displaying 2 it does show that a row has been added to the table.

3- On displaying 3 it does show, the data has been modified and this is the pre-modified data.

4- It follows along with 3; the post-modified data is depicted by using operation 4.****/

These number represents the follwing

ashish.shevale Ten Centuries Points: 1177 More actions · Answer 8

rajborntodare (8/8/2013)
ashish.shevale (8/7/2013)
Hi
Has anyone tried CDC on SQL server 2012
The operation code for update is created as 1 and 2 but it should be 3 and 4
Any guess
Ashish Shevale
Hi Ashish,
I had tried enabling CDC in 2012.
Please check the code i have used,
Enabling CDC on production server database
XXXXXXX,1111
/***
Database Name: xxxxxx
Steps enabling CDC:
Step 1:
Checking if CDC is enabled on the Database already:
***/
Use xxxxxx
Go
Select [name], database_id, is_CDC_enabled
From Sys.databases
Go
/****Gives the detail Description about the tables and CDC details.
Up on checking need to proceed with further step
Step 2:****/
Use xxxxxxxx
Go
Exec sys.sp_cdc_enable_db
Go
/***This Generate the creation of new schema,
Also creates a bunch of system tables ***/
Step 3:
"Enabling CDC on all Tables"
Use xxxxxxxxxx
Go
Select [name], is_tracked_by_cdc
from Sys.tables
GO
/***
Step 4:
Enabling on all tables individually:***/
Exec sys.sp_cdc_enable_table
@source_schema= N'dbo',
@source_Name= N'RB_Table_name',
@role_Name= null
Go
/*** Creates a new table in the System Tables in the name of
[cdc].[dbo_RB_Table_Name] Does Contain all the altered Details.
It’s same for entire Database and for all tables
$operation column is formed in the process of enabling CDC, helps to recognize what kind of changes are done to the table.
It represents in code from number 1 to 4
The number depicts the kind of change done to the data in the table;
1 - On displaying 1 it does show that a row has been deleted from the table.
2- On displaying 2 it does show that a row has been added to the table.
3- On displaying 3 it does show, the data has been modified and this is the pre-modified data.
4- It follows along with 3; the post-modified data is depicted by using operation 4.****/
These number represents the follwing

Hi raj

did u tried updating the table

did it gave 3 and 4 in operation column

------------------------------------------------------------------------------------

Ashish

rajborntodare Hall of Fame Points: 3576 More actions · Answer 9

Hi raj
did u tried updating the table
did it gave 3 and 4 in operation column

Yes it do give me the 3 and 4

3 is the value or data before modification

4 is the value or data after modification

thanks,

Raj