SQL error

[Lin Ma]

Does anyone can tell me what is wrong with my SQL server?

My database field size is 3000 with nvarchar.

I got an error

Microsoft]{ODBC SQL server Driver} [SQL server]
Cannot create a row of size 8087 which is greater than the allowable max of
8060.
[Microsoft]{SQL Server} The statement has been terminated.

Thanks for your help.

Lin Ma

[Aaron Bertrand - MVP]

You have too many columns, or columns that are too large, aside from this
one "field" which has a size of 3000. You can only put 8060 bytes in a row,
so if your total column defined sizes are greater than that, you will get a
warning (not an error!) when creating the table, and inserts that fully
populate all of the defined size will generate an error. Can you show your
CREATE TABLE statement? Also, how exactly are you generating this error?





"Lin Ma" <a@a.com> wrote in message
news:#NZbhVJeDHA.2428@TK2MSFTNGP09.phx.gbl...

> Does anyone can tell me what is wrong with my SQL server?
>
> My database field size is 3000 with nvarchar.
>
> I got an error
>
> Microsoft]{ODBC SQL server Driver} [SQL server]
> Cannot create a row of size 8087 which is greater than the allowable max

of

> 8060.
> [Microsoft]{SQL Server} The statement has been terminated.
>
> Thanks for your help.
>
> Lin Ma
>
>

[Lin Ma]

Aaron,

Thanks. The information I inset into the field is too large.

But I limited to 2500 characters. I think the field size is 3000 will be OK.

Does field size = 3000 means can hold 3000 characters?

Finally, I change the field type to TEXT and resolved the problem.

Am I doing correct?

Thanks,

Lin Ma





"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eFAx8bJeDHA.2300@TK2MSFTNGP10.phx.gbl...

> You have too many columns, or columns that are too large, aside from this
> one "field" which has a size of 3000. You can only put 8060 bytes in a

row,

> so if your total column defined sizes are greater than that, you will get

a

> warning (not an error!) when creating the table, and inserts that fully
> populate all of the defined size will generate an error. Can you show

your

> CREATE TABLE statement? Also, how exactly are you generating this error?
>
>
>
>
>
> "Lin Ma" <a@a.com> wrote in message
> news:#NZbhVJeDHA.2428@TK2MSFTNGP09.phx.gbl...

> > Does anyone can tell me what is wrong with my SQL server?
> >
> > My database field size is 3000 with nvarchar.
> >
> > I got an error
> >
> > Microsoft]{ODBC SQL server Driver} [SQL server]
> > Cannot create a row of size 8087 which is greater than the allowable max

> of

> > 8060.
> > [Microsoft]{SQL Server} The statement has been terminated.
> >
> > Thanks for your help.
> >
> > Lin Ma
> >
> >

>
>

[Aaron Bertrand - MVP]

> Does field size = 3000 means can hold 3000 characters?

Yes. However, if your INSERT statement also inserts into your other
columns, and you go over 8060 bytes total (hint: it is not solely because of
*this* column!), you'll get the error.

> Finally, I change the field type to TEXT and resolved the problem.
>
> Am I doing correct?

In my opinion, no. Storing a maximum of 3,000 bytes in a TEXT column is
grossly inefficient. Once again, can you show your CREATE TABLE statement?
There are likely other ways you can get below the 8,060 byte threshold.

[Lin Ma]

Aaron,

Here is my long table. All the text field used to be the nvarchar 3000
field.

-------------- start table -------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CIA_Favorite_CIA]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CIA_Favorite] DROP CONSTRAINT FK_CIA_Favorite_CIA
GO

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

