June 15, 2007 at 10:39 am
We have recently had an issue where some fields are being overwritten in the SQL Server 2000. The data is in the form of a string and depending on which field is being overwritten they are between 18 and 76 characters in length. Each field is a string of the same length. The SPROC's used to update the data are passed 2 parameters. 1 of them is an identifier which consists of 2 alpha characters followed by between 4 and 7 numeric characters. The data type is set to char(9) for this field. Then there is the string that is passed which is just a string consiting of 0,1,2 or 3 and in one case 0-5 and would look something like this 11231313132213311213... again these are set dependant on the individual field that we are updating and are set as char and set to the length of the string. It has recently become a problem where 1 of the databases has had the fields that hold the strings overwritten.
In our older DB's the identifier that was passed was a number that was from an identifier field, and the datatype of the columns that hold the strings in these older DB's was set to varchar(100).
Could this possibly be because of the new ID system that was implemented that is causing this on occasion to overwrite records? The ID's are not similar and the only way we know it is happening is that there are records where all 5 of the fields are the same. and the odds of that happeneing are phenomenal. 76 to the 3rd for one column 40 to the 4th for another, 18 to the 6th for another, etc...
So does anyone have any ideas what could cause this to overwrite? Here is one of the SPROCs that is used to add the data
Create Procedure sp_AddA1EData
@user-id char(9),
@A1 char(40)
AS
BEGIN
UPDATE
eDataTable
SET
A1 = @A1,
Completed = '2'
WHERE
UserID = @user-id
END
GO
August 7, 2007 at 3:04 am
Is the UserID column supposed to be unique?
You mention that the odds of it happening are phenomanal, but the possibility is always there unless the column is defined as unique.
It goes without saying that if the UserID column is not unique, the sproc will update all rows matching the UserID parameter passed in.
August 7, 2007 at 7:11 am
Yes, the UserId column is Unique. They are set up as a foreign key to another table where they are the primary key.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply