Nvarchar column getting truncated to 256 characters

Question

I have a SQL Server Express 2012 database. There is a table which previously had a column of type nvarchar(255). Now my requirements have changed and I am required to store long strings in the same column, so I modified the columns to have size 512.

alter Table [dbo].[mytable] ALTER COLUMN [mycol] nvarchar(512) NULL;

Now when I try updating data in the column whose size was increased, the update goes through fine. But when I query the data, the column in question only returns 256 chars of data.

I have tried updates from application (jdbc) and manual update SQL queries in SSMS. But in both cases the results are the same. The data some how seems to be truncated at 256 chars.

update [dbo].[mytable] 
set mycol1 = 'Lot+hrJBVaD2hX1PjF34M2K8nLxyC3csgjZEnXOgzhyQ4iVNU8DYsPIePaMuHB4nC4cJghRSvbwQGw4me3mpynGxWeTIiCaxMFMJofF7u+yhEpa5S4hmeKaADCMHv/Mr+J/Ao3wbtJPO5VQcp6q7nUkQbVUTTAZjJ0GKLq3trpTxqNT52WrXFP+ZL1rN6Ho8rMMO74BvGxzojHo7ly9aO9iqfoi+LxJKvHK54QKpQLc/2AT5bjdUnupafURzmJvCII4DvL8R7ROSu3PbXDEEN/BmBZSY7Gune64ADh0q6R4RmLMBJFxviFMxxcjIgkKngma/uSVzYTdNLQJqD9nW2w==', 
mycol2 ='a0rfSnMgCSun8J6+LmElAY4Kx1ZmCEuCkCHeVW7wi6Jo7apAZ/qiGj0kRD+ZMlK5r4icgoBufv7Lzs1zlWZkoMMdWuula5keNo8X4TH3Y8cyEL3Ujio/Co6UVL7xngdXYY6woFxPQQtW0FB+/miGlY6KrByRV9vMFsPTGc77+dc0wgFQxE3CsPU97nRVe8m/LsT97qoolSvtyB0xHtDeQ70q2VRNB2oAyeVpsqoVN7/MQb7xW9glqYk0w/uHhZ6kx5QkSEbn1Ssq77KN/96yQluzcDm9E9eiMBdDMXFsu3FjqPy5BSyyvLi13NKaJcpaGo+do8jz0Rl6iXuorfSNyw==' 
where ... conditions;

select max(len(mycol1)), max(len(mycol2)) from [dbo].[mytable];

...returns 255. No errors were shown when running the queries.

I checked that elsewhere in the same database I have longer columns (nvarchar(998)) and there the updates go through fine - I can store longer values and get that data successfully.

I also tried rebooting the database just in case the alter did not go through properly but still data is truncated at the same size. I have already confirmed that the table shows the correct column size when I check the properties.

I have this database sitting on AWS RDS. Incidentally same column level alter works fine on the database on my local machine.

I do not have any views in the database and I have tried querying the exact records I update. This alter script works fine on my own local instance of SQL Server; the problems show up only on the SQL Server instance on my test env (AWS RDS).

I can't share the table definition due to its proprietary nature. I am not using a stored procedure. I tested by running a plain SQL update query using SSMS. The application has parameter set as String, and does not do any datatype truncation. It leaves that to the database.

Any ideas on what could be wrong appreciated.

Answer 1

My guess (which turned out to be correct) was that a trigger was modifying / truncating the value somehow.

I usually first check to make sure I am really observing the data correctly, since SSMS truncates display output by default (and it's different in results to text/grid). So checking the value, LEN, MAX(LEN()), and ensuring you are looking at the right column, the right table, and that your definition has been updated correctly, are useful things to check.

Once you've ruled that out, if you were updating from application, I would check to make sure the app doesn't have the wrong data type / size for its local variables or command parameters.

If you were calling a stored procedure, I would check the parameter, since silent truncation can occur here:

CREATE PROCEDURE dbo.foo @x nvarchar(255)
AS PRINT @x;
GO

DECLARE @string nvarchar(512) = REPLICATE(N'x', 254)
  + REPLICATE(N'y', 258);

PRINT @string;
EXEC dbo.foo @x = @string;

Results:

xxx...xxxyyy...
xxx...xxxy

The second value is obviously truncated to the parameter definition (but no warning or error is ever logged).

But if you are getting truncation from a direct update in SSMS, and your parameters / variables / constants look right, dig for a trigger. It's likely the 255 definition is still hard-coded there somewhere.

Answer 2

Maybe your application still has the datatype set to nvarchar(255), and is passing the parameter already truncated to the database.

Please share your table definition and the stored procedure that performs the update, to see if any parameter still has the 255 length hard-coded.

You can try the following code (make sure you change the table and column names to fit yours) to make sure the table definition and the inserts are working correctly:

exec sp_help 'Table_1' -- check table definition before test

declare @testparam nvarchar(512)

set @testparam = REPLICATE('X',512)

update [dbo].[Table_1] set [column_char] = @testparam
where column2 = <yourtestid>

select  len([column_char])
from [dbo].[Table_1] 
where column2 = <yourtestid>