Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Shelly Newsgroups: comp.databases.ms-sqlserver Subject: Re: Varbinary and nvarchar problem Date: Mon, 23 Jun 2014 10:27:25 -0400 Organization: A noiseless patient Spider Lines: 56 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit Injection-Date: Mon, 23 Jun 2014 14:27:20 +0000 (UTC) Injection-Info: mx05.eternal-september.org; posting-host="8ee873ed26843422d6d6b2a910ee355c"; logging-data="8559"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/Z2W/y/K2kZ/OFxtdC3zBmpJGjXtI2EMQ=" User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 In-Reply-To: Cancel-Lock: sha1:94OJWeAGKZ6EQfqL09UyqO7rvAE= Xref: csiph.com comp.databases.ms-sqlserver:1775 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