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

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

Re: Varbinary and nvarchar problem

Show all headers | View raw

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

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