convert all the column of table to ToBase64String
-
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
-
This is not a valid SQL datatype.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ok sir
then whats the alternate of this .
becaue I have to chive in sql ,
regards,
Vipin jha
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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,
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In this case I will not get output like fghxvsUd3nuO6Zvmhv1t/w==
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
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 -
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
-
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 -
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
-
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