大网数据广告活动
数据库与应用安全

ASCII function returns different results in Publisher and Subscriber

· 52CE 我爱测

This article provides workarounds for the issue that the ASCII function returns different results in the Publisher and Subscriber database tables.

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:

    1. Open SQL Server Management Studio and connect to the server acting as a Distributor.

    2. Under Object Explorer, expand SQL Server Agent, and then expand Jobs.

    3. Select the snapshot agent job for the affected publication, right-click it, and then select Properties > Steps > Step 2 > Edit.

    4. In the Job Step Properties window, add -NativeBcpFileFormatVersion 100 at the end of the command and select OK to save the changes.

    5. 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.

    6. 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.

    7. 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:

    1. Apply SQL Server 2019 Cumulative Update 15 (CU15) or a later version on the Distributor server.

    2. 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.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 01/20/2022
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。