CODEWITHSUNDEEP
sqlsql-server
cjmaria
cjmaria

Reputation: 340

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

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
    )

Upvotes: 1

Views: 2678

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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

Upvotes: 5

TomTom
TomTom

Reputation: 62093

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.

Upvotes: 1

Related Questions