May 16, 2007 at 4:10 pm
(Before I start, I want to highlight that this is inherited bad code...)
Scenario: create scripts that run fine under SS 2000. Same create scripts generate errors under 2K5 - even if the DB is created in 2000 / 80 compatability mode. Source of the issue: a stored proc with an update statement that incudes the attempt to update an identity column. 2K doesn't complain about this (even attaching to a 2K server via SSMS) at create time. Obviously this proc was never used.
I just want to make sure I'm not hallucinating. Anyone else experienced this?
May 17, 2007 at 7:55 am
Has there been any procedure run, or called that uses the Identity Insert Off / On functionality? When you say 2K doesn't complain, does that mean not complaining on run, or on syntax checking? If you use the Identity Insert, it should run under 2k5 as well, but as you said earlier, it is just bad code practice.
May 18, 2007 at 8:11 am
To my knowledge neither version of sql server will allow updates to an identity column in a table unless that table has the set identity_insert property ON.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2007 at 12:21 pm
Yes, I am well aware that they don't and shouldn't *run*. The issue is the install / creation of the stored procedure which will, when called, run the update statement. 2K5 won't even let the stored proc come into existance on the database. 2000 apparently will. I am assuming this is a difference of when SQL Server will run its syntax checking. I'm just interested to know if anyone else has encountered this issus / a similar one.
May 23, 2007 at 12:40 pm
You are absolutely correct.
SQL Server 2005 has more strict validation which prevents bad code to pass through. It is really good. We had clean up issue, when we upgraded to SQL Server 2005, we recreated all the objects (SP, Functions). We cleaned up lots of objects when we created it.
Pinal Dave
sqlauthority.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply