Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

GO-SQL blog A blog dedicated for SQL Server Developers alike!

My name is Andrew Fenna, and I am a database developer at The Laterooms Group. I have written this blog to educate my fellow database developers in subjects that I think are necessary to keep at the top of our game. I have been working with RDBMS’s since 2002 and I have worked within financial, gaming, public, and travel sectors within the Northwest of England. I have gained quite a lot of experience throughout my journey and have tackled some very difficult challenges along the way. Therefore, I am blogging my experiences, which I hope will give you the inspiration to try and tackle problems that are deemed impossible to start, but with some encouragement and guidance become much more simpler and structured, thank you for your time. Andrew J Fenna.

CDC changes column-by-column instead of row-by-row, can you improve it???

I’m writing a table value function that returns a cdc recordset that, instead of returning changed data row-by-row, I need to output them column-by-column.

Say for example I have a table called auth.Account with cdc enabled on fields Email and Password. e.g.

EXEC sys.sp_cdc_enable_table @source_schema = N’auth’,
@source_name = N’Account’, @captured_column_list = N’
Email,
Password
@supports_net_changes = 1;

Right, now cdc is enabled, i need my recordset to look like this:-

ID, CDC_OPERATION, LSN, CurrentEmail, PreviousEmail, CurrentPassword, PreviousPassword

This is my solution so far, yep I know its a bit messy and could be improved, any ideas on how I could improve this? Thanks AJF

– =========================================
– Author: Andrew Fenna
– Create date: 2013-08-15
– Description: Get Changed Auth Details TVF
– =========================================
ALTER FUNCTION [auth].[ChangedAuthDetails] ( @FromLsn BINARY(10) )
RETURNS @User TABLE
(
UserID INT ,
CDC_OPERATION CHAR(1) ,
LSN BINARY(10) ,
CurrentEmail NVARCHAR(255) ,
PreviousEmail NVARCHAR(255) ,
CurrentPassword NVARCHAR(32) ,
PreviousPassword NVARCHAR(32)
)
AS
BEGIN

DECLARE @ToLsn BINARY(10)

IF ( @FromLsn IS NULL )
SELECT @FromLsn = sys.fn_cdc_get_min_lsn(N’auth_account’)
ELSE
SELECT @FromLsn = sys.fn_cdc_increment_lsn(@FromLsn)

SELECT @ToLsn = sys.fn_cdc_get_max_lsn()

IF ( @FromLsn = sys.fn_cdc_increment_lsn(@ToLsn) )
RETURN

– Query for change data
INSERT INTO @User
SELECT a.UserID ,
‘U’ AS [__$operation] ,
a.[__$start_lsn] ,
A.CurrentEmail ,
a.PreviousEmail ,
b.CurrentPassword ,
b.PreviousPassword
FROM ( SELECT Email.[__$start_lsn] ,
Email.ID AS [UserID] ,
Email.[3] AS [PreviousEmail] ,
Email.[4] AS [CurrentEmail]
FROM ( SELECT [__$start_lsn] ,
ID ,
Email ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX(Email) FOR [__$operation] IN ( [3], [4] ) ) AS Email
) A
CROSS APPLY ( SELECT [password].[__$start_lsn] ,
[Password].[3] AS [PreviousPassword] ,
[Password].[4] AS [CurrentPassword]
FROM ( SELECT [__$start_lsn] ,
ID ,
[Password] ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX([Password]) FOR [__$operation] IN ( [3], [4] ) ) AS [Password]
) B
WHERE A.__$start_lsn = B.__$start_lsn
UNION
SELECT [ID] AS [UserID] ,
CASE __$operation
WHEN 1 THEN ‘D’
WHEN 2 THEN ‘I’
ELSE NULL
END AS CDC_OPERATION,
[__$start_lsn] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN Email
END AS [CurrentEmail] ,
CASE [__$operation]
WHEN 1 THEN Email
WHEN 2 THEN NULL
END AS [PreviousEmail] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN [Password]
END AS [CurrentPassword] ,
CASE [__$operation]
WHEN 1 THEN [Password]
WHEN 2 THEN NULL
END AS [PreviousPassword]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] < 3
RETURN
END

Comments

Leave a comment on the original post [gosql.co.uk, opens in a new window]

Loading comments...