Thursday, November 19, 2009

Varbinary to string

I've experienced a particular frustration with SQL Server many times over the years: the seeming impossibility of programmatically fetching a varbinary value from a table, and converting it to the string value required by a procedure call in the same batch. One example would be when troubleshooting replication by extracting a xact_seqno varbinary(16) value from the MSdistribution_history table, and using it as the value for the nchar(22) @xact_seqno_start and/or @xact_seqno_end input parameters to the sp_browsereplcmds procedure.

For a long time, I just did this by hand because there didn't seem to be a way to get the value out. Later, I sometimes used the output parameter functionality of sp_executesql to bluff my way through this. However, I just discovered the there is a built-in (but undocumented) function that allows you to transform a varbinary into a string! It's master.dbo.fn_varbintohexstr(). Here's a script that accomplishes the replication troubleshooting task I cited above (I assume here you already know the publication database id and agent id - finding those is another subject altogether):

declare @last_xact_seqno varbinary(16)
,@last_xact_seqno_str nchar(22)

select @last_xact_seqno = max(xact_seqno)
from distribution..MSdistribution_history
where agent_id = 6
and error_id = 0

set @last_xact_seqno_str = master.dbo.fn_varbintohexstr(@last_xact_seqno)

exec sp_browsereplcmds
@xact_seqno_start = @last_xact_seqno_str
,@publisher_database_id = 7
Post a Comment