March 15, 2013 at 1:11 pm
This is my second post asking this question. Hopefully someone will respond this time. I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'AVA',
@role_name = NULL
GO
-- UPDATE A ROW IN THE AVA TABLE FROM SSMS
UPDATE AVA SET AvaDesc = 'Test3' WHERE AvaKey = 119
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 3 (BEFORE UPDATE) AND 4 (AFTER UPDATE) CORRECTLY
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
GO
CREATE PROCEDURE [dbo].[spdv_AVAUpdate]
@AVAAStvpAVA READONLY-- table valued parameter
AS
DECLARE@ErrLogIDAS INTEGER
BEGIN TRY
SET NOCOUNT ON
BEGIN TRANSACTION
UPDATEAVA SET
AVA.AvaDesc= TVP.AvaDesc,
AVA.AvaArrKey = TVP.AvaArrKey
FROM@AVA TVP
INNER JOIN
AVA AVA ON (AVA.AvaKey = TVP.AvaKey)
-- Commit the transaction
COMMIT TRANSACTION
-- Return '' for success
SELECT '' AS ErrMessage
END TRY
BEGIN CATCH
-- Roll back any active or uncommittable transactions
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- Log the error into the ErrorLog table
EXECUTE dbo.uspLogError @ErrLogID = @ErrLogID OUTPUT
-- Retrieve logged error information.
SELECTdbo.ErrorLog.*
FROMdbo.ErrorLog
WHEREErrLogID = @ErrLogID
END CATCH
GO
GRANT EXECUTE on dbo.spdv_AVAUpdate TO public
GO
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Does anyone know why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
Thanks,
Gina
March 15, 2013 at 2:04 pm
How about this, comparing apples to oranges. Your manual UPDATE is not the same as the update done in the procedure. Try using the same update for both tests and see what you get.
March 15, 2013 at 3:21 pm
Thanks for the reply, Lynn. I'm not sure how to replicate the table valued parameter, but I executed the following from SSMS:
BEGIN TRY
SET NOCOUNT ON
BEGIN TRANSACTION
UPDATEAVA SET
AVA.AvaDesc= TVP.AvaDesc,
AVA.AvaArrKey = TVP.AvaArrKey
FROM(SELECT 119AvaKey,
'Test5'AvaDesc,
6AvaArrKey) TVP
INNER JOIN
AVA AVA ON (AVA.AvaKey = TVP.AvaKey)
-- Commit the transaction
COMMIT TRANSACTION
-- Return '' for success
SELECT '' AS ErrMessage
END TRY
BEGIN CATCH
-- Roll back any active or uncommittable transactions
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
---- Log the error into the ErrorLog table
-- EXECUTE dbo.uspLogError @ErrLogID = @ErrLogID OUTPUT
-- -- Retrieve logged error information.
--SELECTdbo.ErrorLog.*
--FROMdbo.ErrorLog
--WHEREErrLogID = @ErrLogID
END CATCH
And got the correct results:
__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
0x0031E853000001D20008NULL0x0031E853000001D2000230x02119Test36NULL
0x0031E853000001D20008NULL0x0031E853000001D2000240x02119Test56NULL
So I'm still unsure why I'm having the problem when our application calls the stored procedure.
March 15, 2013 at 3:26 pm
For certain UPDATE statements, SQL must instead actually do a DELETE and an INSERT instead of an UPDATE. So, the result is normal for SQL.
If you need to see it as an "UPDATE" when you report on the CDC table, I think you'll have to add logic to "recombine" them yourself.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 3, 2013 at 1:01 pm
Lynn- Can you me little more detailed information?
June 3, 2013 at 4:15 pm
ajp3066 (6/3/2013)
Lynn- Can you me little more detailed information?
Not sure what you are asking for here.
September 23, 2015 at 1:49 pm
September 23, 2015 at 6:20 pm
Christophe.Bejjani (9/23/2015)
That might help
Great links. Thanks and welcome aboard.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply