CODEWITHSUNDEEP
sqlsql-server
Asad Nadeem
Asad Nadeem

Reputation: 549

The identifier that starts with ...... is too long. Maximum length is 128

I am shifting my database to MS SQL Server 2008 R2, When I am trying to save long length string then it is giving me the error for Maximum length, while i have set the datatype of that column as "text" after it i replaced it with "varchar(max)" but no solution there.

kindly provide me a solution how to resolve this issue. I am executing the following query:

update hotel 
set hotel_policy = 
    "Overview of Park Central New York - New York
    This hotel is making improvements.
        The property is undergoing renovations. The following areas are affected:
        Bar/lounge
        Business center
        Select guestrooms

    Every effort will be made to minimize noise and disturbance.
    Occupying a Beaux Arts building dating to 1927, Park Central New York Hotel is within a block of famed concert venue Carnegie Hall and within a 5-minute walk of Manhattan’s world-renowned Broadway theater district. Prefer the great outdoors to the Great White Way? Central Park is just 3 blocks from the hotel. There, you can rent a rowboat at the lake, play a game of tennis, or visit the Central Park Zoo. The international boutiques and flagship department stores of Fifth Avenue start within a 10-minute walk of the hotel. For travel to sights farther afield, there are 7 subway lines located within 3 blocks of the Park Central.
    The hotel has a snack bar for guests' convenience, and coffee and tea in the lobby.
    Retreat to your guestroom and sink into a bed with a pillowtop mattress and down comforter and pillows. Need to check email or finish up some work? You’ll find a desk with an ergonomic chair and wireless high-speed Internet access (surcharge). Unwind with a video game (surcharge) on the flat-panel HDTV."

where hotel_id = 1

I search it a lot but the solutions i found are not useful to me.

Thanks!

Upvotes: 30

Views: 107714

Answers (4)

Sajan Alex
Sajan Alex

Reputation: 37

Keep the text in single quotes and escape quotes inside with two single quotes.

Upvotes: 1

Prasanjeet
Prasanjeet

Reputation: 9

 [ UPDATE denomination SET currencyType ='COINS', denomination ='1.00', currencyValue='3CAD', Active ='N', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='1.00' , UPDATE denomination SET currencyType ='COINS', denomination ='1.00', currencyValue='2CAD', Active ='N', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='1.00' , UPDATE denomination SET currencyType ='COINS', denomination ='1.00', currencyValue='2', Active ='Y', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='1.00' , UPDATE denomination SET currencyType ='NOTES', denomination ='7.00', currencyValue='8', Active ='N', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='7.00' , UPDATE denomination SET currencyType ='NOTES', denomination ='10.00', currencyValue='TENCAD', Active ='Y', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='10.00' , UPDATE denomination SET currencyType ='NOTES', denomination ='20.00', currencyValue='TWENTYCAD', Active ='N', UPDATEDBY ='EBIX_ADMIN', UPDATEDDATE =GETDATE() WHERE groupId ='EXC' AND COUNTRYCODE ='CA' AND currencyCode ='CAD' AND denomination ='20.00' ]

Upvotes: -2

Toren
Toren

Reputation: 6856

If you don't want to change double quotes to single quotes add following two lines in the begging of the script 

 SET QUOTED_IDENTIFIER OFF
 SET ANSI_NULLS ON

Upvotes: 16

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

According to ANSI SQL standard, double quotes are used (if necessary) for object identifiers (ex. UPDATE "hotel" ...) and not as string delimiters ("Overview of Park Central ..."). SQL Server has this behavior when QUOTED_IDENTIFIER is ON.

Edit 1: The usage of single and double quotation marks as delimiters for object identifiers (including column aliases) is described below:

                        Delimiter   Delimiter
                        for         for
SET QUOTED_IDENTIFIER   Object ID   Alias ID        StringDelimiter
ON                      " or []     " or ' or []    '
OFF                     []          " or ' or []    " or '
  • ON then double quotes can be used as delimiter for object identifiers (including column aliases) and single quotes are used as delimiters for string literals and/or for column aliases (SELECT Column1 AS 'Alias1' ....)identifiers.
  • OFF then double quotes can be used as delimiter for columns aliases (SELECT Column1 AS "Alias1" ...) and as delimiter for string literals (SELECT "String1" AS Alias1 ...). Single quotation marks can be used as string delimiter and as delimiter for column aliases (SELECT Column1 ASAlias1...).

Use instead single quotes:

update hotel 
set hotel_policy = 'Overview of Park Central ...'
where hotel_id = 1

Upvotes: 44

Related Questions