Exceeding max row size... but not really

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

Transact-SQL (2000)

Exceeding max row size... but not really

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-20 : 12:27:04

Guys,
I have a script that adds a column to an existing table:


if NOT exists (select * from dbo.syscolumns where name = 'MyCol' and id = object_id(N'[MyTable]'))
begin
	alter table dbo.MyTable add MyCol nvarchar(4000) null
end else
begin
	print '2'
end

Upon first execution, the table is created and everything is cool. If we re-reun the script (which of course checks first to see if the column exists) it appears that the logic check succeeds as '2' is printed to the screen, though I also receive the following error/warning:

Warning: The table 'MyTable' has been created but its maximum row size (16053) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
2

What is happening behind the scenes?

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-07-20 : 13:21:39

Some sort of argument between the Parser and the Executer I expect.

If you change:

alter table dbo.MyTable add MyCol nvarchar(4000) null

to

exec ('alter table dbo.MyTable add MyCol nvarchar(4000) null')

does the message go away?

Kristen

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-20 : 13:28:06

yup, the error is gone.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources