Can not insert input field with length gt 128

I got this problem that really puzzled me. I used a stored procedure to
insert records into SQL. When the field of input string contained more than
128 characters, it gave me this message:
The identifier that starts with 'Today is a nice day..........Good bye'
is too long. Maximum length is 128.
But here I am not sending in an identifier name, I am sending in the actual
input which is more than 128 characters long. The input field in the data
base is defined as varchar(1000).
Any ideas?What is the size on the parameter declaration in the stored procedure? I'll
bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:D4B955D4-C318-4E38-BB9F-77B439561881@.microsoft.com...
>I got this problem that really puzzled me. I used a stored procedure to
> insert records into SQL. When the field of input string contained more
> than
> 128 characters, it gave me this message:
> The identifier that starts with 'Today is a nice day..........Good bye'
> is too long. Maximum length is 128.
> But here I am not sending in an identifier name, I am sending in the
> actual
> input which is more than 128 characters long. The input field in the data
> base is defined as varchar(1000).
> Any ideas?|||> What is the size on the parameter declaration in the stored procedure?
> I'll bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
Actually, it sounds like the call he is making to the database doesn't
properly encapsulate this string. It seems SQL Server has mistaken the
string for an identifier (otherwise the error would be "string or binary
data would be truncated"). This usually happens when someone uses " instead
of ' for delimiting a string, but there are other possibilities as well.
"pelican", how about showing us the actual code you are using, instead of
vaguely describing it -- then we don't have to guess. I suppose DDL for the
table you are trying to INSERT into wouldn't hurt either (see
http://www.aspfaq.com/5006).|||Good point, probably an embedded ' in the input somewhere that wasn't
properly escaped by the app. Interesting that it would pick 128 as the
cut-off size, however. I would definitely need to see the code for the SP
and the input string.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:u6KSE6AUFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses "
> instead of ' for delimiting a string, but there are other possibilities as
> well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for
> the table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>|||> Good point, probably an embedded ' in the input somewhere that wasn't
> properly escaped by the app. Interesting that it would pick 128 as the
> cut-off size, however.
That is the maximum size for an identifier. I don't think it is an errant
', it just received this string that it thinks is supposed to be a column,
and is saying, WHOA, this column name is bigger than 128, hold on. It could
be 129 or 8000 characters... the same error will get kicked out.|||Thank you all so much! "AB - MVP" is right, once I changed the double quote
(") to single ('), it worked fine! In the stored procedure, the size on the
parameter is varchar(1000). I did not realize that the "" sign will make SQ
L
believe I was passing an identifier instead of the actually content.
You are my life saver...
"AB - MVP" wrote:

> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses " inste
ad
> of ' for delimiting a string, but there are other possibilities as well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for th
e
> table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>
>|||I read about the identifier length in the help section. It said the same
thing as AB-MVP pointed out, that 128 is the maximum length per row for an
identifier. I just never dreamed that a " will do it.
Thanks a lot!|||Yeah it's called a "quoted identifier" and can be circumvented by using
"parameterized queries".
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:6F3F8CAB-1EBD-45A6-BF74-4DC272EA1804@.microsoft.com...
> Thank you all so much! "AB - MVP" is right, once I changed the double
> quote
> (") to single ('), it worked fine! In the stored procedure, the size on
> the
> parameter is varchar(1000). I did not realize that the "" sign will make
> SQL
> believe I was passing an identifier instead of the actually content.
> You are my life saver...
> "AB - MVP" wrote:
>|||Yep that's what they get for slapping a SQL statement together with +'s
instead of using parameterized queries.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGWbvCBUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> That is the maximum size for an identifier. I don't think it is an errant
> ', it just received this string that it thinks is supposed to be a column,
> and is saying, WHOA, this column name is bigger than 128, hold on. It
> could be 129 or 8000 characters... the same error will get kicked out.
>