Applies to: SQL Server 2019
This article provides workarounds for the issue that the ASCII function returns different results in the Publisher and Subscriber database tables.
Symptoms
Consider the following scenario:
-
You use transactional or merge replication in SQL Server 2019.
-
Initial schema and data are applied through the Replication Snapshot Agent.
-
In the Publisher database, a column that is defined as character data type includes a NULL value - ASCII character 0
char(0).
In this scenario, when you use the ASCII function to convert the column in the Publisher and Subscriber database tables, different results are returned. You can refer to the following sample:
-
Convert the column (
col1) in the Publisher database table:SELECT id, col1, ASCII(col1) FROM PublisherTable:::image type="content" source="media/ascii-function-returns-different-results-publisher-subscriber/publisher-result.png" alt-text="Screenshot of the results for the publisher table.":::
-
Convert the column (
col1) in the Subscriber database table:SELECT id, col1, ASCII(col1) FROM SubscriberTable:::image type="content" source="media/ascii-function-returns-different-results-publisher-subscriber/subscriber-result.png" alt-text="Screenshot of the results for the subscriber tables.":::
Workaround
-
To work around this issue for transactional replication, follow these steps:
-
Open SQL Server Management Studio and connect to the server acting as a Distributor.
-
Under Object Explorer, expand SQL Server Agent, and then expand Jobs.
-
Select the snapshot agent job for the affected publication, right-click it, and then select Properties > Steps > Step 2 > Edit.
-
In the Job Step Properties window, add
-NativeBcpFileFormatVersion 100at the end of the command and select OK to save the changes. -
Apply the latest Microsoft OLE DB driver:
-
If the distribution agent (or merge agent) runs for push subscriptions, apply it on the Distributor server.
-
If the distribution agent (or merge agent) runs for pull subscriptions, apply it on the Subscriber server.
-
-
Rename the msoledbsql.dll file in the C:\Program Files\Microsoft SQL Server\150\COM folder:
-
If it's a push subscription, rename the file in the folder on the Distributor server.
-
If it's a pull subscription, rename the file in the folder on the Subscriber server.
-
-
Copy the msoledbsql.dll file from the C:\Windows\System32\ folder and paste it to the C:\Program Files\Microsoft SQL Server\150\COM folder.
-
-
To work around this issue for merge replication, follow these steps:
-
Apply SQL Server 2019 Cumulative Update 15 (CU15) or a later version on the Distributor server.
-
Follow all the steps that are applied to the transactional replication issue.
-
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
