tough error to debug - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

tough error to debug

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-10 : 16:14:14

Hi experts,
I do not know what causes the error like
"Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition."

When I run the following SQL scripts that are very simple:

create table #t(id int)

insert #t
select 1
union all
select 2

declare @sql varchar(1000)

set @sql = 'alter table #t ADD name varchar(50) NULL'

exec (@sql)

select * from #t

drop table #t

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:15:55

Your example code does not produce the error, so please provide an example that does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 16:28:59

"Your example code does not produce the error"

It does under SQL 2000

- but not SQL 2005 ...

Works in SQL 2000 if I just run the Create / Insert, and then separately run the Declare ... Drop

So perhaps the Optimiser mis-guessing the shape of #t because of the ALTER TABLE statement?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:30:33

I ran it in 2000 (QA) but didn't realize I had connected to a 2005 server.

Why is dynamic SQL being used here?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:32:59

Just add a GO statement before the alter to get it to work. The parser is apparently confused.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-10 : 16:54:05

quote:
Originally posted by tkizer

Just add a GO statement before the alter to get it to work. The parser is apparently confused.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

--
yes, I ran the query in SQL 2000 not 2005
yes. I add a GO and the scripts work. My question is "Why does the GO statement solve the problem?"

create table #t(id int)

insert #t
select 1
union all
select 2

declare @sql varchar(1000)

set @sql = 'alter table #t ADD [mname] varchar(50) NULL'

exec (@sql)

GO

--
select * from #t

--
update #t set [mname]='reng'

--
select * from #t

drop table #t

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-10 : 16:56:04

quote:
Originally posted by tkizer

I ran it in 2000 (QA) but didn't realize I had connected to a 2005 server.

Why is dynamic SQL being used here?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I want Dynamic Pivot table for Crosstab querying, so I use dyanmic SQL here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 16:59:52

quote:
Originally posted by johnsql

yes, I ran the query in SQL 2000 not 2005
yes. I add a GO and the scripts work. My question is "Why does the GO statement solve the problem?"

The parser seems to have a bug. Looks like it's resolved in 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources