CHECKSUM , Produces Same Hash For Two Different Inputs. Is This Right?
Jul 25, 2007
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @V1 VARCHAR(10)
DECLARE @V2 VARCHAR(10)
SELECT @V1 = NULL, @V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@V1,@V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@V1,@V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
View 4 Replies
ADVERTISEMENT
Feb 29, 2008
do you have to store the checksum from the task in order to verify change? Here is what I did and it seems it's not going to work
Lookup transformation that
selects matching fields and returns a t-sql with the binary_checksum(fields)
Checksum transformation returns the checksum of same inbound fields
conditional split passes on changed records to the update
getting all the records everytime and should not be happening.
is there a way to make this work or do I have to store the checksum
View 8 Replies
View Related
Jan 29, 2004
I have a custom SP running from a PB application. When I am retrieving information for a certain client, a co-worker decides to run the same custom SP for another client before my results are displayed. When both results display, I end up with certain pieces of data from his client and he ends up with certain pieces of data from my client. What code in my SP do I need to prevent this from happening?
View 14 Replies
View Related
Jul 23, 2005
Hi,Using SQL Server 2000 with Windows 2000 Adv Server&Microsoft Access linked table (running stored procedure using ADO asfollows:************************************************** ********Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)Dim cnn As ADODB.ConnectionDim cmd As ADODB.CommandDim prm As ADODB.ParameterDim msg As StringOn Error GoTo Err_AddrType_NotInList'Exit the procedure if the combo box was clearedIf Trim(NewData) = "" Then Exit Sub'Confirm that the user wants to add AddrTypemsg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCrmsg = msg & "Do you want to add it?"If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then'If the user chose not to add AddrType, set the response'argument to supress an error message and undo changes.Response = acDataErrContinueMsgBox "No record added.", vbOKOnly, "Action Cancelled"Else'If the user chose to add AddrType, open a recordset'using the AddrType tableSet cmd = New ADODB.CommandSet cnn = New ADODB.Connectioncnn.Open "Provider=SQLOLEDB;Data Source=penland01;InitialCatalog=groomery;Integrated Security=SSPI;"cmd.ActiveConnection = cnncmd.CommandText = "spInsertAddrType"cmd.CommandType = adCmdStoredProcSet prm = cmd.CreateParameter("AddrType", adVarChar,adParamInput, , Trim(NewData))cmd.Execute Parameters:=prm'Set Response argument to indicate that new data is being addedResponse = acDataErrAddedcnn.CloseSet cnn = NothingEnd IfExit_AddrType_NotInList:Exit SubErr_AddrType_NotInList:MsgBox Err.DescriptionResponse = acDataErrContinue************************************************** ********"NewData" is a text string - in this case "Test"The stored procedure referenced in the code is:************************************CREATE PROCEDURE [spInsertAddrType](@AddrType [nvarchar](50))ASINSERT INTO [groomery].[dbo].[tblAddrTypes]([fldAddrType])VALUES(@AddrType)GO*************************************When I execute this code, I receive the following error"Cannot update identity column 'fldAddrTypeID'."fldAddrTypeID is configured as follows:***************************Data Type = intIdentity = YesIdentity Seed = 1Identity Increment = 1***************************The documentation I've found online concerning this error says that it isproduced when you try to supply a value for an identity field without SETIDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can'tfigure why I'm getting this error.Thanks for any help you can offer.Todd
View 1 Replies
View Related
Nov 7, 2006
When I execu sp_refreshview,
I got this error message on a valid view on SQL 2005 express edition.
Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1
Object 'vName' does not exist or is invalid for this operation.
Next, I got a similar error on SQL 2005 for a valid view.
Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1
Object 'vNamehere' does not exist or is invalid for this operation.
Is this Microsoft Bug?
View 1 Replies
View Related
Apr 21, 2008
Hey,
I am using the pivot component in SSIS to pivot a set of data, and up until now it has worked fine. However lately it has started to produce duplicates, meaning 2 rows for a combination of the keys (type 1) used. It is completely random, meaning a combination of keys for one run can work correctly, and in the next run it inserts an extra record for the combination for just one of the pivoted values. This also means that for a batch of 10 mill rows input the output is completely random, because the number of duplicates vary.
Are there any memory restrictions or similar that causes this, or do you guys have any idea what might be wrong?
Thanks in advance
View 5 Replies
View Related
Jun 11, 2008
Hi
What is the purpose of checksum function???
RKNAIR
View 3 Replies
View Related
May 15, 2002
I am running an environment as below
ADO 2.7
SQL Sever OLE DB Provider
VB 6
SQL Server 6.5 SP 5 A Update
If I run a stored procedure that contains a Print statement or gives a warning. Then any error that occurs after that is ignored. No error state is raised in VB and the Errors collection only contains the print or warning.
Can anyone tell me why?
Thanks
Chris
View 1 Replies
View Related
Oct 26, 2007
We have two instances of SQL Server 2005 - SP1 installed on one server. The default instance starts very slowly. When looking at the log I can see the delay is due to Resource Manager based upon the following error.
Message
Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)
What does this mean?
What would cause this to happen?
How do I resolve the problem?
Thanks, Dave
View 8 Replies
View Related
Feb 6, 2004
Hi,
Can anyone provide me with the syntax for comparing rows of two tables using binary checksum? The tables A and B have 8 & 9 columns respectively. The PK in both cases is Col1 & Col2. I want checksum on Columns 1 to 8.
Thanks
View 6 Replies
View Related
Feb 6, 2004
Please execute the script below to understand the problem -
---
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--
View 3 Replies
View Related
Jun 7, 2006
Hello All,
How to use CHECKSUM function and how it is useful?
Thanks
Sanjeev
View 1 Replies
View Related
Jul 23, 2005
I'm developing a stored procedure to run an update based on valuesentered into a .Net web form. I want to capture the chceksum of therow when it is displayed on the form then validate that when the updateis exec'd. Simple enough logic, eh? The problem is when I try to usethe checksum(*) function, SQL server yells at me and says that it isn'trecognized. I'm using SQL Server 7, so wtf? I am not the admin of theserver and I'm skirting around SQL Server Enterprise Manager and usingany free utils, MS Access, and Visual Studio to maintain this db.ThanksAlex Jamrozek
View 7 Replies
View Related
May 29, 2007
Hi,I'd like advices about an idea I add to resolve a problem. thanks toyou in advance for yours answers.I have a database with tables that I load with flat file. The size ofeach table is 600 Mb. The flat file are the image of an applicationand there is no updated date or created date on any table. So mytables are just a copy of the data from the flat file.Now I'd like to create an History Table. So I have to determine whichlines changed and which one did'nt.As I don't have any date on my row the only answer I had unil know wasto check each column on each row to see if any data changed. If thedata changed I add a new line in my history date.My idea is to add a checksum column in both table on all columns. Toknow if any data change I just have to check my PK + my checksumcolumn.Do you think that is a good idea ? Is checksum a quick function ornot ?.Thanks.--K
View 3 Replies
View Related
Jan 24, 2008
Hi,
I heard that page checksum enabled will reports errors occured in the log. That's good.
Currently we have DBCC PHYSICAL_ONLY run alone and CHECKTABLE on group of tables on different days. A suggestion came from a person is to turn off 'DBCC CHECK TABLE' and run only when checksum reports an error and continue running CHECKDB WITH PHYSICAL_ONLY as before.
Is this suggestion a best practice? Please also write few lines to say why it is wrong or wright.
Thanks and best regards
Priw
View 1 Replies
View Related
Sep 24, 2007
Hi,
I recently researched on the CHECKSUM & CHECKSUM_AGG functions in T-Sql and found them really useful. However, I was skeptical that there are chances of these functions returning the same values for non-identical inputs. I just got on to the forums and found more than one unhappy folks writing about their experience with these functions.
I am designing a large database (warehouse) and found these functions tempting to implement for the sake of
using CHECKSUM for
- indexing long character fields
- multiple colums of the same table that would involve in a join and use the new checksum field instead
using CHECKSUM_AGG for
- I bulkcopy flat file soruce data into a character field of a table and to ensure that I am not loading the same file multiple times, I plan to use CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) and verify that no two loads have the same output.
Can some body suggest if I can trust these methods for my purpose?
Many thanks in advance!!
Thanks,
Harish
View 5 Replies
View Related
Feb 18, 2008
it sounds like a column can be added to each row in a table that is the checksum or binary_checksum of an expression. How many bytes do each of these occupy? Does the answer depend on the number and/or length of items in the expression?
View 4 Replies
View Related
Dec 18, 2007
Hi:
i am using checksum in my etl process for this i have a checksum field to calculate the values in my table
the column is a computed column and it has a property for persistence .
what decision should i take should i make it persisted ot not what is the industry standard.
Can you please expalin how this property would affect the behaviour of the column
will this property affect me in any thing like indexes . please let me what step should i take should i make the column persisted or not .
Please let me know.
Thanks,
View 3 Replies
View Related
May 11, 2006
Morning Campers,
I have two tables src_monthly_terrrier and src_weekly_terrier. Both of these tables consists of 10+ columns. As the table names probably suggest, I import weekly data into one and monthly data into another.
All the source data comes from an Excel spreadsheet via straight Import Data procedure. The only guaranteed change on a weekly and monthly basis is that one of the columns in each table named src_date will obviously have the data value for whichever month or week's data it relates to.
I understand that through 'SQL Server Business Intelligence Development Studio' I can create an 'Intergrated Services' package that will import the spreadsheet details for me. I might be going the long way around this, but it was my intention to bring in all the data and then run a couple of 'INSERT INTO' Stored Procedures.
My biggest issue / vunerability I have is that there is no error checking of the data on the way in to ensure that it has not already been imported. What I was thinking I could do to resolve this was to create a Checksum field comprising of a number of different columns (incl src_date) and then somehow write something that will look at the values of each intended imported row and then work out whether a duplicate checksum was found in the target table and then rejected the import routine as Duplicate Data Found (or something similar) and move onto the next stored procedure.
My problem is two fold, one I have no idea how to create said checksum and two no idea where to begin on coding a procedure etc that looks to see if the value already exists etc etc.
I have looked up checksum creation on the net and there appears to be plenty of resource to explain how to create one, so I guess my main question is, Where do I start when it comes to writing some code that will do the check of the checksum before the importation routine begins (or at least the Insert Into procedures.
I would truly appreciate anyone's help on this. In the meanwhile I am off to learn how to create them.
I would like to add, if anyone sees this as a bad idea, then please speak up.
Thanks in Advance
View 1 Replies
View Related
Aug 16, 2006
Matt writes "Greetings! Warning, I'm a rookie. I wrote a stored procedure to pull data in order to do a nightly export/import from one system to another. I have a batch file that looks like this:
bcp "exec WinSNAP_retrieveStudents '0607'" queryout c:WinSNAPData06.txt -c -U user -P password
Sometimes, the file works and I get perfectly formed data, with everything just as I've requested (mostly basic demographic information: names, addresses, etc.).
But, other times the output file contains nothing but garbage characters, like this:
剒乏†††††††††ठ䅍啎䱅†††䴉㐉㐷‰䕓䄠䅐䡃⁅剄
The file size looks right, but it contains nothing but characters like this from beginning to end. I can find no pattern as to why/when good data gets pulled versus the corrupt data. I can run the batch file one minute and get good data, and run it the next minute and it's all corrupt. We have the batch file scheduled late at night when no users are online, and I get the same results -- one day it works, the next it doesn't.
Forgive me if this is a well-documented issue -- my searches so far haven't turned up a thing!
Thanks much for any advice you can provide!!
Matt Smith
DeSoto County School District
Arcadia, FL"
View 1 Replies
View Related
Mar 11, 2008
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
== == == == == == == == == == == == == == == == == == == ==
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.
View 5 Replies
View Related
Nov 20, 2007
Hi Folks,
I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.
Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:
use Master
go
sp_lock
go
use Experience
go
sp_lock
go
which results in:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 1 85575343 0 TAB IS GRANT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 13 0 0 DB S GRANT
52 1 85575343 0 TAB IS GRANT
My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open
Question 1:
The way I read this is: the "use Experience" statement creates a shared database lock.
Am I reading this right?
Question 2:
I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained.
Should I be concerned about these locks? Is this expected behavior?
Thanks Much in advance.
- D
View 3 Replies
View Related
Feb 9, 2006
Tables have 100K rows. Inner join produces 99k. The outer join should be 100K. It is 1000 times larger.
If I do a SELECT DISTINCT, the right # rows are returned (100K)
What gives?
SELECT tblEarth.key1, tblEarth.key2a, tblEarth.key2b
FROM ((tblEarth INNER JOIN tblMoon ON Mid(tblEarth.key1,1,6) = tblMoon.key1) LEFT OUTER JOIN
tblVenus ON (tblEarth.key2b = tblVenus.key2b)
AND (tblEarth.key2a = tblVenus.key2a)) LEFT OUTER JOIN
tblMars ON tblEarth.key2a = tblMars.key2a;
Laurence
View 1 Replies
View Related
Mar 19, 2004
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam
View 7 Replies
View Related
Feb 11, 2015
what is the use of check sum in sql server:
ex: RESTORE DATABASE [XXX] FROM DISK = 'XX.BAK' with check sum
View 1 Replies
View Related
Aug 13, 2007
How much of a performance impact will using Cheksum have over Torn Page Detection for Page Verify Recovery? Thanks
View 3 Replies
View Related
Mar 13, 2007
Looking for some clarification on the CHECKSUM option of the BACKUP command.
If the the CHECKSUM option is specified in the backup, will the backup fail if CHECKSUM finds bad values (or at least raise an error)? Or, is it only reported when doing a RESTORE VERIFYONLY?
Thank you.
View 3 Replies
View Related
Nov 1, 2006
How do I write a regression test for a stored proc that produces multiple rowsets via multipl e select queries? E.g.
CREATE PROCEDURE myProc AS
SELECT 'Some stuff', GETDATE()
SELECT 'Some more stuff'
For single-select procs, I can create a temp table and INSERT #temp EXEC myProc, then evaluate the contents of the table to verify correct behavior, but that doesn't work in this case.
View 1 Replies
View Related
Aug 22, 2006
With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.
This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.
With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
ENDActually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION dbo.fnPesoTextChecksum
(
@Data TEXT
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden
View 6 Replies
View Related
Mar 22, 2007
For detecting delta records, I'm a big fan of SQLIS' checksum transform. I'm having difficulty in it's install on my current machine, however. After the installation and the new transform is added to my DataFlow toolbox... I can't open the UI for the transform to define the checksum. Instead, I get the following error:
===================================
Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. (Microsoft Visual Studio)
------------------------------
Program Location:
at Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransformUI.Edit(IWin32Window parentWindow, Variables variables, Connections connections)
at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.StartComponentUI(Boolean startGenericUI)
Anyone have any suggestions? Thanks in advance.
View 2 Replies
View Related
Jul 20, 2007
I am using the Konesans Checksum transformation ( http://www.sqlis.com/21.aspx ) to detect changes in my big (many columns, type 2 SCD) dimensional table.
But I am running into collossions
The checksum transformation, sometimes misses a small change in the record, for instance when a certain flag is set or unset. Is there a more robust checksum generator? Of any other suggestions on to solve this?
thx
View 12 Replies
View Related
Aug 30, 2006
Does anyone know how to detect the CHECKSUM setting of the PAGE_VERIFY database option (2005 only)?
BOL (ALTER DATABASE) includes the following statement:
PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.
However, there is no column named page_verify_option in the view sys.databases, and DATABASEPROPERTYEX('IsTornPageDetectionEnabled') does not discriminate between the settings CHECKSUM and NONE (it returns 0 for both)!
View 1 Replies
View Related
Oct 3, 2014
Is there anyway to get a sum of values from the below sample where Code=A?
Code1 Code2 Code3 Val1 Val2 Val3
A A B 1 5 8
B A B 5 6 2
Desired Result
Val = 12
View 1 Replies
View Related