Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1767 > unrolled thread

Varbinary and nvarchar problem

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-19 15:00 -0400
    Re: Varbinary and nvarchar problem Erland Sommarskog <esquel@sommarskog.se> - 2014-06-19 23:06 +0200
      Re: Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-19 17:49 -0400
        Re: Varbinary and nvarchar problem Erland Sommarskog <esquel@sommarskog.se> - 2014-06-20 16:16 +0200
          Re: Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-21 09:17 -0400
          Re: Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-23 09:49 -0400
            Re: Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-23 10:27 -0400
              Re: Varbinary and nvarchar problem Erland Sommarskog <esquel@sommarskog.se> - 2014-06-23 21:33 +0200
                Re: Varbinary and nvarchar problem Shelly <sheldonlg@thevillages.net> - 2014-06-23 16:35 -0400
                  Re: Varbinary and nvarchar problem Erland Sommarskog <esquel@sommarskog.se> - 2014-06-24 07:19 +0000

#1767 — Varbinary and nvarchar problem

I have a table with a field that is nvarchar.  The input data is 
obtained from first doing a HASHBYTES(MD5, the_char_string). Call this 
the_data.

If I first do a CAST(the_data as NVARCHAR)  or if I do a 
CONVERT(NVARCHAR, the_data), then there is no problem with inserting the 
field.  However, when I do a select * via Sqlserver Manager, that field 
contains the string "??????".

If I do not first do the conversion, it insert the hash into the field, 
and succeeds for the first 15 of 1991 records.  It then bombs with 
"incorrect syntax near '<9e>V!¢(Q Ã+;<86>"^@Lmç'.

This process is interrogating a join of two tables and then is inserting 
a processing of the resulting data rows into another table.  It is on 
the insert (which is one condition of a merge statement) where the 
failure occurs.

Does anyone have a suggestion on how I can get the data into the third 
table and not have it all be "???????".

-- 
Shelly

[toc] | [next] | [standalone]

#1768

Shelly (sheldonlg@thevillages.net) writes:
> I have a table with a field that is nvarchar.  The input data is 
> obtained from first doing a HASHBYTES(MD5, the_char_string). Call this 
> the_data.
> 
> If I first do a CAST(the_data as NVARCHAR)  or if I do a 
> CONVERT(NVARCHAR, the_data), then there is no problem with inserting the 
> field.  However, when I do a select * via Sqlserver Manager, that field 
> contains the string "??????".
> 
> If I do not first do the conversion, it insert the hash into the field, 
> and succeeds for the first 15 of 1991 records.  It then bombs with 
> "incorrect syntax near '<9e>V!¢(Q Ã+;<86>"^@Lmç'.
> 
> This process is interrogating a join of two tables and then is inserting 
> a processing of the resulting data rows into another table.  It is on 
> the insert (which is one condition of a merge statement) where the 
> failure occurs.
> 
> Does anyone have a suggestion on how I can get the data into the third 
> table and not have it all be "???????".


It would help if you showed us the code. But given the syntax error it
sounds like you are building an SQL string from the value returned by 
hashbytes, and of course that will fail as soon as the hash includes the
byte 0x27, that is a single quote. The odds for that *not* to happen in 
2000 are very small.

But why are you building SQL strings from the values in the first place? 
and why would you do it when you already have the data in SQL Server? Which 
you seem to have since you run HASHBYTES() on it.

The question marks are due because your string literal is obviously 
varchar; that is it is not prefixed by N. Then again, why would you
cast the hash value to nvarchar? It is a binary value, not a string.

If you really want to store it as a string, I would suggest that you 
store it as a hex string in a varchar column but that will take up double
the space.

In summary: since I don't see the code, I can't say for sure exactly
what you are doing wrong, but judging from your narrative, there are 
number of flaws.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1769

