is too long. Maximum length is 128. while using double quotes

the identifier that starts with is too long. maximum length is 128
pyodbc the identifier that starts with ... is too long
invalid length for connection option 'data source', maximum length is 128

I have a SP in MSSQL that will insert and error log to the SQL

ALTER PROCEDURE [dbo].[TestLog]


@PrimaryName nvarchar(255),
@ERROR nvarchar(4000)

AS
BEGIN


insert into Process_Log 
(
PrimaryName ,
ERROR
) VALUES
(
@PrimaryName ,
@ERROR
)

END

My sample error message is this:

Violation of PRIMARY KEY constraint 'PK__AP__2EC21549E681BC94'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (215009).
The statement has been terminated.

as I have the ' in string I have to use double quotes and SET QUOTED_IDENTIFIER OFF.

I am unable to change the error message or edit it as it is from other application.

EDIT:

My Process table structure:

CREATE TABLE [dbo].[Process_Log](
    [PrimaryName] [nvarchar](255) NULL,
    [ERROR] [nvarchar](max) NULL
) 

In your query replace:

insert into Process_Log 
(
PrimaryName ,
ERROR
) VALUES
(
@PrimaryName ,
@ERROR
)

with:

insert into Process_Log 
(
PrimaryName ,
ERROR
) VALUES
(
@PrimaryName ,
REPLACE(@ERROR, '''', '''''')
)

This is NOT changing the error message, just "escaping" the single quotes so that SQL Server won't get confused.

You will also need to get rid of the QUOTED_IDENTIFIER bit and forget about double quotes.


I'm starting to think that this isn't SQL Server, as that "stuff" you added in your comment below certainly isn't SQL as I know it... and it doesn't do anything if I try running it through SSMS.

Try this:

DECLARE @test TABLE (error VARCHAR(MAX));
--Doesn't work due to single quotes
--INSERT INTO @test SELECT 'Violation of PRIMARY KEY constraint 'PK__AP__2EC21549E681BC94'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (215009). The statement has been terminated.';
INSERT INTO @test SELECT 'Violation of PRIMARY KEY constraint ''PK__AP__2EC21549E681BC94''. Cannot insert duplicate key in object ''dbo.Test''. The duplicate key value is (215009). The statement has been terminated.';
SELECT * FROM @test;

is too long. Maximum length is 128. while using double quotes, In your query replace: insert into Process_Log ( PrimaryName , ERROR ) VALUES ( @PrimaryName , @ERROR ). with: insert into Process_Log ( PrimaryName  Maximum length is 128. Strings in SQL are delimited by 'single quotes', not "double quotes". MS SQL has a setting, QUOTED_IDENTIFIER, which when OFF will allow either, but ON is proper SQL syntax and an ODBC default anyway. MS SQL thinks that your string is an identifier, which cannot be longer that 128 characters.

If the column does not support strings with, then you will need to alter your TestLog table and increase the size of the column which was truncated. You can learn how to alter a table in MS SQL here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017

You will need to change your parameter from

@ERROR nvarchar(4000)

to

@ERROR nvarchar(MAX)

Also, you will need to take a look at how many characters your error message is and act accordingly.

And also, you will need to make sure your quotes are properly escaped.

Update Error in: Database, When executing a an update workflow in Quick Connect for Base Update Error in: Database - The Identifier Maximum Length is 128 The identifier that starts with '<queryContainer query="SELECT dbo.database.field" is too long. Double Quotes are used for object identifiers for example: "hotel", and  Your problem may be caused by apostrophe's. I noticed that you are using quotes around your string. So, I suggest you change your code like so

I have found a solution for that: I have to execute the SP with SET QUOTED_IDENTIFIER OFF before exec SP, I had SET QUOTED_IDENTIFIER OFF - in my SP - but it has to be executed every time I execute SP. (SP is executed in the software.)

Example:

SET QUOTED_IDENTIFIER OFF
Exec [TestLog]
@PrimaryName = "test"
,@ERROR = "Violation of PRIMARY KEY constraint 'PK__AP__2EC21549E681BC94'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (215009).
The statement has been terminated."

Then I will avoid a a 128 len probmem.

The identifier that starts with.is too long. Maximum length is 128 , Update Error in: Database - The Identifier Maximum Length is 128 (126344) that starts with '<queryContainer query="SELECT dbo.database.field" is too long. Double Quotes are used for object identifiers for example: "hotel", and the SQL​  Maximum length is 128. while using double quotes I have to use double quotes and SET QUOTED_IDENTIFIER OFF. the len of the string is more than 128 SQL throws

is too long. Maximum length is 128. Error, BOL states the max identifier length as 128 characters. Remember, in Transact​-SQL, always use single quotes, never double quotes. You declare using NVARCHAR not VARCHAR throughout, but use non-Unicode literals which makes me doubt you are really looking to support Unicode (Unicode literals in T-SQL are where you add the N prefix). Nitpicky, but the traditional Windows max path length is 260 not 255; the inconsistent double slashing is also a bit odd.

Unclosed quotation mark after the character string + is too long , (long text) WHERE id=1 and I get this error The identifier that sta. thanks and If I want to use double quote, what should I change in the Update  The identifier that starts with 'Where Prospect.Create_Date_Time >= '1/1/1991' AND Prospect.Create_Date_Time <= '12/15/2001' AND Prospect.Mail_Country ='US' ORDE' is too long. Maximum length is 128. I have tried doing select * to shorten the length of code and it had no effect since this had no qoutes in it.

Single quotes in parameter to SP, Unclosed quotation mark after the character string + is too long. Maximum length is 128. hi all, I am using the following query to insert a log entry, bu. Don't use double quotes around strings, use single quotes. This is the  Unclosed quotation mark after the character string + is too long. Maximum length is 128. SQL Server - Unclosed quotation mark after the character string + is too long. Maximum length is 128.

Comments
  • So what's the issue here? Did you know that you can also replace ' with '' and this will work just as well as double quotes, etc.?
  • Yes I know, but as I said I am unable to edit the error message. It has to stay as it is. and when I use SET QUOTED_IDENTIFIER OFF and the len of the string is more than 128 SQL throws and error (len too long). I tried to do left(@ERROR, 128) but it is not working with values in double quotes.
  • your error message field would be well suited to varchar(max) - I often see error logs where messages/stack traces are stored as long fields, but key messages can end up having to be truncated. Some people had a 2 hour meeting about how long to make their error table, unaware of data-types text, varchar(max) etc.
  • My table has nvarchar(MAX) for Error field.
  • in your procedure it is limited to 4000 characters on the parameter though
  • I don't think it will work, as the syntax without "" will be wrong, right? (Incorrect syntax near PK__...)
  • Nope, you are incorrect, try it... you might be surprised! Basically it will replace ' with '', then SQL Server will in turn replace '' back to '. It's just telling the query engine that when you have ' to save it as part of the message rather than seeing it as the end of the message.
  • declare #test nvarchar(255) set #test 'Violation of PRIMARY KEY constraint 'PK__AP__2EC21549E681BC94'. Cannot insert duplicate key in object 'dbo.Test'. The duplicate key value is (215009). The statement has been terminated.' select replace(#test, '''', '''''') ERROR MESSAGE: Incorrect syntax near 'Violation of PRIMARY KEY constraint '. (Preplace # with @)
  • No sorry, it will not work. It will work with a string value like 'hello word' or even with "hello word" but if I will have "some random error with more than 128 char len" SQL will error.
  • @Lemon please show us the structure of your TestLog table.
  • CREATE TABLE [dbo].[Process_Log]( [PrimaryName] [nvarchar](255) NULL, [ERROR] [nvarchar](max) NULL )
  • @Lemon how long is primary name you intend to insert into the table?
  • less than 20 char for now? I don't think it will be ever longer than 255 - is is there just for test.