All my tests were connected to the target database.

-----Original Message-----
From: Julia Poddoubsky [mailto:[Email Address Removed] Monday, June 30, 2003 3:15 PM
To: LazyDBA.com Discussion
Subject: RE: SQL bug or what? Question on the max row size warning


I tried to run the script and found out that you have to execute the script
in the database you are looking for a column:
use your_database_name
code goes here

If you are executing the script from another database - the column is not
found and sql server "thinks' it doesn't exist.
Julia

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


Thanks but unfortunately, that didn't help. Still get the same
warning message.

And actually, I don't think either way to check the existence
of that column makes SQL 'thinks' the column doesn't exist,
because in neither sitution, I got the print 'ever get here'
which is in the IF section.

Any other thoughts?

-----Original Message-----
From: Julia Poddoubsky [mailto:[Email Address Removed] Monday, June 30, 2003 3:00 PM
To: LazyDBA.com Discussion
Subject: RE: SQL bug or what? Question on the max row size warning


Looks like your code "thinks" that the column doesn't exist.
I always use the following script to check if the column
already exists, try
it:

if not exists (select * from your_db_name..syscolumns where name =
'your_column_name' and id = (
select id from dbo.sysobjects where id =
object_id(N'[dbo].[you_table_name]') and OBJECTPROPERTY(id,
N'IsUserTable')
= 1)
)

change your_db_name, your_column_name, you_table_name to the
real names and
run.
Julia

-----Original Message-----
From: Fang, Elaine [mailto:Elaine.[Email Address Removed] Monday, June 30, 2003 12: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]MS Sql Server LazyDBA home page