SQL SERVER 2000 to 2008 Migration: Bug in T-SQL 2K

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL SERVER 2000 to 2008 Migration: Bug in T-SQL 2K

Author

Topic

skamie
Starting Member

1 Post

Posted - 2008-11-29 : 03:08:06

Hi ,

I am working on migrating some SQL SERVER 2000 databases to SQL SERVER 2008. A piece of code which is still working in 2000 is failing in 2008 (in 2005 also).

The code goes like this

declare @columns table (ColumnName nvarchar(128)
,DefaultExp nvarchar(128)
,IsNullable varchar(3)
,HasSource bit)

IF Object_Id('tempdb.dbo.#TargetColumns') is not null
drop table #TargetColumns

create table #TargetColumns (ColumnName nvarchar(128)
,data_type nvarchar(128)
,OrdinalPosition int
,IsNullable varchar(3)
,HasSource bit
,DefaultExp nvarchar(128)
,AutoVal tinyint)

INSERT INTO #TargetColumns VALUES( 'C1', 'INT', 1, 'No', 1, '',0)
INSERT INTO #TargetColumns VALUES( 'C2', 'INT', 2, 'No', 1, '',0)
INSERT INTO #TargetColumns VALUES( 'C3', 'INT', 3, 'No', 0, '',0)
INSERT INTO #TargetColumns VALUES( 'C4', 'INT', 4, 'No', 1, '',0)

insert @columns
select ColumnName, DefaultExp, IsNullable from #TargetColumns
where HasSource = 1
and columnname not in ('InsertTime','InsertDate')
order by OrdinalPosition

select *from @columns

The code highlighted in Red is working fine in SQL SERVER 2000 but fails in 2005 & 2008 with “Insert Error: Column name or number of supplied values does not match table definition.”

It is obvious that the code has to fail, because the target table is having 4 columns, the insert did not specify column list & the select statement returns only 3 columns.

But in SQL 2000 it is working fine. It inserted 3 records and for the last column it inserted NULL. I was wondering why it did not fail with Column count mismatch error.

So I commented the WHERE part, still it worked fine. Then I commented the ORDER BY clause, then it FAILED. I changed the ORDER BY column with a column in select statement (OrdinalPosition with ColumnName) it FAILED.

From this I infer that, in SQL SERVER 2000, columns listed in the ORDER BY clause is included in the SELECT list. In our case “OrdinalPosition” is added in the SELECT columns, thereby increasing the count to 4 and matched with source tables column count). Interesting thing is for the last colum, it has inserted NULL not the value of OrdinalPosition (the column specified in ORDER BY clause) which is correct.

In the above code I changed the temporary table with a permanent table. It still worked fine in 2000. It failed in SQL 20005 & 2008.

My question is whether my inference is correct? Is this a bug or feature in SQL SERVER 2000 which is removed from SQL 2005 onwards.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-29 : 05:35:18

Bug if you want to call it a bug. Truth is, the SQL 2000 T-SQL parser was far less strict about what it considered valid SQL than the 2005 and 2008 parsers are. There are a few constructs that work on 2000 and don't work on 2005, 2008.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources