hashbytes

/*
Checksum() isnt very good for detecting data changes. Use hashbytes() instead
Example of output from script below:
-361975519341300.801789
*/


select avg(cast(cast(hsh as bigint) as decimal(25,0))) as truncatedHash
from (
select cast(HASHBYTES('SHA1',
[StringField1]+
[StringField2]+
[StringField3]) as binary(20)) as hsh
FROM dbo.TableToBeInvestigated
) as hashedData

Comments

Post a Comment

Popular posts from this blog

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Cause for Parameter is incorrect 0x80070057 error in ssis

Image
Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.
Read more

How to fail sql server agent job if powershell script fails

Image
Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...
Read more