January 3, 2007 at 9:31 am
Hi,
I have a sql script that's supposed to copy a deprecated column to a new column; If the table contains a column 'Deadline' (deprecated) just copy the value to the new column 'ExpirationDate'.
--migrate from deadline to ExpirationDate
IF
EXISTS
(
select * from syscolumns
where
id = ( select id from sysobjects where name = 'CAMPAIGNS' )
and
name='Deadline' )
BEGIN
update dbo.CAMPAIGNS
set ExpirationDate = Deadline
END
It works great in 2000.
2005 however has an eager evaluator. The script fails and i get:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Deadline'.
Howcome it enters the conditional statement and evaluates it?
The whole point of the if statement is to execute this script only if this column DOES exist;
Any ideas???
January 3, 2007 at 11:48 am
Try this query and let us know the results:
select * from information_schema.columns where table_name = 'CAMPAIGNS' and column_name = 'Deadline'
Generally, you want to use the information_schema views (as MS wants to be able to change the system tables, should the wish to) rather than the system tables and, as an aside, you can use OBJECT_ID() rather than the subquery to obtain the objectid.
SQL guy and Houston Magician
January 3, 2007 at 11:51 am
Sorry, I misread your post. One option is to use dymanic SQL for your update. That way it doesn't fail at parsetime. We'll try to think of a better alternative.
One other thing, SQL Server doesn't evaluate IF statements during parse/compile so it doesn't know that you will not execute that step.
SQL guy and Houston Magician
January 3, 2007 at 2:01 pm
there is not much control over query compilation in 2005. I have been biten by this quite a bit. Even with Dynamic queries. The only posibility I see is to use dynamic sql as posted above but "on the update statement only".
* Noel
January 4, 2007 at 12:19 am
Thanks, I appreciate your responses.
Basically you're saying that a feature that worked perfect in SqlServer 2000 doesn't work in SqlServer 2005. I can't believe that something so fundamental is not working.
Oh well, back to bad ol' dynamic sql.
January 4, 2007 at 12:16 pm
How your query is working 2000?
It is not working either in 2000 or 2005... it is giving me the same error.
What version/Build you are on 2000?
Here is the test script I used to repro the problem...
drop table test
create table test ( id int, name sysname, dt datetime default getdate(), dt1 datetime )
insert into test
select 1, 'test', getdate(), getdate()+1
IF EXISTS
(select * from syscolumns
where id = object_id('test')
and name='Deadline' )
begin
update test
set dt = deadline
end
MohammedU
Microsoft SQL Server MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply