Changing the data type to avoid Msg 511

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)

Changing the data type to avoid Msg 511

Author

Topic

yelena
Starting Member

1 Post

Posted - 2005-01-03 : 21:47:29

I have a table
CREATE TABLE [dbo].[DealCompanyInfo] (
[IDDealCompanyInfo] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IDCompany] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sAlternateName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sBusinessDescription] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sDealID] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sYearEstablished] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupDealStage] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupDealSource] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sDealSourceName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupDealPriority] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateIntroduction] [datetime] NULL ,
[IDLookupCurrency] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDGroupInfo] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupIndustry] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupIndustrySubset] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupDealStatus] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtStatusDate] [datetime] NULL ,
[dtExpectedInvestmentDate] [datetime] NULL ,
[sWhoPassed] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sWhoCommunicatedPass] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sWhyPassed] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sComments] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fMinInvestmentSize] [float] NULL ,
[fMaxInvestmentSize] [float] NULL ,
[fTotalSize] [float] NULL ,
[fCapitalRaised] [float] NULL ,
[dtCallBackDate] [datetime] NULL ,
[dtCreatedDate] [datetime] NOT NULL ,
[sCreatedBy] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdatedDate] [datetime] NULL ,
[sUpdatedBy] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[IDReceivedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sProduct] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sHistory] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sManagement] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sMarket] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sCapital] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sOpportunity] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sRisk] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sExit] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sFocus] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sAdvantage] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sMarketComment] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sReviewComment] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fRevenue] [float] NULL ,
[fEBITDA] [float] NULL ,
[IDLookupFSFormat] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupValuationBasics] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupFundCategory] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupFundCategory1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupFundCategory3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupInvOrientation] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupClassification] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtLiquidationDate] [datetime] NULL ,
[IDLookupDealType] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDLookupInvDecision] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDContactInfoPartner] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDContactInfoAssociate] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

---------------
and I am trying to alter it
When I run the following:
---------------
Alter Table DealCompanyInfo alter column sBusinessDescription text Null
Alter Table DealCompanyInfo alter column sComments text Null
Alter Table DealCompanyInfo alter column sMarketComment text Null
Alter Table DealCompanyInfo alter column sReviewComment text Null

------
I got
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8148 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Could you please advise?
Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 22:14:24

Have a look at this
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

Alter table wil make a copy of fixed length columns which is probably what is causing your problem.
It's always better to make a new table, copy the data and rename.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources