SQL Server Performance Forum – Threads Archive

SQL 2000/7.0 implicit casting problem

I want to know why this statement will run in a SQL 7.0 environment but not in a 2000.
update MyTable set iSomeId = case when sMyField > 5 then 99 else sMyField end where lSomeId is null
The statement is setting a tinyint field to be the contents of a char field and relying on implicit casting. I am not condoning the statement. I did not write it and will be rewriting, but it sparked my curiosity. The statement is part of a stored procedure.
When run under SQL 2000 it generated the following error Server: Msg 245, Level 16, State 1, Procedure spMyStoredProcedure, Line 446
Syntax error converting the varchar value ‘M ‘ to a column of data type int. On checking the data, there are indeed cases where lSomeId is null and sMyField contains ‘M’. But here is the crux; when run under SQL 7.0 against a database with an identical
structure and data it appears the complete. The iSomeIds that correspond to the sMyField ‘M’s are set to NULL. Why is this so? Is this a server setting or is it a difference between 7.0 and 2000? Any thoughts would be appreciated. Thanks.

Yes, this is a difference between both version, AFAIK. I think there is also a kb article describing that, however I haven’t got the link at hand right now. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

KBAhttp://support.microsoft.com/kb/900625 what Frank was referring about the hotfix. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Aah, you can also count on satya! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I believed that the change was introduce with a Service Pack as the RTM installation of 2000 exhibits the same behaviour, but I would have guessed it was SP2. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks for your help guys. At least I know that it is a release difference and not a server setting of some kind. BTW, thanks for the link Satya, but I cannot find mention of my problem in http://support.microsoft.com/kb/900625

The information is provided in above KBA is minimal and fixed with the hotfix as they mentioned.
Then check another KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q271566 fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>