MS SQL does not allow VARCHAR over 128 characters, why?

Question

I have a table with a column configured to hold nvarchar data type. I am trying to add a row using

INSERT INTO TABLE_NAME VALUES (value1, value2...)

Sql-server gets stuck on a 180 character string that I am trying to assign to the nvarchar data type column returning:

Error: The identifier that starts with [part of string] is too long. Maximum length is 128.

I don't understand why this is happening since nvarchar(max) should hold 2GByte of storage as I read here: What is the maximum characters for the NVARCHAR(MAX)?

Any ideas of what I've got wrong here?

UPDATE:

The table was created with this:

CREATE TABLE MED_DATA (
    MED_DATA_ID INT
    ,ORDER_ID INT
    ,GUID NVARCHAR
    ,INPUT_TXT NVARCHAR
    ,STATUS_CDE CHAR
    ,CRTE_DTM DATETIME
    ,MOD_AT_DTM DATETIME
    ,CHG_IN_REC_IND CHAR
    ,PRIMARY KEY (MED_DATA_ID)
    )

And my actual INSERT statement is as follows:

INSERT INTO MED_DATA
VALUES (
    5
    ,12
    ,"8fd9924"
    ,"{'firstName':'Foo','lastName':'Bar','guid':'8fd9924','weightChanged':false,'gender':'Male','heightFeet':9,'heightInches':9,'weightPounds':999}"
    ,"PENDING"
    ,"2017-09-02 00:00:00.000"
    ,"2017-09-02 00:00:00.000"
    ,NULL
    )

Show us the **real** sql you are using. "Identifier too long" suggests that you are trying a column **name** (not a value) that is too long — , Jul 23 '18 at 06:07

score 5 · Accepted Answer · answered Jul 23 '18 at 06:39

By default, double quotes in T-SQL do not delimit a string. They delimit an identifier. So you cannot use double quotes here. You could change the default but shouldn't.

If this is being directly written in a query window, use single quotes for strings and then double up quotes within the string to escape them:

INSERT INTO MED_DATA VALUES (5, 12, '8fd9924', '{''firstName'':''Foo'',''lastName'':''Bar'',''guid'':''8fd9924'',''weightChanged'':false,''gender'':''Male'',''heightFeet'':9,''heightInches'':9,''weightPounds'':999}', 'PENDING', '2017-09-02T00:00:00.000', '2017-09-02T00:00:00.000', NULL)

But if, instead, you're passing this string across from another program, it's time to learn how to use parameterized queries. That'll also allow you to pass the dates across as dates and not rely on string parsing to reconstruct them correctly.

Also, as noted, you need to fix your table definitions because they've currently nvarchar which means the same as nvarchar(1).

score 1 · Answer 2 · answered Jul 23 '18 at 06:10

1

Are you aware of what an Identifier is? Here is a hint - it is a NAME. SQL Server is not complaining about your data, it is complaining about a field or table name. SOmehow your SQL must be totally borked so that part of the text is parsed as name of a field or table. And yes, those are limited to 128 characters.

This is clear in the error message:

Error: The identifier

clearly states it is an identifier issue.

answered Jul 23 '18 at 06:10

TomTom

61,059
10
88
148

Borked? Running with the [default settings](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017)? – Damien_The_Unbeliever Jul 23 '18 at 06:32

MS SQL does not allow VARCHAR over 128 characters, why?

2 Answers2