Warning message
-
Hi all,
I've just run the script to create a couple of tables in my database and I received this message:
Warning: The table 'Email_Template' has been created but its maximum row size (8292) 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.
I'm not sure exactly what it means because I didn't copy any data, I was just creating the tables.
More background: the tables already existed in the database, but I wanted to clear out the data and get the identity back to 1, so I generated the script in EM, dropped the tables, then ran the script in QA and that's when I received the message.
Here's the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Email_Detail_Email_Template]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Email_Detail] DROP CONSTRAINT FK_Email_Detail_Email_Template
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SetUpdatedEmail_Detail]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[SetUpdatedEmail_Detail]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SETUpdated_Email_Template]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[SETUpdated_Email_Template]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Email_Detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Email_Detail]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Email_Template]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Email_Template]
GO
CREATE TABLE [dbo].[Email_Detail] (
[DetailId] [int] IDENTITY (1, 1) NOT NULL ,
[TemplateId] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Division] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Active] [bit] NOT NULL ,
[Deleted] [bit] NOT NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Email_Template] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Body] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EFrom] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NOT NULL ,
[Deleted] [bit] NOT NULL ,
[LastUpdated] [datetime] NOT NULL ,
[LastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Email_Detail] WITH NOCHECK ADD
CONSTRAINT [PK_Email_Detail] PRIMARY KEY CLUSTERED
(
[DetailId]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Email_Template] WITH NOCHECK ADD
CONSTRAINT [PK_Email_Template] PRIMARY KEY CLUSTERED
(
[Id]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Email_Detail] WITH NOCHECK ADD
CONSTRAINT [DF_Email_Detail_Active] DEFAULT (1) FOR [Active],
CONSTRAINT [DF_Email_Detail_Deleted] DEFAULT (0) FOR [Deleted],
CONSTRAINT [DF_Email_Detail_LastUpdated] DEFAULT (getdate()) FOR [LastUpdated]
GO
ALTER TABLE [dbo].[Email_Template] WITH NOCHECK ADD
CONSTRAINT [DF_Email_Template_Active] DEFAULT (1) FOR [Active],
CONSTRAINT [DF_Email_Template_Deleted] DEFAULT (0) FOR [Deleted],
CONSTRAINT [DF_Email_Template_LastUpdated] DEFAULT (getdate()) FOR [LastUpdated]
GO
ALTER TABLE [dbo].[Email_Detail] ADD
CONSTRAINT [FK_Email_Detail_Email_Template] FOREIGN KEY
(
[TemplateId]
 
REFERENCES [dbo].[Email_Template] ([Id]
 
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [SetUpdatedEmail_Detail] ON [dbo].[Email_Detail]
AFTER INSERT, UPDATE
AS
UPDATE Email_Detail
SET LastUpdated = GETDATE()
WHERE DetailId IN (SELECT DetailId
FROM INSERTED)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [SETUpdated_Email_Template] ON dbo.Email_Template
FOR INSERT, UPDATE
AS
UPDATE Email_Template
SET LastUpdated = GETDATE()
WHERE [Id] IN (SELECT [id]
FROM INSERTED)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Melissa,
if you look at the table Email_Templates and add the maximum size that each column can have, you'll see that is more than 8060 which is the maximum recommeded value per row. The fact that those possible large columns can contain less data than that maximum makes SQL Server give you the posiblity to still create it but it warns you that there may be problems with it. You can always trim the size of the largest (I hope) to get rid of the warning
Hope is Clear.
* Noel -
hi,
in your case if you really need to store more than 8000 characters in your row, i would then recommend creating a field of the type "TEXT".
the way a field of type TEXT is stored by SQL Server is different that it would store a field with datatype VARCHAR(8000).
hope this helps.
-- Parag
-
I second that - the column "Body" in Email_Templates needs to be of "text" datatype.
**ASCII stupid question, get a stupid ANSI !!!** -
Okay, thanks for the great help all!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply