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.