CREATE TABLE [dbo].[CIA] (
[CIANum] [int] IDENTITY (1, 1) NOT NULL ,
[CIAStatus] [int] NULL ,
[CIAOriginator] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CIAOriginatorID] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OrgDate] [smalldatetime] NULL ,
[SuntronSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIAType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIADepartment] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIASource] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIAResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIAProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIASubProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIAProblem] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditFindingNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AuditStandard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AuditSectionNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AuditDocNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AuditDocSectionNumber] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AssyNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssyRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartQuantity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteNCMRNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerCARNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerNCMRNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PONumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupplierName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupplierRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupplierPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupplierEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManageReviewDate] [smalldatetime] NULL ,
[CoordinatorPersonID] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CoordinationDate] [datetime] NULL ,
[CoordinationResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CoordinationComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CIAClassification] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InvestigationPersonID] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[InvestigationDate] [smalldatetime] NULL ,
[CIASimilarProcessYN] [bit] NULL ,
[CIASimilarProcess] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InvRootCauseToolsUsed] [nvarchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[InvRootCause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvContainmentAction] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvActionOption] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvProposedAction] [bit] NULL ,
[InvComments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvActionProposedDate] [smalldatetime] NULL ,
[InvCostEstCalc] [bit] NULL ,
[InvCostQuantified] [float] NULL ,
[InvCostUnQuantified] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InvBenefitEstCalc] [bit] NULL ,
[InvBenefitQuantified] [float] NULL ,
[InvBenefitUnQuantified] [nvarchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[InvOpportunityStatement] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VerificationPersonIDOld] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CIARejectReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvCompleteYN] [bit] NULL ,
[InvAddressRootCauseYN] [bit] NULL ,
[InvAdequateContainmentYN] [bit] NULL ,
[InvActionEffectiveYN] [bit] NULL ,
[InvCostBenefitAccurateYN] [bit] NULL ,
[InvActionAtThisTimeYN] [bit] NULL ,
[InvVerificationAcceptReject] [bit] NULL ,
[InvVerComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvVerifierPersonID] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvVerificationDate] [smalldatetime] NULL ,
[ProjectLeaderID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProjectTeamID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ActionResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionResultComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionEffectivityDate] [smalldatetime] NULL ,
[ActionActualEstimatedCost] [bit] NULL ,
[ActionCost] [float] NULL ,
[ActionCostComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionActualEstimatedBenefit] [bit] NULL ,
[ActionBenefit] [float] NULL ,
[ActionBenefitComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ActionDate] [smalldatetime] NULL ,
[ActionVerPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ActionPastDue] [bit] NULL ,
[ActionPastDueTime] [float] NULL ,
[ActionVer_ActionPerformedYN] [bit] NULL ,
[ActionVer_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionVerDate] [smalldatetime] NULL ,
[ValidationEffYN] [bit] NULL ,
[Validation_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ValidatorPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ValidationBackTo] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ValidationDate] [smalldatetime] NULL ,
[ReIssueCIA] [int] NULL ,
[RejectCIANum] [int] NULL ,
[ReIssueCIANum] [int] NULL ,
[CIATimeSpend] [float] NULL ,
[CIAClosedDate] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


----------------end table -----------------




"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:uqh0UuJeDHA.2304@TK2MSFTNGP11.phx.gbl...

> > Does field size = 3000 means can hold 3000 characters?

>
> Yes. However, if your INSERT statement also inserts into your other
> columns, and you go over 8060 bytes total (hint: it is not solely because

of

> *this* column!), you'll get the error.
>

> > Finally, I change the field type to TEXT and resolved the problem.
> >
> > Am I doing correct?

>
> In my opinion, no. Storing a maximum of 3,000 bytes in a TEXT column is
> grossly inefficient. Once again, can you show your CREATE TABLE

statement?

> There are likely other ways you can get below the 8,060 byte threshold.
>
>

[Aaron Bertrand - MVP]

Yikes... are you sure all of those nvarchar(50) columns require 50
characters? And the larger ones too? Do they really all have to be
nvarchar (will CIADepartment, for example, ever really need to support
foreign alphabets)? Also, could none of this information go in a peripheral
table?

The way it is now, you may as well just store this data in flat files. I
can't imagine having to use this table for anything, and expecting half
decent performance...




