A problem with the bug that 2byte characters are to stored in msSql server

Started by hmjvaline, August 28, 2019, 03:13:08 PM

Previous topic - Next topic

hmjvaline

I use the latest windows X64 version of netxms 2.2.17 with the Microsoft sql server database.
I found that when using a string mixed with one-byte word and two-byte word to store the database,
the calculation length error will occur, causing the string to be Truncated.
I suspect that it may be that ms sql calculates the character length for varchar in a different way than other databases.
Nvarchar will treat both one-byte word and the two-byte word as one character, but the varchar data type will treat the ASCII word of one-byte  as a character, but the two-character word will be treated as two characters.

Is there any possibility to solve this problem?

Attached below is the SQL query I fetched using SQL PROFILER, select @p1 actully is stored = 'Sw-209-dlink總務' not 'Sw-209-dlink總務處',because the p1 is varchar(16)

declare @p1 int
set @p1=6981
exec sp_prepexec @p1 output,N'@P1 varchar(16),@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 varchar(9),@P11 int,@P12 varchar(9),@P13 varchar(1),@P14 int,@P15 int,@P16 varchar(9),@P17 varchar(9),@P18 int,@P19 int,@P20 varchar(37),@P21 varchar(37),@P22 int,@P23 varchar(1),@P24 varchar(1),@P25 varchar(1),@P26 varchar(1),@P27 bigint,@P28 int,@P29 int',N'UPDATE object_properties SET name=@P1,status=@P2,is_deleted=@P3,inherit_access_rights=@P4,last_modified=@P5,status_calc_alg=@P6,status_prop_alg=@P7,status_fixed_val=@P8,status_shift=@P9,status_translation=@P10,status_single_threshold=@P11,status_thresholds=@P12,comments=@P13,is_system=@P14,location_type=@P15,latitude=@P16,longitude=@P17,location_accuracy=@P18,location_timestamp=@P19,guid=@P20,image=@P21,submap_id=@P22,country=@P23,city=@P24,street_address=@P25,postcode=@P26,maint_event_id=@P27,state_before_maint=@P28 WHERE object_id=@P29','Sw-209-dlink總務處',2,0,1,1566991628,0,0,1,0,'01020304',75,'503C2814','',0,0,'0.000000','0.000000',0,0,'d025b358-fd3d-1d4d-9b5e-6337e2fd9fc1','00000000-0000-0000-0000-000000000000',0,'','','','',0,0,713
select @p1