Post reply

convert all the column of table to ToBase64String

  • February 17, 2015 at 4:37 am

    #315476

    Hi ,

    I am looking to achive below code in TSQL.

    I want to convert all the column of table to ToBase64String.

    Public Class ScriptMain

    Inherits UserComponent

    Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)

    Dim hash As Byte() = md5.ComputeHash(columnContents)

    Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)

    Row.RowChecksum = hashString

    End Sub

    End Class

    Regards,

    Vipin jha

  • February 17, 2015 at 5:01 am

    #1778020

    This is not a valid SQL datatype.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • February 17, 2015 at 5:05 am

    #1778021

    Ok sir

    then whats the alternate of this .

    becaue I have to chive in sql ,

    regards,

    Vipin jha

  • February 17, 2015 at 5:06 am

    #1778022

    vipin_jha123 (2/17/2015)

    --

    becaue I have to chive in sql ,

    --

    I am not familiar with the verb 'chive'. What do you mean?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • February 17, 2015 at 5:12 am

    #1778023

    Couple steps back... What are you trying to do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2015 at 5:14 am

    #1778026

    Suppose I have 3 column in department table like

    Select Deptid,Dept_name,location from department.

    in same query I want to derive checlksum ToBase64String value .

    My output shild be look like below output

    HjGrRZvXy/kMQE+NKwfXNA==

    470xf2ZinGTq0PoQtPGiWQ==

    uFM0YIkWVsRlEgg9lmkekw==

    fghxvsUd3nuO6Zvmhv1t/w==

    Regards,

  • February 17, 2015 at 5:19 am

    #1778028

    A checksum is numeric, it doesn't require Base64 encoding.

    Something like

    Select Deptid,Dept_name,location, checksum(Dept_name) from department

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2015 at 5:20 am

    #1778030

    In this case I will not get output like fghxvsUd3nuO6Zvmhv1t/w==

  • February 17, 2015 at 5:25 am

    #1778032

    No, because a checksum doesn't need to be base64 encoded.

    Base64 is used for converting binary values to text.

    Again, what are you trying to do here? What's the point of this?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2015 at 5:33 am

    #1778034

    There's an unpleasant XML conversion that may work for you

    WITH source AS (

    SELECT Deptid,Dept_name,location,HASHBYTES('MD5',CAST(Deptid AS VARCHAR(10)) + Dept_name + location) AS col

    FROM department)

    SELECT Deptid,Dept_name,location,

    CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("col")))', 'VARCHAR(MAX)') AS Base64

    FROM source;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 17, 2015 at 7:46 am

    #1778067

    Looking at what's been posted I'm linking this is some sort of ETL (or similar) process where you are trying to create a hash key on columns, in order to check to see if there has been any change, correct?

    You can do this in a number of ways, one is to use the HASHBYTES function in SQL.

    Are you trying to do this in an SSIS script task? If so there are plenty of Hashkey genererators out there on codeplex etc.

    One word of warning, the Hash algorithms can have an issue with the blob data type.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • February 18, 2015 at 1:11 am

    #1778270

    in my source all the columns are varchar.

    I want to convert into ToBase64String value to get output like HjGrRZvXy/kMQE+NKwfXNA== ,

    470xf2ZinGTq0PoQtPGiWQ==

    regards,

    Vipin jha

  • February 18, 2015 at 1:32 am

    #1778271

    vipin_jha123 (2/18/2015)

    in my source all the columns are varchar.

    I want to convert into ToBase64String value to get output like HjGrRZvXy/kMQE+NKwfXNA== ,

    470xf2ZinGTq0PoQtPGiWQ==

    regards,

    Vipin jha

    But why do you want to do this?

    What is its purpose, do you need to do it as a part of an ETL process? Can it be done in SSIS, or does it have to be done as part of an SQL Script?

    Without knowledge this we cant suggest the best way forward.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • February 18, 2015 at 1:40 am

    #1778274

    Because initially It was implemented by SSIS on production.

    now I have to do using SQL , in development we are getting the data like HjGrRZvXy/kMQE+NKwfXNA==.

    if i used another method i will get another checksum value in that case source checksum will not match with destination checksum, so it will treat as new record.

    that is why I am looking to handle it using sql only

    thanks

  • February 18, 2015 at 1:41 am

    #1778276

    Yes you are correct.

Viewing 15 posts - 1 through 15 (of 21 total)

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