January 9, 2009 at 12:18 am
I posted an earlier post where one of my columns is not being updating correctly. The problem still persists and no one haven't been able to solve this in detail. This update is weird because the same process to update another column works perfectly.
The table has 5 columns
userid (uniqueidentifier)
username (nvarchar)
points (int)
totalplays (int)
comments (int)
The default values of score, totalplays, and comments is 0.
When a user finishes a quiz, their total points (points) are updated using the following procedure:
ALTER PROCEDURE [dbo].[sp_ScoreUpdate]
-- Add the parameters for the stored procedure here
@username nvarchar(50),
@score int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE Userstats SET Score=Score + @score WHERE username=@username
END
This update works perfectly. Since the default is 0, then if someone earns 100 points then it updates to 0 + 100=100. If the person earns 200 points then it updates to 100+200=300.
However, the plays column is not updating correctly even though the procedure is pretty much the same except a few modifications. This is the procedure to update the # of plays.
ALTER PROCEDURE [dbo].[sp_UpdatePlays]
-- Add the parameters for the stored procedure here
@username varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE UserStats SET TotalPlays=TotalPlays + 1 WHERE Username=@username
END
The default is 0. When 1 is added to 0 the result is not 1! It is putting in 11! When I ran it again for a second time, the result was to update to 2! Instead, 11 was updated to either 23! or 24! The columns are integer columns, not varchar columns so this is not an issue! Is this a bug if something is increased by 1! Someone please help. This is driving me bonkers!
Thanks a lot.
January 9, 2009 at 12:31 am
Mark (1/9/2009)
I...ALTER PROCEDURE [dbo].[sp_UpdatePlays]
-- Add the parameters for the stored procedure here
@username varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE UserStats SET TotalPlays=TotalPlays + 1 WHERE Username=@username
END
The default is 0. When 1 is added to 0 the result is not 1! It is putting in 11! When I ran it again for a second time, the result was to update to 2! Instead, 11 was updated to either 23! or 24! The columns are integer columns, not varchar columns so this is not an issue! Is this a bug if something is increased by 1! Someone please help. This is driving me bonkers!
Thanks a lot.
- Triggers on table "userstats" ??
- is Username a unique item ? (maybe you should add "game" to the where clause)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 9, 2009 at 5:47 am
Can you post the create statement for the table and any associated triggers or indexes?
January 9, 2009 at 6:24 am
Nicholas, can you please post the exact query that you are using to get the data from UserStats which shows these figures?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2009 at 6:38 am
Your initial table definition:
userid (uniqueidentifier)
username (nvarchar)
points (int)
plays (int)
comments (int)
The query giving you problems:
UPDATE UserStats SET TotalPlays=TotalPlays + 1 WHERE Username=@username
Where is the column TotalPlays coming from? It's not there.
I said before and I'll say again, it looks like something (a trigger) is making the TotalPlays or plays column into a string and then concatenating values instead of adding them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2009 at 5:32 am
There are no triggers on the table at all!
I executed the stored procedure within sqlserver and the column updated fine. However, when done through the code of the application, the results are screwy.
January 10, 2009 at 7:35 am
If you had mentioned that this was only an issue through the app and not when run manually, which was what everybody thought, this could have been resolved a long time ago. Your problem is an application one then. Run profiler while using the application and trap what its doing. There may be behaviours like a loop or a straight table update from the application. The profile trace will tell you exactly what's going on.
January 10, 2009 at 7:51 am
Nicholas Cain (1/10/2009)
If you had mentioned that this was only an issue through the app and not when run manually, which was what everybody thought, this could have been resolved a long time ago. Your problem is an application one then. Run profiler while using the application and trap what its doing. There may be behaviours like a loop or a straight table update from the application. The profile trace will tell you exactly what's going on.
100% 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 10, 2009 at 10:09 am
I ran the profile trace, and when it went through the code, there was nothing unusual: The procedure only has one parameter "@username" and the command text for the sqlcommand was "sp_ScoreUpdate" so the parameter is passing in the right value for the parameter. It has to be something in the pipe between the app and the sql server that is being changed. I even changed the commandtype and wrote the sql statment directly in the code as:
UPDATE UserStats SET TotalPlays=TotalPlays + 1 WHERE username='username1'
No one can't seem to figure out what is going on. This is so strange because another column that uses the same formula works perfectly when updated. This is driving me bananas.
January 10, 2009 at 10:52 am
It does sound crazy.
Can you create a new user (and login) and trace that from the application? I'm suspicious that the application is doing something else and you're missing it in looking for the issue.
I'd also create a logging table and set a trigger that puts the inserted and deleted information into this table for the row updated so you can examine it.
January 10, 2009 at 10:59 am
- figure out how many times the application calls the sproc in a single application run. (or how many times it executes the cmd-object)
it must be a loop of some kind in the application process.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 10, 2009 at 7:35 pm
Can someone teach me about triggers. My experience with sqlserver is pretty much limited to creating stored procedures, so I don't know know anything about logging tables as Sam suggested.
January 10, 2009 at 8:15 pm
execute sp_helptrigger UserStats
This will tell you if there are any triggers on the table.
A trigger is fired depending upon the action performed, they can be set for insert/update/delete and perform other tasks, for example put entries into other tables, check for certain data, and prevent inserts of certain data combinations, send emails when items change, etc...
January 11, 2009 at 10:29 pm
Problem solved. Thanks for everyone who responded with their expertise. Apparently, the code in the app was postbacking every second due to the timer control and therefore updating the column as such. So all I had to do is set the page.ispostback=false and now everything is fine.
January 11, 2009 at 11:53 pm
Thank you for the confirmation 🙂
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply