Stored Procedure Not Update a Table Reliably
-
Hi
This is a bit long, so I'm using headings.
Symptoms:
A stored procedure (see attached for the table definition, three rows of data and the sp creation script) is updating a table correctly when RaceScale = "001" or "100", but not "999" There are no special conditions or relationships associated with a RaceScale of "999", as you can see in the attached script RaceScale is just a normal column in a table. This stored procedure ran perfectly all of the times when I stepped through/debugged it and when I ran it normally through SSMS.
The result of the SP on bad records is that the new Race Number is being inserted into the CURRRN column, the current Race Number is being inserted into both the NEWRN and OLDRN columns and both OLDDATE and NEWDATE are being set to the new date. If my phrasing has confused you, please see the attached script just below "-- Below is a bad record to illustrate the problem:"
My investigations/what I have done:
The VB6 application calls the sp in the same way for each RaceScale:
CnDB.Execute "EXEC sp_EditRACENUMBERScale " & raceCode & newRaceNumber & ", '999'" & newDate & oldDate--' The variable values are:
--' raceCode = "'abc123'"
--' newRaceNumber = ", 123.45"
--' newDate = ", '2009/08/11'"
--' oldDate = ", '2009/08/10'"
--' This code works correctly for the other two RaceScales.
I've tried changing it to:
CnDB.Execute "DECLARE @raceCode varchar(15); DECLARE @newRaceNumber float; DECLARE @racescale varchar(3); DECLARE @newDate datetime; DECLARE @oldDate datetime; SET @raceCode = '" & raceCode" & "'; SET @newRaceNumber = " & newRaceNumber & "; SET @racescale = '999'; SET @newDate = '" & newdate & "'; SET @oldDate = '" & olddate & "'; EXECUTE [dbo].[sp_EditPriceScale] @raceCode, @newRaceNumber, @racescale, @newDate, @oldDate;"--' The variable values are:
--' raceCode = "'abc123'"
--' newRaceNumber = "123.45"
--' newDate = "'2009/08/11'"
--' oldDate = "'2009/08/10'"
I've rebuilt all the indices on the table, but it didn't help. It takes less than one second to run the sp on my machine.
Like I mentioned above, the stored procedure ran perfectly each and every time when I stepped through/debugged it or ran it normally through SSMS:
EXEC sp_EditRACENUMBERScale '000000000000003', 500, '999', '2009/08/11', '2009/08/10'I checked using SQLS Profiler that the correct command is sent to the database, and every time and for every RaceScale the SP command is sent through correctly. I even checked that every statement within the sp is being executed correctly and they are. The template that I used was "TSQL_SPs"
Horribleness:
The worst part is that while I was repeatedly testing this part of the program, using the same parameters and resetting the data after every test, it suddenly worked for RaceScale "999". When I tried it again, it didn't work. I repeatedly ran the tests and reset the data every time. Please see "Script that I used to reset the data after each test run" in the attached zip file for the reset script.
Other information:
About a month ago we migrated the database from SQL 2000 to SQL 2005 by backing it up on the old server and restoring it on the new server. The collation throughout the database is set as "SQL_Latin1_General_CP1_CI_AS" but on the server it is "Latin1_General_CI_AS". The users have run this part of the program since the migration and it has worked.
I can give you the scripted statistics on the table if you need them.
I've renamed the objects, so if you spot any sytax errors, feel free to point them out but they're probably just from me renaming things.
I'm using SQL 2008 on my machine and the client is using SQL 2005.
Summary of my question to you:
WTF?
-
Probably it won't fix anything, but I usually run stored procedures with ADODB.Command objects.
At least I'm sure it maps variable types correctly.
If you want to trap what happens inside the stored procedure with Profiler, I suggest you add RPC::StmtCompleted events to your trace.
Hope this helps
Gianluca
-- Gianluca Sartori
-
*frustration*
Turns out that much later in the code (a hundred lines or so later in the method) the following line wrote the race to the database again:
"DoRaceCheck"
Yeah, that's the line in it's entirety, buried in a whole bunch of code.
Developer Fail or Maintainer Fail?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply