CODEWITHSUNDEEP
sql-serverstored-proceduressql-insert
Lemon
Lemon

Reputation: 141

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

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
)

Upvotes: 1

Views: 8673

Answers (3)

Lemon
Lemon

Reputation: 141

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.

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

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;

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76464

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://learn.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.

Upvotes: 0

Related Questions