Ok, I've had a few emails on this, so I'll post the code here.

This is the code for the first component, a user defined function to
translate a Varbinary value into a Varchar string of hex values. The
hex string will obviously contain twice as many bytes as the binary
string.

The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.

-Clay


if exists (select 1
from sysobjects
where name = 'ufn_VarbinaryToVarcharHex'
and type = 'FN')
drop function ufn_VarbinaryToVarcharHex
GO

CREATE FUNCTION dbo.ufn_VarbinaryToVarcharHex (@VarbinaryValue
varbinary(4000))
RETURNS Varchar(8000) AS
BEGIN

Declare @NumberOfBytes Int
Declare @LeftByte Int
Declare @RightByte Int

SET @NumberOfBytes = datalength(@VarbinaryValue)

IF (@NumberOfBytes > 4)
RETURN Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,

1,

(@NumberOfBytes/2)) as varbinary(2000)))
+ Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@VarbinaryValue,

((@NumberOfBytes/2)+1),

2000) as varbinary(2000)))

IF (@NumberOfBytes = 0)
RETURN ''


-- Either 4 or less characters (8 hex digits) were input
SET @LeftByte = CAST(@VarbinaryValue as Int) & 15
SET @LeftByte = CASE WHEN (@LeftByte < 10)
THEN (48 + @LeftByte)
ELSE (87 + @LeftByte)
END
SET @RightByte = (CAST(@VarbinaryValue as Int) / 16) & 15
SET @RightByte = CASE WHEN (@RightByte < 10)
THEN (48 + @RightByte)
ELSE (87 + @RightByte)
END
SET @VarbinaryValue = SUBSTRING(@VarbinaryValue, 1,
(@NumberOfBytes-1))

RETURN CASE WHEN (@LeftByte < 10)
THEN
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) +
char(@RightByte) + char(@LeftByte)
ELSE
Payment.dbo.ufn_VarbinaryToVarcharHex(@VarbinaryVa lue) +
char(@RightByte) + char(@LeftByte)
END


END
go

GRANT EXECUTE ON [dbo].[ufn_VarbinaryToVarcharHex] TO [PUBLIC]
GO