I suspect that this really has to do with the parser and how it handles DDL
statements. More than likely it is just parsing the statement and
determining that adding the new column will make the total row size greater
than the maximum length. What happens if you comment out the ALTER
statement?

Kevin Martin
Database Engineer
Multimedia Games



-----Original Message-----
From: Fang, Elaine [mailto:Elaine.[Email Address Removed] Monday, June 30, 2003 2:56 PM
To: LazyDBA.com Discussion
Subject: SQL bug or what? Question on the max row size warning


Hi list,

I have no problem (error message etc) when I created table 'contacts' which
has quite a few columns. However, if I run the following code segment, I
will get

Warning: The table 'Contacts' has been created but its maximum row size
(8083) 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.
password column is already there

Here is the code:

-- in my case, 'password' column is already there
IF NOT EXISTS (SELECT sc.id FROM syscolumns sc INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = 'Contacts' and
sc.name = 'Password')
BEGIN
-- should not get here since that column already exists
print 'ever get here?'
PRINT 'Adding columns to Contacts table......'
ALTER TABLE Contacts
ADD Password nvarchar (12) NULL
IF @@ERROR <> 0
RAISERROR(' Error adding columns to Contacts table!', 16, 1)
END
ELSE
begin
print 'password column is already there'
end
GO

My questions are --

1. If the row is too wide, why didn't I get warning during table creation
time?

2. Since it only goes to the 'else' section of above code segment, how would
it still get into the Alter statement? My test showed that if I remove the
Alter table in the IF clause, then the warning message will be gone.

TIA...

Elaine







---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 6/10/2003


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 6/10/2003

MS Sql Server LazyDBA home page