Weird SQL Truncate error

[JJ]

This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've got it
wrong with ntext?? The table new records from the page without any errors,
its just when I type directly into the columns....?



[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

JJ

 

[MyndPhlyp]

This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've got it
wrong with ntext?? The table new records from the page without any errors,
its just when I type directly into the columns....?



[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working with
ODBC. I don't know if it is applicable to your situation. The solution was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com

 

[JJ]

Hi.

Thanks for that - however as I am typing directly into the tables I don't
think ODBC comes into play.

JJ

This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just
change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've got it
wrong with ntext?? The table new records from the page without any
errors,
its just when I type directly into the columns....?



[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working with
ODBC. I don't know if it is applicable to your situation. The solution was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com

 

[Guest]

Hi.

Thanks for that - however as I am typing directly into the tables I don't
think ODBC comes into play.

JJ

This is slightly OT but ....
When testing why my aspx page isn't returning the data as expected I decided
to type directly into one of my SQL tables.
(The column definitions are below).
When I type in data into any of the columns I get this error:
"string or binary data will be truncated"
I've tried clearing out all the columns but no luck. Even if I just
change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've got it
wrong with ntext?? The table new records from the page without any
errors,
its just when I type directly into the columns....?
[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working with
ODBC. I don't know if it is applicable to your situation. The solution was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com- Hide quoted text -

- Show quoted text -

The maximum number of bytes per row is 8060. When you add a row in
length exceeds 8060 bytes you will get an error. If I look into your
table, I see that Abstract is [nvarchar](4000), that means this column
used 4000x2 bytes - almost the 100% of capacity

Reduced the size of your fields and it will help. Otherwise, add a
child table and move large field there.

 

[Guest]

The maximum number of bytes per row is 8060. When you add a row in
length exceeds 8060 bytes you will get an error. If I look into your
table, I see that Abstract is [nvarchar](4000), that means this column
used 4000x2 bytes - almost the 100% of capacity

Reduced the size of your fields and it will help. Otherwise, add a
child table and move large field there.- Hide quoted text -

- Show quoted text -

Changing nvarchar to varchar can help too. (N) means unicode support,
that needs 2 bytes per character. This is a reason why the size is x2
in bytes.

 

[JJ]

If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ

Hi.

Thanks for that - however as I am typing directly into the tables I don't
think ODBC comes into play.

JJ

This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I
decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just
change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've
got
it
wrong with ntext?? The table new records from the page without any
errors,
its just when I type directly into the columns....?

[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working
with
ODBC. I don't know if it is applicable to your situation. The solution
was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com-
Hide quoted text -

- Show quoted text -

The maximum number of bytes per row is 8060. When you add a row in
length exceeds 8060 bytes you will get an error. If I look into your
table, I see that Abstract is [nvarchar](4000), that means this column
used 4000x2 bytes - almost the 100% of capacity

Reduced the size of your fields and it will help. Otherwise, add a
child table and move large field there.

 

[JJ]

When I changed the row 'HtmlBody' to nvarchar(MAX) the errors went away.
It seems its the ntext row (when there's lots of text in it) thats causing
the error.

Will nvarchar(MAX) be ok for lots of text? How does it compare to ntext?

JJ

If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ

Hi.

Thanks for that - however as I am typing directly into the tables I
don't
think ODBC comes into play.

JJ







This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I
decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just
change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless I've
got
it
wrong with ntext?? The table new records from the page without any
errors,
its just when I type directly into the columns....?

[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working
with
ODBC. I don't know if it is applicable to your situation. The solution
was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com-
Hide quoted text -

- Show quoted text -

The maximum number of bytes per row is 8060. When you add a row in
length exceeds 8060 bytes you will get an error. If I look into your
table, I see that Abstract is [nvarchar](4000), that means this column
used 4000x2 bytes - almost the 100% of capacity

Reduced the size of your fields and it will help. Otherwise, add a
child table and move large field there.

 

[JJ]

Interestingly, in the depths of the SQL help docs, I noticed this:

Important:
ntext, text, and image data types will be removed in a future version of
Microsoft SQL Server. Avoid using these data types in new development work,
and plan to modify applications that currently use them. Use nvarchar(max),
varchar(max), and varbinary(max) instead. For more information, see Using
Large-Value Data Types.


So nvarchar(MAX) should cope with a large amount of html text, as did ntext,
.....shouldn't it...?
JJ

When I changed the row 'HtmlBody' to nvarchar(MAX) the errors went away.
It seems its the ntext row (when there's lots of text in it) thats causing
the error.

Will nvarchar(MAX) be ok for lots of text? How does it compare to ntext?

JJ

If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ

Hi.

Thanks for that - however as I am typing directly into the tables I
don't
think ODBC comes into play.

JJ







This is slightly OT but ....

When testing why my aspx page isn't returning the data as expected I
decided
to type directly into one of my SQL tables.
(The column definitions are below).

When I type in data into any of the columns I get this error:
"string or binary data will be truncated"

I've tried clearing out all the columns but no luck. Even if I just
change
on of the int's to a different number I get the same error.
I believe all are under the limits for the column types - unless
I've got
it
wrong with ntext?? The table new records from the page without any
errors,
its just when I type directly into the columns....?

[ID] [int] IDENTITY(1,1) NOT NULL,
[AddedDate] [datetime] NOT NULL,
[AddedBy] [nvarchar](256) NOT NULL,
[IssueNumber] [int] NOT NULL,
[Subject] [nvarchar](256) NOT NULL,
[Abstract] [nvarchar](4000) NULL,
[HtmlBody] [ntext] NOT NULL,
[PlainTextBody] [ntext] NOT NULL,
[StatusID] [int] NOT NULL ,
[LastSent] [datetime] NOT NULL,
[RecordClicks] [bit] NOT NULL,
[Listed] [bit] NOT NULL ,
[Approved] [bit] NOT NULL ,
[OnlyForMembers] [bit] NOT NULL,
[ViewCount] [int] ,

I seem to recall a limitation in retrieving large field sizes working
with
ODBC. I don't know if it is applicable to your situation. The
solution was
to retrieve the large columns in chunks. This link will get you some
information.

http://www.google.com/search?hl=en&q=blob+getchunk+site:microsoft.com-
Hide quoted text -

- Show quoted text -

The maximum number of bytes per row is 8060. When you add a row in
length exceeds 8060 bytes you will get an error. If I look into your
table, I see that Abstract is [nvarchar](4000), that means this column
used 4000x2 bytes - almost the 100% of capacity

Reduced the size of your fields and it will help. Otherwise, add a
child table and move large field there.

 

[Guest]

When I changed the row 'HtmlBody' to nvarchar(MAX) the errors went away.
It seems its the ntext row (when there's lots of text in it) thats causing
the error.

Will nvarchar(MAX) be ok for lots of text? How does it compare to ntext?

Ah, you are about 2005....

nvarchar(MAX) is a replacement for old ntext and you should use this
new type in SQL Server 2005. It's simply handy - ntext data type
cannot be a variable in a procedure, you can concatenate or use a
substring with it. With nvarchar(MAX) you don't have these problems.