By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,681 Members | 1,775 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,681 IT Pros & Developers. It's quick & easy.

Weird SQL Truncate error

P: n/a
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
May 31 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"JJ" <ab*@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
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%3Amicrosoft.com

May 31 '07 #2

P: n/a
JJ
Hi.

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

JJ

"MyndPhlyp" <no****@homeright.nowwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>
"JJ" <ab*@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
>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%3Amicrosoft.com

May 31 '07 #3

P: n/a
On May 31, 2:03 pm, "JJ" <a...@xyz.comwrote:
Hi.

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

JJ

"MyndPhlyp" <nob...@homeright.nowwrote in message

news:%2****************@TK2MSFTNGP03.phx.gbl...


"JJ" <a...@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
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%3Amicrosoft.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.

May 31 '07 #4

P: n/a
On May 31, 2:27 pm, Alexey Smirnov <alexey.smir...@gmail.comwrote:
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.
May 31 '07 #5

P: n/a
JJ
If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@q69g2000hsb.googlegr oups.com...
On May 31, 2:03 pm, "JJ" <a...@xyz.comwrote:
>Hi.

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

JJ

"MyndPhlyp" <nob...@homeright.nowwrote in message

news:%2****************@TK2MSFTNGP03.phx.gbl...


"JJ" <a...@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
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%3Amicrosoft.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.

May 31 '07 #6

P: n/a
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

"JJ" <ab*@xyz.comwrote in message
news:uf**************@TK2MSFTNGP04.phx.gbl...
If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@q69g2000hsb.googlegr oups.com...
>On May 31, 2:03 pm, "JJ" <a...@xyz.comwrote:
>>Hi.

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

JJ

"MyndPhlyp" <nob...@homeright.nowwrote in message

news:%2****************@TK2MSFTNGP03.phx.gbl.. .

"JJ" <a...@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
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%3Amicrosoft.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.


May 31 '07 #7

P: n/a
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
"JJ" <ab*@xyz.comwrote in message
news:ur**************@TK2MSFTNGP02.phx.gbl...
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

"JJ" <ab*@xyz.comwrote in message
news:uf**************@TK2MSFTNGP04.phx.gbl...
>If I made the Abstract column 'ntext' would that solve my problem?
Thanks,

JJ
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@q69g2000hsb.googleg roups.com...
>>On May 31, 2:03 pm, "JJ" <a...@xyz.comwrote:
Hi.

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

JJ

"MyndPhlyp" <nob...@homeright.nowwrote in message

news:%2****************@TK2MSFTNGP03.phx.gbl. ..

"JJ" <a...@xyz.comwrote in message
news:eS**************@TK2MSFTNGP02.phx.gbl...
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%3Amicrosoft.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.



May 31 '07 #8

P: n/a
On May 31, 3:00 pm, "JJ" <a...@xyz.comwrote:
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.

May 31 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.