convert all the column of table to ToBase64String

Question

convert all the column of table to ToBase64String

vipin_jha123

Hall of Fame

Points: 3085
More actions
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

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

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

This is not a valid SQL datatype.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

Ok sir

then whats the alternate of this .

becaue I have to chive in sql ,

regards,

Vipin jha

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

vipin_jha123 (2/17/2015)
--
becaue I have to chive in sql ,
--

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 5

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,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 7

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 9

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 10

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 11

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 12

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 13

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 14

vipin_jha123

Hall of Fame

Points: 3085

February 18, 2015 at 1:41 am

#1778276

Yes you are correct.