On 6/19/2014 5:06 PM, Erland Sommarskog wrote:
> Shelly (sheldonlg@thevillages.net) writes:
>> I have a table with a field that is nvarchar.  The input data is
>> obtained from first doing a HASHBYTES(MD5, the_char_string). Call this
>> the_data.
>>
>> If I first do a CAST(the_data as NVARCHAR)  or if I do a
>> CONVERT(NVARCHAR, the_data), then there is no problem with inserting the
>> field.  However, when I do a select * via Sqlserver Manager, that field
>> contains the string "??????".
>>
>> If I do not first do the conversion, it insert the hash into the field,
>> and succeeds for the first 15 of 1991 records.  It then bombs with
>> "incorrect syntax near '<9e>V!¢(Q Ã+;<86>"^@Lmç'.
>>
>> This process is interrogating a join of two tables and then is inserting
>> a processing of the resulting data rows into another table.  It is on
>> the insert (which is one condition of a merge statement) where the
>> failure occurs.
>>
>> Does anyone have a suggestion on how I can get the data into the third
>> table and not have it all be "???????".
>
>
> It would help if you showed us the code. But given the syntax error it
> sounds like you are building an SQL string from the value returned by
> hashbytes, and of course that will fail as soon as the hash includes the
> byte 0x27, that is a single quote. The odds for that *not* to happen in
> 2000 are very small.
>
> But why are you building SQL strings from the values in the first place?
> and why would you do it when you already have the data in SQL Server? Which
> you seem to have since you run HASHBYTES() on it.
>
> The question marks are due because your string literal is obviously
> varchar; that is it is not prefixed by N. Then again, why would you
> cast the hash value to nvarchar? It is a binary value, not a string.
>
> If you really want to store it as a string, I would suggest that you
> store it as a hex string in a varchar column but that will take up double
> the space.
>
> In summary: since I don't see the code, I can't say for sure exactly
> what you are doing wrong, but judging from your narrative, there are
> number of flaws.
>

The database being interrogated is one from a supplier.  We want to put 
a minimum of information into our tracking database.  The two are joined 
and if there is no entry in the tracking table with the merge command, 
then an insert is done.  Otherwise an update is done.  The major field 
of interest in that table is the one for last updated.

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.

Thank you for responding.

-- 
Shelly

[toc] | [prev] | [next] | [standalone]

#1770

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.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1772

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.

Thanks.  I'll try that.

-- 
Shelly

[toc] | [prev] | [next] | [standalone]

#1774

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.

-- 
Shelly

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#1776

Shelly (sheldonlg@thevillages.net) writes:
> 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?
> 

Because you use it in expression which also includes an integer value, 
(which because of the data-type precedence rules in SQL Server leads to an 
implicit conversion) or you are trying to insert the data into an integer
column/variable.

That is all I can say without seeing the rest of the code and the
table definitions. Well, one more thing: watch out for triggers with
dynamic SQL.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1777

On 6/23/2014 3:33 PM, Erland Sommarskog wrote:
> Shelly (sheldonlg@thevillages.net) writes:
>> 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?
>>
>
> Because you use it in expression which also includes an integer value,
> (which because of the data-type precedence rules in SQL Server leads to an
> implicit conversion)

The three pieces that make up the content of what is to in the HASHBYTE 
are all varchar strings.  There are no integer values.  For example, one 
record has 'C00001', 'COMERCIAL' and 'S' as the three pieces, so the 
HASHBYTE is of 'C00001COMERCIALS'.  The result of the HASHBYTE is a 
varbinary, but the convert should be doing an explicit conversion of it 
to varchar(32).

>  or you are trying to insert the data into an integer
> column/variable.

This is a select, not and insert, so there is no target table column.

>
> That is all I can say without seeing the rest of the code and the
> table definitions. Well, one more thing: watch out for triggers with
> dynamic SQL.
>

-- 
Shelly

[toc] | [prev] | [next] | [standalone]

#1778

Shelly (sheldonlg@thevillages.net) writes:
> The three pieces that make up the content of what is to in the HASHBYTE 
> are all varchar strings.  There are no integer values.  For example, one 
> record has 'C00001', 'COMERCIAL' and 'S' as the three pieces, so the 
> HASHBYTE is of 'C00001COMERCIALS'.  The result of the HASHBYTE is a 
> varbinary, but the convert should be doing an explicit conversion of it 
> to varchar(32).
 
Well, as long as you don't share the code you are using there is not much
more than I or anyone else can say. 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web