August 5, 2008 at 9:40 am
Hi,
I have a very strange problem on our production server. We have made some changes on the dev machine that included adding a couple of new columns to a table and then changing a stored proc to update one of those new columns. No existing code in the procedure was changed.
Everything worked fine on the development machine.
We then created the extra columns on production and altered the procedure. Suddenly, no columns in the table were updated. The new columns had NULL values in them and the update set one of the new columns to 0.
Not only did that particular update fail to work, but a part of the procedure that is untouched that updated a datetime column also stopped working. We managed to find the existing procedure again and now it appears to work.
Have I missed something here? This seems to be very strange behaviour...
August 5, 2008 at 9:47 am
HI Paul,
Please could you give us the table schema and the proc schema with example input and outputs.
Otherwise it is going to be very very hard for us to help you.
Please check this link below for more info:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 9:52 am
Hi Chris,
I've managed to work it out, but I dont know why it makes a difference. In the original procedure that was created some time ago, there was this:
IF NOT @v-2 = NULL
Trying everything I could think of, I decided I didnt like how that was and changed it to:
IF @v-2 IS NOT NULL
And it now works. I think the first statement looks abit funny, but why it worked for 3 years up until we altered the proc I have no idea!
August 5, 2008 at 9:56 am
Glad you managed to find the answer.
There are a few articles about nulls on this site.
here is one of them
http://www.sqlservercentral.com/articles/Advanced+Querying/2829/
I tend NEVER EVER to use 'something' = null
😉
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 9:59 am
I think that the first statement should always take the TRUE branch, so maybe you just didn't have any NULLS before you added your new columns?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 9:59 am
The orignal procedure was created in SQL 2000, but the amended one in 2005. I completely agree with your comments, however this could well create massive problems if not detected! How a piece of code is valid in 2000 but not 2005 but yet does not generate at least a warning is rather worrying
August 5, 2008 at 10:11 am
Paul.Allen (8/5/2008)
The orignal procedure was created in SQL 2000, but the amended one in 2005. I completely agree with your comments, however this could well create massive problems if not detected! How a piece of code is valid in 2000 but not 2005 but yet does not generate at least a warning is rather worrying
Actually Paul, you make an excellent point. The thing is this syntax was logically (but not syntacticaly) invalid on both SQL 2000 and 2005! I've always wondered why the SQL parser/compiler doesn't throw an error or at least a warning on this.
Maybe you could post it to the Microsoft Connect database: http://connect.microsoft.com/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply