Warning sign: What does it mean??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Warning sign: What does it mean???

Author

Topic

spinoza
Starting Member

49 Posts

Posted - 2006-01-06 : 15:37:22

I typed the following query

ALTER TABLE [konnik].[PORTAL_CONFIG]
ADD CONSTRAINT [DF_PORTAL_CONFIG_AUTOPM_ON]
DEFAULT (1) FOR [AUTOPM_ON]

and i received the following warning

Warning: The table 'PORTAL_CONFIG' has been created but its maximum row size (12068) 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.

Bear in mind that this table come from Access through the Dts. Could anyone translate this for me? How can i overcome this???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 15:45:29

If ever you insert or update a row to cause the row to be over 8060 bytes, then that insert/update will fail. For this reason, you should make sure that your table's maximum row size does not exceed 8000. If you need it to go over, then you must use text data type.

Post the DDL for PORTAL_CONFIG table and we can help show you exactly where the problem is.

Tara Kizer
aka tduggan

spinoza
Starting Member

49 Posts

Posted - 2006-01-06 : 16:03:44

sorry, I am quite new using SQL... what DDL means? and where i can find it?

Furthermore, those bytes get me confuse...bytes are equal to characters?? Sorry if it is so naive my question...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 16:07:47

quote:

sorry, I am quite new using SQL... what DDL means? and where i can find it?

Sorry! I should have explained that as you've posted in the newbie forum.

It means data definition language. That's the create table statement. You can easily generate this in EM by right clicking, all tasks, generate sql script, selecting your object. Typically I'd ask to select options on the third tab, but we won't need that information to help you out on this.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 16:10:57

quote:

Furthermore, those bytes get me confuse...bytes are equal to characters??

For character data types, yes. So if you've got a column that is defined as char(4000) and you fill that entire column, then its size is 4000 bytes.

quote:

Sorry if it is so naive my question...

No worries! That's why you've posted in this forum. We're more understanding of questions like these in this forum. I just had to realize that it was the newbie forum, which I didn't on my first post.

Tara Kizer
aka tduggan

spinoza
Starting Member

49 Posts

Posted - 2006-01-06 : 16:19:15

ok...Thank you very much indeed...here is the DDL...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PORTAL_CONFIG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PORTAL_CONFIG]
GO

CREATE TABLE [dbo].[PORTAL_CONFIG] (
[AUTOPM_MESSAGE] [ntext] COLLATE Greek_CI_AS NULL ,
[AUTOPM_ON] [int] NULL ,
[AUTOPM_SUBJECTLINE] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_CLASSIC] [tinyint] NULL ,
[C_ALLOWUPLOADS] [tinyint] NULL ,
[C_DOWNMSG] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_FEATUREDPOLL] [int] NULL ,
[C_FORUMSTATUS] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_FORUMSUBSCRIPTION] [int] NULL ,
[C_ICALEXIST] [int] NULL ,
[C_ICALNEW] [int] NULL ,
[C_INTHOTTOPICNUM] [int] NULL ,
[C_INTRANKLEVEL0] [int] NULL ,
[C_INTRANKLEVEL1] [int] NULL ,
[C_INTRANKLEVEL2] [int] NULL ,
[C_INTRANKLEVEL3] [int] NULL ,
[C_INTRANKLEVEL4] [int] NULL ,
[C_INTRANKLEVEL5] [int] NULL ,
[C_JOKEOFTHEWEEK] [int] NULL ,
[C_PAGEWIDTH] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_PMTYPE] [tinyint] NULL ,
[C_POLLCREATE] [int] NULL ,
[C_REMINDERS] [int] NULL ,
[C_STRACTIVELINKCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRAGE] [int] NULL ,
[C_STRAIM] [tinyint] NULL ,
[C_STRALLOWFORUMCODE] [tinyint] NULL ,
[C_STRALLOWHTML] [tinyint] NULL ,
[C_STRALTFORUMCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRALTHEADCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRAUTHTYPE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRAUTOLOGON] [int] NULL ,
[C_STRBADWORDFILTER] [tinyint] NULL ,
[C_STRBADWORDS] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRBIO] [int] NULL ,
[C_STRCATEGORYCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRCATEGORYFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRCITY] [int] NULL ,
[C_STRCOPYRIGHT] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRCOUNTRY] [int] NULL ,
[C_STRDATETYPE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRDEFAULTFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRDEFAULTFONTFACE] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRDEFAULTFONTSIZE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRDEFTHEME] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STREDITEDBYDATE] [tinyint] NULL ,
[C_STREMAIL] [tinyint] NULL ,
[C_STREMAILVAL] [int] NULL ,
[C_STRFAVLINKS] [int] NULL ,
[C_STRFLOODCHECK] [int] NULL ,
[C_STRFLOODCHECKTIME] [int] NULL ,
[C_STRFOOTERFONTSIZE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRFORUMCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRFORUMFIRSTCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRFORUMFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRFORUMLINKCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRFORUMURL] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRFULLNAME] [int] NULL ,
[C_STRGFXBUTTONS] [tinyint] NULL ,
[C_STRGLOW] [tinyint] NULL ,
[C_STRHEADCELLCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRHEADERFONTSIZE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRHEADERTYPE] [int] NULL ,
[C_STRHEADFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRHIDEEMAIL] [tinyint] NULL ,
[C_STRHOBBIES] [int] NULL ,
[C_STRHOMEPAGE] [tinyint] NULL ,
[C_STRHOMEURL] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRHOTTOPIC] [tinyint] NULL ,
[C_STRHOVERFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRHOVERTEXTDECORATION] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRICONS] [tinyint] NULL ,
[C_STRICQ] [tinyint] NULL ,
[C_STRICSLOCATION] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRIMGINPOSTS] [tinyint] NULL ,
[C_STRIPGATEBAN] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATECOK] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATECSS] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATEEXP] [nvarchar] (3) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATELCK] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATELKMSG] [nvarchar] (100) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATELOG] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATEMET] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATEMSG] [nvarchar] (100) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATENOACMSG] [nvarchar] (100) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATETYP] [nvarchar] (2) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATEVER] [nvarchar] (15) COLLATE Greek_CI_AS NULL ,
[C_STRIPGATEWARNMSG] [nvarchar] (100) COLLATE Greek_CI_AS NULL ,
[C_STRIPLOGGING] [tinyint] NULL ,
[C_STRLINKCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRLINKTEXTDECORATION] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRLNEWS] [int] NULL ,
[C_STRLOGINTYPE] [tinyint] NULL ,
[C_STRLOGONFORMAIL] [int] NULL ,
[C_STRMAILMODE] [nvarchar] (100) COLLATE Greek_CI_AS NULL ,
[C_STRMAILSERVER] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRMARSTATUS] [int] NULL ,
[C_STRMOVETOPICMODE] [tinyint] NULL ,
[C_STRMSN] [tinyint] NULL ,
[C_STRNEWFONTCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRNEWREG] [int] NULL ,
[C_STRNOCOOKIES] [tinyint] NULL ,
[C_STRNTGROUPS] [int] NULL ,
[C_STROCCUPATION] [int] NULL ,
[C_STRPAGEBGCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRPAGEBGIMAGE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRPAGENUMBERSIZE] [int] NULL ,
[C_STRPAGESIZE] [int] NULL ,
[C_STRPICTURE] [int] NULL ,
[C_STRPOPUPBORDERCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRPOPUPTABLECOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRPRIVATEFORUMS] [tinyint] NULL ,
[C_STRQUICKREPLY] [int] NULL ,
[C_STRQUOTE] [int] NULL ,
[C_STRRANKADMIN] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR0] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR1] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR2] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR3] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR4] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLOR5] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLORADMIN] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKCOLORMOD] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL0] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL1] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL2] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL3] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL4] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKLEVEL5] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRANKMOD] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRRECENTTOPICS] [int] NULL ,
[C_STRSECUREADMIN] [tinyint] NULL ,
[C_STRSENDER] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRSETCOOKIETOFORUM] [tinyint] NULL ,
[C_STRSEX] [int] NULL ,
[C_STRSHOWIMAGEPOWEREDBY] [int] NULL ,
[C_STRSHOWMODERATORS] [tinyint] NULL ,
[C_STRSHOWPAGING] [int] NULL ,
[C_STRSHOWRANK] [tinyint] NULL ,
[C_STRSHOWSTATISTICS] [int] NULL ,
[C_STRSHOWTOPICNAV] [int] NULL ,
[C_STRSIGNATURES] [tinyint] NULL ,
[C_STRSITETITLE] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRSTATE] [int] NULL ,
[C_STRTABLEBORDERCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRTIMEADJUST] [int] NULL ,
[C_STRTIMEADJUSTLOCATION] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRTIMETYPE] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRTITLEIMAGE] [nvarchar] (255) COLLATE Greek_CI_AS NULL ,
[C_STRTOPICNOWRAPLEFT] [tinyint] NULL ,
[C_STRTOPICNOWRAPRIGHT] [tinyint] NULL ,
[C_STRTOPICWIDTHLEFT] [nvarchar] (10) COLLATE Greek_CI_AS NULL ,
[C_STRTOPICWIDTHRIGHT] [nvarchar] (10) COLLATE Greek_CI_AS NULL ,
[C_STRUNIQUEEMAIL] [tinyint] NULL ,
[C_STRVAR1] [nvarchar] (20) COLLATE Greek_CI_AS NULL ,
[C_STRVAR2] [nvarchar] (20) COLLATE Greek_CI_AS NULL ,
[C_STRVAR3] [nvarchar] (20) COLLATE Greek_CI_AS NULL ,
[C_STRVAR4] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRVISITEDLINKCOLOR] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRVISITEDTEXTDECORATION] [nvarchar] (50) COLLATE Greek_CI_AS NULL ,
[C_STRYAHOO] [tinyint] NULL ,
[C_STRZIP] [tinyint] NULL ,
[CONFIG_ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 16:27:41

Are you going to be storing unicode characters in your varchar columns? When you use nvarchar or nchar, the size is double. So nvarchar(50) equals 100 if you fill the entire column. With varchar, it would only have been 50. So if you don't plan on storing foreign language in those columns, switch them to varchar or char. You should be within the limits if you make those changes.

Tara Kizer
aka tduggan

spinoza
Starting Member

49 Posts

Posted - 2006-01-06 : 16:38:21

I use foreign language for my portal...the Greek language...so is there any solution to this??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 16:42:26

You'll have to shorten the table by removing columns. You are 4000 bytes over the limit right now.

It looks like this is a denormalized table (you'd have to do some reading on database design to understand this, specifically the normal forms) in the first place though, due to its size. Perhaps you could split up this table into multiple tables.

Tara Kizer
aka tduggan

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-09 : 04:30:06

This looks like a classic example of "a table designed by a developer". It looks like someone has thought of what bits of information they might ever need to store and jotted them down to make one big table.

If you follow up on Tara's suggestion and read a bit about normalization then you might be able to reduce the table to a sensible size, and future proof it at the same time. A good place to start will be all the data items where you have numbers 0-5 after them. They could mostly be extracted into seperate tables. You may have a need for only 5 items now, but what about when you need the sixth item in the future?

-------
Moo. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-09 : 04:45:50

do normalize.
but if that's not possible for you use ntext columns for storing text.
it's ugly but it works.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources