Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘fn_dnlog’

LSN Convert

Posted by Simon Cho on 01/29/2012

http://support.microsoft.com/kb/886839

CREATE FUNCTION dbo.fn_convertnumericlsntobinary(
@numericlsn numeric(25,0)
) returns binary(10)
AS
BEGIN
-- Declare components to be one step larger than the intended type
-- to avoid sign overflow problems. For example, convert(smallint, convert(numeric(25,0),65535)) will fail but convert(binary(2),
-- convert(int,convert(numeric(25,0),65535))) will give the
-- intended result of 0xffff.
declare @high4bytelsncomponent bigint,
@mid4bytelsncomponent bigint,
@low2bytelsncomponent int
select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000
select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000
select @low2bytelsncomponent = convert(int, @numericlsn)
return convert(binary(4), @high4bytelsncomponent) +
convert(binary(4), @mid4bytelsncomponent) +
convert(binary(2), @low2bytelsncomponent)
END

Posted in Common | Tagged: , , | 3 Comments »