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

Groups > comp.databases.ms-sqlserver > #1769

Re: Varbinary and nvarchar problem

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web