"Lin Ma" <a@a.com> wrote in message
news:emLO00JeDHA.1820@TK2MSFTNGP10.phx.gbl...

> Aaron,
>
> Here is my long table. All the text field used to be the nvarchar 3000
> field.
>
> -------------- start table -------------
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_CIA_Favorite_CIA]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[CIA_Favorite] DROP CONSTRAINT FK_CIA_Favorite_CIA
> GO
>
> if exists (select * from dbo.sysobjects where id =

object_id(N'[dbo].[CIA]')

> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[CIA]
> GO
>
> CREATE TABLE [dbo].[CIA] (
> [CIANum] [int] IDENTITY (1, 1) NOT NULL ,
> [CIAStatus] [int] NULL ,
> [CIAOriginator] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [CIAOriginatorID] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OrgDate] [smalldatetime] NULL ,
> [SuntronSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIADepartment] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [CIASource] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIASubProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [CIAProblem] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AuditNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AuditFindingNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AuditStandard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [AuditSectionNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AuditDocNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [AuditDocSectionNumber] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AssyNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AssyRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartQuantity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [SiteNCMRNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [CustomerName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [CustomerRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CustomerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [CustomerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [CustomerCARNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CustomerNCMRNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [PONumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [SupplierRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [SupplierEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [ManageReviewDate] [smalldatetime] NULL ,
> [CoordinatorPersonID] [nvarchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [CoordinationDate] [datetime] NULL ,
> [CoordinationResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CoordinationComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAClassification] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [InvestigationPersonID] [nvarchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvestigationDate] [smalldatetime] NULL ,
> [CIASimilarProcessYN] [bit] NULL ,
> [CIASimilarProcess] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [InvRootCauseToolsUsed] [nvarchar] (200) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvRootCause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvContainmentAction] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvActionOption] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvProposedAction] [bit] NULL ,
> [InvComments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvActionProposedDate] [smalldatetime] NULL ,
> [InvCostEstCalc] [bit] NULL ,
> [InvCostQuantified] [float] NULL ,
> [InvCostUnQuantified] [nvarchar] (200) COLLATE

SQL_Latin1_General_CP1_CI_AS

> NULL ,
> [InvBenefitEstCalc] [bit] NULL ,
> [InvBenefitQuantified] [float] NULL ,
> [InvBenefitUnQuantified] [nvarchar] (200) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvOpportunityStatement] [text] COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [VerificationPersonIDOld] [nvarchar] (3000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIARejectReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvCompleteYN] [bit] NULL ,
> [InvAddressRootCauseYN] [bit] NULL ,
> [InvAdequateContainmentYN] [bit] NULL ,
> [InvActionEffectiveYN] [bit] NULL ,
> [InvCostBenefitAccurateYN] [bit] NULL ,
> [InvActionAtThisTimeYN] [bit] NULL ,
> [InvVerificationAcceptReject] [bit] NULL ,
> [InvVerComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvVerifierPersonID] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvVerificationDate] [smalldatetime] NULL ,
> [ProjectLeaderID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [ProjectTeamID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,
> [ActionResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [ActionResultComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionEffectivityDate] [smalldatetime] NULL ,
> [ActionActualEstimatedCost] [bit] NULL ,
> [ActionCost] [float] NULL ,
> [ActionCostComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionActualEstimatedBenefit] [bit] NULL ,
> [ActionBenefit] [float] NULL ,
> [ActionBenefitComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ActionDate] [smalldatetime] NULL ,
> [ActionVerPersonID] [nvarchar] (3000) COLLATE

SQL_Latin1_General_CP1_CI_AS

> NULL ,
> [ActionPastDue] [bit] NULL ,
> [ActionPastDueTime] [float] NULL ,
> [ActionVer_ActionPerformedYN] [bit] NULL ,
> [ActionVer_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionVerDate] [smalldatetime] NULL ,
> [ValidationEffYN] [bit] NULL ,
> [Validation_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ValidatorPersonID] [nvarchar] (3000) COLLATE

SQL_Latin1_General_CP1_CI_AS

> NULL ,
> [ValidationBackTo] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> [ValidationDate] [smalldatetime] NULL ,
> [ReIssueCIA] [int] NULL ,
> [RejectCIANum] [int] NULL ,
> [ReIssueCIANum] [int] NULL ,
> [CIATimeSpend] [float] NULL ,
> [CIAClosedDate] [smalldatetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>
> ----------------end table -----------------
>
>
>
>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:uqh0UuJeDHA.2304@TK2MSFTNGP11.phx.gbl...

> > > Does field size = 3000 means can hold 3000 characters?

> >
> > Yes. However, if your INSERT statement also inserts into your other
> > columns, and you go over 8060 bytes total (hint: it is not solely

because

> of

> > *this* column!), you'll get the error.
> >

> > > Finally, I change the field type to TEXT and resolved the problem.
> > >
> > > Am I doing correct?

> >
> > In my opinion, no. Storing a maximum of 3,000 bytes in a TEXT column is
> > grossly inefficient. Once again, can you show your CREATE TABLE

> statement?

> > There are likely other ways you can get below the 8,060 byte threshold.
> >
> >

>
>

[Lin Ma]

Thanks Aaron.

My database admin told me to use nvarchar and said to store more characters.

I am not good at database design but will make batter next time.

Do you know any good SQL database design resource link?

Thanks for the teaching.

Lin Ma


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:usZ7P7JeDHA.3428@tk2msftngp13.phx.gbl...

> Yikes... are you sure all of those nvarchar(50) columns require 50
> characters? And the larger ones too? Do they really all have to be
> nvarchar (will CIADepartment, for example, ever really need to support
> foreign alphabets)? Also, could none of this information go in a

peripheral

> table?
>
> The way it is now, you may as well just store this data in flat files. I
> can't imagine having to use this table for anything, and expecting half
> decent performance...
>
>
>
>
> "Lin Ma" <a@a.com> wrote in message
> news:emLO00JeDHA.1820@TK2MSFTNGP10.phx.gbl...

> > Aaron,
> >
> > Here is my long table. All the text field used to be the nvarchar 3000
> > field.
> >
> > -------------- start table -------------
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[FK_CIA_Favorite_CIA]') and OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[CIA_Favorite] DROP CONSTRAINT FK_CIA_Favorite_CIA
> > GO
> >
> > if exists (select * from dbo.sysobjects where id =

> object_id(N'[dbo].[CIA]')

> > and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[CIA]
> > GO
> >
> > CREATE TABLE [dbo].[CIA] (
> > [CIANum] [int] IDENTITY (1, 1) NOT NULL ,
> > [CIAStatus] [int] NULL ,
> > [CIAOriginator] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [CIAOriginatorID] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OrgDate] [smalldatetime] NULL ,
> > [SuntronSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [CIAType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CIADepartment] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [CIASource] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CIAResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [CIAProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [CIASubProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [CIAProblem] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AuditNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [AuditFindingNumber] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [AuditStandard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [AuditSectionNumber] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [AuditDocNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [AuditDocSectionNumber] [nvarchar] (50) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AssyNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [AssyRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PartNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [PartRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PartQuantity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [SiteNCMRNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [CustomerName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [CustomerRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [CustomerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [CustomerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [CustomerCARNumber] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [CustomerNCMRNumber] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [PONumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [SupplierName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [SupplierRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [SupplierPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [SupplierEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [ManageReviewDate] [smalldatetime] NULL ,
> > [CoordinatorPersonID] [nvarchar] (3000) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CoordinationDate] [datetime] NULL ,
> > [CoordinationResult] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [CoordinationComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [CIAClassification] [nvarchar] (50) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [InvestigationPersonID] [nvarchar] (3000) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvestigationDate] [smalldatetime] NULL ,
> > [CIASimilarProcessYN] [bit] NULL ,
> > [CIASimilarProcess] [nvarchar] (500) COLLATE

SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [InvRootCauseToolsUsed] [nvarchar] (200) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvRootCause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvContainmentAction] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [InvActionOption] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvProposedAction] [bit] NULL ,
> > [InvComments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvActionProposedDate] [smalldatetime] NULL ,
> > [InvCostEstCalc] [bit] NULL ,
> > [InvCostQuantified] [float] NULL ,
> > [InvCostUnQuantified] [nvarchar] (200) COLLATE

> SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [InvBenefitEstCalc] [bit] NULL ,
> > [InvBenefitQuantified] [float] NULL ,
> > [InvBenefitUnQuantified] [nvarchar] (200) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvOpportunityStatement] [text] COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [VerificationPersonIDOld] [nvarchar] (3000) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CIARejectReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvCompleteYN] [bit] NULL ,
> > [InvAddressRootCauseYN] [bit] NULL ,
> > [InvAdequateContainmentYN] [bit] NULL ,
> > [InvActionEffectiveYN] [bit] NULL ,
> > [InvCostBenefitAccurateYN] [bit] NULL ,
> > [InvActionAtThisTimeYN] [bit] NULL ,
> > [InvVerificationAcceptReject] [bit] NULL ,
> > [InvVerComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [InvVerifierPersonID] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [InvVerificationDate] [smalldatetime] NULL ,
> > [ProjectLeaderID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [ProjectTeamID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS

> NULL

> > ,
> > [ActionResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [ActionResultComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [ActionEffectivityDate] [smalldatetime] NULL ,
> > [ActionActualEstimatedCost] [bit] NULL ,
> > [ActionCost] [float] NULL ,
> > [ActionCostComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ActionActualEstimatedBenefit] [bit] NULL ,
> > [ActionBenefit] [float] NULL ,
> > [ActionBenefitComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

> > [ActionPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [ActionDate] [smalldatetime] NULL ,
> > [ActionVerPersonID] [nvarchar] (3000) COLLATE

> SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [ActionPastDue] [bit] NULL ,
> > [ActionPastDueTime] [float] NULL ,
> > [ActionVer_ActionPerformedYN] [bit] NULL ,
> > [ActionVer_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ActionVerDate] [smalldatetime] NULL ,
> > [ValidationEffYN] [bit] NULL ,
> > [Validation_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ValidatorPersonID] [nvarchar] (3000) COLLATE

> SQL_Latin1_General_CP1_CI_AS

> > NULL ,
> > [ValidationBackTo] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS

NULL

> ,

> > [ValidationDate] [smalldatetime] NULL ,
> > [ReIssueCIA] [int] NULL ,
> > [RejectCIANum] [int] NULL ,
> > [ReIssueCIANum] [int] NULL ,
> > [CIATimeSpend] [float] NULL ,
> > [CIAClosedDate] [smalldatetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> >
> > ----------------end table -----------------
> >
> >
> >
> >
> > "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> > news:uqh0UuJeDHA.2304@TK2MSFTNGP11.phx.gbl...

> > > > Does field size = 3000 means can hold 3000 characters?
> > >
> > > Yes. However, if your INSERT statement also inserts into your other
> > > columns, and you go over 8060 bytes total (hint: it is not solely

> because

> > of

> > > *this* column!), you'll get the error.
> > >
> > > > Finally, I change the field type to TEXT and resolved the problem.
> > > >
> > > > Am I doing correct?
> > >
> > > In my opinion, no. Storing a maximum of 3,000 bytes in a TEXT column

is

> > > grossly inefficient. Once again, can you show your CREATE TABLE

> > statement?

> > > There are likely other ways you can get below the 8,060 byte

threshold.

> > >
> > >

> >
> >

>
>

[Aaron Bertrand [MVP]]

> Do you know any good SQL database design resource link?

[url]http://www.aspfaq.com/2423[/url]

[Jeff Cochran]

>Here is my long table. All the text field used to be the nvarchar 3000

>field.

If it's really text, you should be storing it as text anyway. Also, I
don't know the relationships between your data but it would seem that
normalizing the database would help quite a bit. You have customer,
supplier and product data all in the same table. Break as much of
this as makes sense into separate tables and use foreign keys to
reference them. For a good sample, look at the Northwind sample
database.

Also, it looks like you took defaults on many of these field lengths.
CUSTOMERPHONE is nvarchar(50), phone numbers are 10 digits. Even with
country code and a calling card you'd be hard pressed to reach more
tha 20 characters in any phone number.

Jeff

>-------------- start table -------------
>
>if exists (select * from dbo.sysobjects where id =
>object_id(N'[dbo].[FK_CIA_Favorite_CIA]') and OBJECTPROPERTY(id,
>N'IsForeignKey') = 1)
>ALTER TABLE [dbo].[CIA_Favorite] DROP CONSTRAINT FK_CIA_Favorite_CIA
>GO
>
>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CIA]')
>and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>drop table [dbo].[CIA]
>GO
>
>CREATE TABLE [dbo].[CIA] (
> [CIANum] [int] IDENTITY (1, 1) NOT NULL ,
> [CIAStatus] [int] NULL ,
> [CIAOriginator] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [CIAOriginatorID] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [OrgDate] [smalldatetime] NULL ,
> [SuntronSite] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIADepartment] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIASource] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIASubProduct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAProblem] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AuditNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AuditFindingNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [AuditStandard] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AuditSectionNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [AuditDocNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [AuditDocSectionNumber] [nvarchar] (50) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [AssyNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AssyRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartRev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PartQuantity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SiteNCMRNumber] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [CustomerName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CustomerRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CustomerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CustomerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CustomerCARNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [CustomerNCMRNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [PONumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierRep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SupplierEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManageReviewDate] [smalldatetime] NULL ,
> [CoordinatorPersonID] [nvarchar] (3000) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [CoordinationDate] [datetime] NULL ,
> [CoordinationResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [CoordinationComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIAClassification] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [InvestigationPersonID] [nvarchar] (3000) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvestigationDate] [smalldatetime] NULL ,
> [CIASimilarProcessYN] [bit] NULL ,
> [CIASimilarProcess] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [InvRootCauseToolsUsed] [nvarchar] (200) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvRootCause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvContainmentAction] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvActionOption] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvProposedAction] [bit] NULL ,
> [InvComments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvActionProposedDate] [smalldatetime] NULL ,
> [InvCostEstCalc] [bit] NULL ,
> [InvCostQuantified] [float] NULL ,
> [InvCostUnQuantified] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [InvBenefitEstCalc] [bit] NULL ,
> [InvBenefitQuantified] [float] NULL ,
> [InvBenefitUnQuantified] [nvarchar] (200) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvOpportunityStatement] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [VerificationPersonIDOld] [nvarchar] (3000) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [CIARejectReason] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvCompleteYN] [bit] NULL ,
> [InvAddressRootCauseYN] [bit] NULL ,
> [InvAdequateContainmentYN] [bit] NULL ,
> [InvActionEffectiveYN] [bit] NULL ,
> [InvCostBenefitAccurateYN] [bit] NULL ,
> [InvActionAtThisTimeYN] [bit] NULL ,
> [InvVerificationAcceptReject] [bit] NULL ,
> [InvVerComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvVerifierPersonID] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InvVerificationDate] [smalldatetime] NULL ,
> [ProjectLeaderID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [ProjectTeamID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [ActionResult] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionResultComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionEffectivityDate] [smalldatetime] NULL ,
> [ActionActualEstimatedCost] [bit] NULL ,
> [ActionCost] [float] NULL ,
> [ActionCostComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionActualEstimatedBenefit] [bit] NULL ,
> [ActionBenefit] [float] NULL ,
> [ActionBenefitComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [ActionDate] [smalldatetime] NULL ,
> [ActionVerPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [ActionPastDue] [bit] NULL ,
> [ActionPastDueTime] [float] NULL ,
> [ActionVer_ActionPerformedYN] [bit] NULL ,
> [ActionVer_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActionVerDate] [smalldatetime] NULL ,
> [ValidationEffYN] [bit] NULL ,
> [Validation_Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ValidatorPersonID] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
> [ValidationBackTo] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ValidationDate] [smalldatetime] NULL ,
> [ReIssueCIA] [int] NULL ,
> [RejectCIANum] [int] NULL ,
> [ReIssueCIANum] [int] NULL ,
> [CIATimeSpend] [float] NULL ,
> [CIAClosedDate] [smalldatetime] NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>
>
>----------------end table -----------------
>
>
>
>
>"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
>news:uqh0UuJeDHA.2304@TK2MSFTNGP11.phx.gbl...

>> > Does field size = 3000 means can hold 3000 characters?

>>
>> Yes. However, if your INSERT statement also inserts into your other
>> columns, and you go over 8060 bytes total (hint: it is not solely because

>of

>> *this* column!), you'll get the error.
>>

>> > Finally, I change the field type to TEXT and resolved the problem.
>> >
>> > Am I doing correct?

>>
>> In my opinion, no. Storing a maximum of 3,000 bytes in a TEXT column is
>> grossly inefficient. Once again, can you show your CREATE TABLE

>statement?

>> There are likely other ways you can get below the 8,060 byte threshold.
>>
>>

>

[Dina Womack]

Please Help!!!
I am trying to populate a dropdownlist from information in two other
dropdropdownlists on my web page. Everything else works, but I keep
getting this error -
Incorrect syntax near the keyword 'INNER'.

here is the code I am using in the selected index changed event of the
2nd dropdownlist

<code>
Private Sub ddlModificationType_SelectedIndexChanged(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
ddlModificationType.SelectedIndexChanged


Dim SQLserver As String = "server =
localhost;uid=sa;password=;database=master"
Dim conn As SqlConnection
Dim com As SqlCommand
Dim sql As String

sql = "select
tblStudentModificationList.ModID,ModificationList. ModDesc" & _
"FROM (ModificationList)INNER JOIN tblStudentModification ON" &
_
"ModificationList.ModID = tblStudentModification.ModID" & _
"WHERE tblStudentModification.ModID =" &
(ddlStudentName.SelectedIndex) & _
"AND ModificationList.ModType =" &
(ddlModificationType.SelectedIndex) & _
"ORDER BY tblStudentModification.ModID"


conn = New SqlConnection(SQLserver)
com = New SqlCommand(sql, conn)

Dim ds As New DataSet
Dim mydataadapter As New SqlDataAdapter(sql, conn)

mydataadapter.Fill(ds)

ddlModification.DataSource = ds
ddlModification.DataBind()

End Sub

Thanks in advance for any help!!!






*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ken Schaefer]

I think you need a space:

FROM (ModificationList)INNER JOIN

There is no space between the ")" character and the word INNER. You will
also need a space between ModDesc and the word FROM.

I suggest you Response.Write(sql) to see what your SQL statement actually
looks like.

Cheers
Ken

"Dina Womack" <dina_womack@worldnet.att.net> wrote in message
news:uJ7jFAscEHA.3616@TK2MSFTNGP10.phx.gbl...

>
> Please Help!!!
> I am trying to populate a dropdownlist from information in two other
> dropdropdownlists on my web page. Everything else works, but I keep
> getting this error -
> Incorrect syntax near the keyword 'INNER'.
>
> here is the code I am using in the selected index changed event of the
> 2nd dropdownlist
>
> <code>
> Private Sub ddlModificationType_SelectedIndexChanged(ByVal sender As
> Object, ByVal e As System.EventArgs) Handles
> ddlModificationType.SelectedIndexChanged
>
>
> Dim SQLserver As String = "server =
> localhost;uid=sa;password=;database=master"
> Dim conn As SqlConnection
> Dim com As SqlCommand
> Dim sql As String
>
> sql = "select
> tblStudentModificationList.ModID,ModificationList. ModDesc" & _
> "FROM (ModificationList)INNER JOIN tblStudentModification ON" &
> _
> "ModificationList.ModID = tblStudentModification.ModID" & _
> "WHERE tblStudentModification.ModID =" &
> (ddlStudentName.SelectedIndex) & _
> "AND ModificationList.ModType =" &
> (ddlModificationType.SelectedIndex) & _
> "ORDER BY tblStudentModification.ModID"
>
>
> conn = New SqlConnection(SQLserver)
> com = New SqlCommand(sql, conn)
>
> Dim ds As New DataSet
> Dim mydataadapter As New SqlDataAdapter(sql, conn)
>
> mydataadapter.Fill(ds)
>
> ddlModification.DataSource = ds
> ddlModification.DataBind()
>
> End Sub
>
> Thanks in advance for any help!!!
>
>
>
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Dina Womack]

Ken,

Thanks for the direction - you were right on the nose. The SQL now does
not generate any errors, however there is still nothing in the ddl even
though this query generates results in the query analyzer. I also didn't
know about the Response.write tool (Very new to asp!!!!).

Thanks again for the help - Now back to the grindstone...LOL.

Dina



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron [SQL Server MVP]]

For further help, you may wish to see a .NET group.
[url]http://www.aspfaq.com/5002[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Dina Womack" <dina_womack@worldnet.att.net> wrote in message
news:uJ7jFAscEHA.3616@TK2MSFTNGP10.phx.gbl...

>
> Please Help!!!
> I am trying to populate a dropdownlist from information in two other
> dropdropdownlists on my web page. Everything else works, but I keep
> getting this error -
> Incorrect syntax near the keyword 'INNER'.
>
> here is the code I am using in the selected index changed event of the
> 2nd dropdownlist
>
> <code>
> Private Sub ddlModificationType_SelectedIndexChanged(ByVal sender As
> Object, ByVal e As System.EventArgs) Handles
> ddlModificationType.SelectedIndexChanged
>
>
> Dim SQLserver As String = "server =
> localhost;uid=sa;password=;database=master"
> Dim conn As SqlConnection
> Dim com As SqlCommand
> Dim sql As String
>
> sql = "select
> tblStudentModificationList.ModID,ModificationList. ModDesc" & _
> "FROM (ModificationList)INNER JOIN tblStudentModification ON" &
> _
> "ModificationList.ModID = tblStudentModification.ModID" & _
> "WHERE tblStudentModification.ModID =" &
> (ddlStudentName.SelectedIndex) & _
> "AND ModificationList.ModType =" &
> (ddlModificationType.SelectedIndex) & _
> "ORDER BY tblStudentModification.ModID"
>
>
> conn = New SqlConnection(SQLserver)
> com = New SqlCommand(sql, conn)
>
> Dim ds As New DataSet
> Dim mydataadapter As New SqlDataAdapter(sql, conn)
>
> mydataadapter.Fill(ds)
>
> ddlModification.DataSource = ds
> ddlModification.DataBind()
>
> End Sub
>
> Thanks in advance for any help!!!
>
>
>
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[natg504]

I'm trying to create a dynamic where clause, but I'd like it to be in a single
string. But I am getting the error below.
======================================
<cfset txtWHERE = "WHERE 0=0 and Dept='ACCT' ">

<cfquery name="rstResults" datasource="DLCourses">
SELECT #FORM.txtQSelect#
FROM courses
#txtWHERE#;
</cfquery>
======================================
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '0=0 AND Dept = ''ACCT'''.

[jdeline]

Use PreserveSingleQuotes function:

#PreserveSingleQuotes(txtWHERE)#

Also, you don't need that trailing ;

[natg504]

Thanks!

[Mike Greider]

Just a friendly tip that you might want to review the statement to make sure
that you aren't opening yourself up to a SQL injection attack. Since you are
passing a string variable to be evaluated as SQL, you'll make to make extra
certain that the variable doesn't contain anything malicious.