Recently, I answered question about ‘Conn Id in Infomart?’ in SGGU(www.sggu.com) forum and referred to the link ‘Link ConnId In Infomart’. However, requester want to retrieve Conn ID directly from Genesys Infomart. As Conn Id field is stored in decimal format in Infomart database (GIM), it was not useful as T-Server log uses hexadecimal format.
In C#, you can simply call Int64.ToString(“X”) to convert integer into hexadecimal format but there were no inbuilt functions within SQL Server. I wrote SQL Server function to convert decimal to hex as below..
CREATE FUNCTION ConvertToBase ( @value AS BIGINT ) RETURNS VARCHAR(MAX) AS BEGIN -- some variables DECLARE @characters CHAR(36), @result VARCHAR(MAX), @base int; -- the encoding string SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz', @result = '', @base = 16; -- Convert it into hex WHILE @value > 0 SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result, @value = @value / @base; -- Prefix 0 set @result = N'0'+ @result; -- return our results RETURN @result; END
Above function is Patrick Caldwell blog post about converting decimals to base numbers and big thanks to him.
SQL query to get Conn ID from Infomart database is as below
select top 1 interaction_id, dbo.converttobase(media_server_ixn_id) from interaction_segment_fact order by interaction_id desc