From: sheldonlg@thevillages.net   
      
   On 6/23/2014 9:49 AM, Shelly wrote:   
   > On 6/20/2014 10:16 AM, Erland Sommarskog wrote:   
   >> Shelly (sheldonlg@thevillages.net) writes:   
   >>> Anyway, I have the solution.  It is to enclose the HASHBYTES stuff with   
   >>> the following:   
   >>>   
   >>> SUBSTRING(master.dbo.fn_varbintohexstr(  hashbytes calculation ), 3,   
   >>> 32).   
   >>>   
   >>> The result is a 32 character hex string put into the tracking table.   
   >>>   
   >>   
   >> If you are on SQL 2008, use   
   >>   
   >>    convert(varchar(32), hashbytes, 2)   
   >>   
   >> instead, as this is a documented and supported way to get a hex string.   
   >   
   > It didn't work.  I got something like:   
   >   
   > Msg 245, Level 16, State 1, Line 1   
   > Conversion failed when converting the varchar value   
   > '04B2D57D287ADE502C621C3534C574' to data type int.   
   >   
      
   It gets even more crazy.   
      
   I have a query that works. One field is in the query is:   
      
   HASHBYTES('MD5', table1.field1 + table1.field2 + table1.field3) as OUT_NAME   
      
   this gave a wierd looking string. I want to have this be a 32 bit hex   
   string so I tried surrounding the hashbyte calculation with   
      
   CONVERT(VARCHAR(32), THE-HASHBYTE-STUFF, 2) AS OUT_NAME.   
      
   I also tried (this one worked elsewhere in the query)   
      
   SUBSTRING(master.dbo.fn_varbintohexstr(THE-HASHBYTE-STUFF), 3, 32) AS   
   OUT_NAME   
      
   Both of those gave errors in converting a varchar to an int.   
      
   I also tried converting each piece int the HASHBYTES to a varchar or to   
   an nvarchar and still got errors in the pieces -- even though the one   
   without any conversions -- the plain HASHBYTES -- worked. I also tried   
   CAST, but still had those errors.   
      
   Why does   
      
   CONVERT(VARCHAR(32), something-that-works, 2)   
      
   fail -- and with a conversion to int?   
      
   --   
   Shelly   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)