Question:
"Is there some special option one needs to set on the publication to preserve the encoding in transfer?"
Answer:
There is an option on Publication side to check:
"Publication properties -> Articles -> Article properties -> Copy collation (True / False)".
Explanation: "Copy collation: Determines wheter to copy collation settings to the destination object."
Image:
Additional answer:
As an answer to your EDIT 2:
"EDIT2: Last week I made a test table using NVARCHAR instead, and that replicated successfully where utf-8 didn't"
here is an example query for demonstrating the use of UTF-8 collations for unicode characters in varchar columns.
It could better explain why the NVARCHAR column is successfully replicated in your case.
-- Example Query:
DECLARE @Data TABLE (
varchar_nonutf8 VARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC
, varchar_utf8 VARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC_UTF8
, nvarchar_nonutf8 NVARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC
, nvarchar_utf8 NVARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);
-- unicode characters exist in this string ("разуме" is Serbian for "understands")
DECLARE @Value NVARCHAR(MAX) = N'SQL2019 разуме UTF8'
INSERT INTO @Data (varchar_nonutf8, varchar_utf8, nvarchar_nonutf8, nvarchar_utf8)
VALUES (@Value, @Value, @Value, @Value)
SELECT * FROM @Data
Query result:
Additional answer 1:
You should set collation on distribution database, which allows accepting correct data (it can be _UTF8 one also).
Another thing to check is the CALL/SCALL usage on Publication side:
Find it in: "Publication properties -> Articles -> Article properties -> Statement Delivery".
More explanations can be found here (blogged by Bartosz Lewandowski)
Example Image:
Additional answer 2:
Use this script to browse replication commands and find the commands and check if the problematic strings exist.
(script taken from Bartosz Lewandowski here)
--BROWSE REPL COMMANDS
declare @publisher_db nvarchar(150)
declare @publisher_db_id int
declare @seqno nvarchar(500)
declare @seqno_bin varbinary(16)
declare @error_id int
declare @error_txt nvarchar(1000)
declare @command_id int
/*-----------------PARAMETERS---------------------*/
set @publisher_db='<Published db>'
set @seqno_bin=<LSN>
set @seqno='<LSN>'
set @command_id = <command id> -- Leave NULL if all you want to get ALL commands
/*-----------------------------------------------*/
create table #temp_commands (
xact_seqno varbinary(16) NULL,
originator_srvname varchar(100) NULL,
originator_db varchar(100) NULL,
article_id int NULL,
type int NULL,
partial_command bit NULL,
hashkey int NULL,
originator_publication_id int NULL,
originator_db_version int NULL,
originator_lsn varbinary (16) NULL,
command nvarchar (1024) NULL,
command_id int NULL)
select @publisher_db_id=id
from mspublisher_databases where publisher_db=@publisher_db
select @error_id=error_id
from msdistribution_history where xact_seqno=@seqno_bin
select top 1 @error_txt=error_text
from dbo.MSrepl_errors where id = @error_id
insert into #temp_commands
exec sp_browsereplcmds
@xact_seqno_start=@seqno,
@xact_seqno_end=@seqno,
@publisher_database_id=@publisher_db_id
if @command_id is NULL
BEGIN
select command,command_id, @error_txt as 'Error'
from #temp_commands order by command_id
END
ELSE
BEGIN
select command,command_id, @error_txt as 'Error'
from #temp_commands where command_id=@command_id
order by command_id
END
drop table #temp_commands
