Post reply

string or binary data will be truncated when using hashbytes

  • March 21, 2017 at 8:18 am

    #335830

    I'm running the below. , Why is it failing when I cast it as nvarchar(max) but not when I cast it as nvarchar(4000). This is one of about 60 columns I’m concatenating in my hashbytes. For some reason it is the only one erroring

    this doesn't work
    select Hashbytes('SHA2_512', Isnull(Cast( project_timeline__c AS NVARCHAR(max)), '-1')) [sha2_512_key]
    FROM [SFDC_EXTRACT_DEV].dbo.sf_opportunity(nolock)

    yet this works..
    select Hashbytes('SHA2_512', Isnull(Cast( project_timeline__c AS NVARCHAR(4000)), '-1')) [sha2_512_key]
    FROM [SFDC_EXTRACT_DEV].dbo.sf_opportunity(nolock)

  • March 21, 2017 at 8:32 am

    #1934162

    Depends on SQL Server version, from BOL:
    For SQL Server 2014 and earlier, allowed input values are limited to 8000 bytes.

    The specific page:  https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(hashbytes_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(MiscellaneousFilesProject);k(DevLang-TSQL)&rd=true

  • March 21, 2017 at 9:23 am

    #1934177

    ah, that's right. I pulled the code from something i wrote on a sql server 2016 ent server. I forgot about that. That's going to be a problem for me. Is there a work around for this in sql server 2014?

  • March 21, 2017 at 9:31 am

    #1934179

    Only thing I could suggest is BINARY_CHECKSUM.

  • March 21, 2017 at 4:44 pm

    #1934236

    Snargables - Tuesday, March 21, 2017 9:23 AM

    ah, that's right. I pulled the code from something i wrote on a sql server 2016 ent server. I forgot about that. That's going to be a problem for me. Is there a work around for this in sql server 2014?

    Yes. You can use a CLR for it which won't have that limit.

    There are several examples on the net, including free sets already tested for SQL Server such as the one on http://www.sqlsharp.com/features/

    I normally tend to use my own as I have full control over the assembly and code.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply