Microsoft® SQL Server® 2012 Bible

960

Part VII: Monitoring and Auditing

sys.fn_cdc_map_time_to_lsn

('largest less than or equal', '20121231');

SELECT __$start_lsn, __$seqval, __$operation,

__$update_mask, DepartmentID Name, GroupName, ModifiedDate

FROM cdc.fn_cdc_get_all_changes_HumanResources_Department

(@BeginLSN, @EndLSN, 'all')

ORDER BY __$start_lsn

Result:

__$start_lsn __$seqval __$operation

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

0x0000005400001D6E0008 0x0000005400001D6E0003 2

0x0000005400001D6E0008 0x0000005400001D6E0006 2

0x0000005400001D700007 0x0000005400001D700002 4

0x0000005400001D7D0008 0x0000005400001D7D0003 2

0x0000005400001D7D0008 0x0000005400001D7D0006 2

0x0000005400001D7F0004 0x0000005400001D7F0002 4

0x0000005400001D810005 0x0000005400001D810003 1

__$update_mask Name GroupName ModifiedDate

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

0x0F 17 SQL Rocks 2012-03-04 11:21:48.720

0x0F 18 CDC Rocks 2012-03-04 11:21:48.720

0x02 17 SQL Rocks 2012-03-04 11:21:48.720

0x0F 19 PBM Rocks 2012-03-04 11:21:55.387

0x0F 20 TVP Rocks 2012-03-04 11:21:55.387

0x04 18 T-SQL Rocks 2012-03-04 11:21:48.720

0x0F 20 TVP Rocks 2012-03-04 11:21:55.387

You can also pass the functions directly to the table’s CDC function. This is essentially the

same code as the previous query, but slightly simpler, which is usually a good thing:

SELECT *

FROM cdc.fn_cdc_get_all_changes_HumanResources_Department

(sys.fn_cdc_map_time_to_lsn

('smallest greater than or equal', '20120101'),

sys.fn_cdc_map_time_to_lsn

('largest less than or equal', '20121231'),

'all') as CDC

ORDER BY __$start_lsn

You can also convert an LSN directly to a time using the fn_cdc_map_lsn_to_time() func-

tion. The next query extends the previous query by returning the time of the transaction:

-- with lsn converted to time

SELECT

sys.fn_cdc_map_lsn_to_time(__$start_lsn)

c41.indd 960c41.indd 960 7/31/2012 10:17:22 AM7/31/2012 10:17:22 AM

http://www.it-ebooks.info