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.
>

Can I populate an existing table from inline function table data?

I need to populate an existing table with the same data contained in/returne
d
from an inline function table. An external app uses the data from the inlin
e
function table in a form. I need to be able to update that data also in a
form, so I need to populate an existing table with the same data. Yes, I
could write an sp and some code, but I am just checking if there is a way to
grab the data from the inline function table to place in the existing table.
I tried Insert Into in the function - which it said is not allowed.
My issue is that the function takes a lot of arguments (10 args). From the
form, all I have to do is say
Frm.Recorsource = "Select * from dbo.f_data(" & strArgs & ")"
It would take way more code to do the same thing with an SP and 10 Params.
Is there a way to grab the inline function table data?
Thanks,
RichAre you saying that you cannot use an inline function as the source for a SE
LECT which is the source
for an INSERT? This is not what I am seeing:
USE tempdb
GO
CREATE TABLE a(c1 int, c2 int)
GO
CREATE FUNCTION f()
RETURNS TABLE
AS
RETURN(
SELECT 1 AS c1, 11 AS c2
UNION ALL
SELECT 2 AS c1, 22 AS c2
)
GO
INSERT INTO a(c1, c2)
SELECT c1, c2 FROM f()
GO
SELECT * FROM a
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:088A39C0-59BB-4037-81F8-F777F7C8FF90@.microsoft.com...
>I need to populate an existing table with the same data contained in/return
ed
> from an inline function table. An external app uses the data from the inl
ine
> function table in a form. I need to be able to update that data also in a
> form, so I need to populate an existing table with the same data. Yes, I
> could write an sp and some code, but I am just checking if there is a way
to
> grab the data from the inline function table to place in the existing tabl
e.
> I tried Insert Into in the function - which it said is not allowed.
> My issue is that the function takes a lot of arguments (10 args). From th
e
> form, all I have to do is say
> Frm.Recorsource = "Select * from dbo.f_data(" & strArgs & ")"
> It would take way more code to do the same thing with an SP and 10 Params.
> Is there a way to grab the inline function table data?
> Thanks,
> Rich