Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))