如何在SQL Server 2012中使用链接服务器转换为DB2 / AS400的二进制数据？

We have two database servers, the both of them using SQL Server 2012, but the OS is different. The older one is using Windows Server 2008R2 Standard, while the newer one is Windows Server 2012R2 Standard, both of them is x64. The other difference is the exact version number of the SQL Server, so the older is

我们有两个数据库服务器，它们都使用SQL Server 2012，但操作系统不同。较旧的一个使用的是Windows Server 2008R2 Standard，而较新的一个是Windows Server 2012R2 Standard，它们都是x64。另一个区别是SQL Server的确切版本号，所以旧版本是

• Microsoft SQL Server Management Studio 11.0.3128.0
• Microsoft SQL Server Management Studio 11.0.3128.0
• Microsoft Data Access Components (MDAC) 6.1.7601.17514
• Microsoft数据访问组件（MDAC）6.1.7601.17514
• Operating System 6.1.7601
• 操作系统6.1.7601

while the newer:

而较新的：

• Microsoft SQL Server Management Studio 11.0.5343.0
• Microsoft SQL Server Management Studio 11.0.5343.0
• Microsoft Data Access Components (MDAC) 6.3.9600.17415
• Microsoft数据访问组件（MDAC）6.3.9600.17415
• Operating System 6.3.9600
• 操作系统6.3.9600

I already created a properly working linked server to get data from our DB2/AS400 database on the old server. I used Data Access Tool 4.0 to make a connection string and got the following one:

我已经创建了一个正常工作的链接服务器来从旧服务器上的DB2 / AS400数据库中获取数据。我使用Data Access Tool 4.0创建了一个连接字符串并获得了以下内容：

Provider=DB2OLEDB;User ID=****;Initial Catalog=CAT;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=****;Network Port=446;Package Collection=DATA;Default Schema=DATA;Process Binary as Character=True;Units of Work=RUW;Default Qualifier=DATA;DBMS Platform=DB2/AS400;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Database Name=TEST_DB;Authentication=Server;Decimal As Numeric=False;Derive Parameters=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;

Nowadays I would like to make the same linked server on the new one, but it couldn't convert the binary data as a proper one.

现在我想在新服务器上创建相同的链接服务器，但它无法将二进制数据转换为正确的服务器。

For example data from old server compared to the new one:

例如，旧服务器的数据与新服务器的数据相比：

1. "" === 0x404040
2. “”=== 0x404040
3. M === 0xD4
4. M === 0xD4

Connection string that used on the new server:

在新服务器上使用的连接字符串：

Provider=DB2OLEDB;User ID=****;Initial Catalog=CAT;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=****;Network Port=446;Package Collection=DATA;Default Schema=DATA;Process Binary as Character=True;Units of Work=RUW;Default Qualifier=DATA;DBMS Platform=DB2/AS400;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Database Name=TEST_DB;Authentication=Server;Decimal As Numeric=False;Derive Parameters=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;

I already tried:

我已经尝试过：

• Used Data Access Tool 4.0 because it already worked in the past (Process Binary as Character = True)
• 使用过的Data Access Tool 4.0，因为它已经在过去工作过了（Process Binary as Character = True）
• Used Data Access Tool 5.0 to be more compatible with SQL Server 2012 (Process Binary option is not included)
• 使用的数据访问工具5.0与SQL Server 2012更兼容（不包括进程二进制选项）
• Installed hotfix from the MS Support (https://support.microsoft.com/en-us/kb/2993741)
• MS支持部门安装的修补程序（https://support.microsoft.com/en-us/kb/2993741）
• Went through the possible settings and tried some combinations
• 通过可能的设置并尝试了一些组合
• Compared the already working linked server and the newly created one
• 比较已经工作的链接服务器和新创建的服务器

Please help me how can I solve this problem!

请帮帮我怎样才能解决这个问题！

1 个解决方案

#1

Installed the i series ODBC driver on to the system and configured a system DSN, which should connect to the database.

将i系列ODBC驱动程序安装到系统上并配置了一个系统DSN，它应该连接到数据库。

https://social.msdn.microsoft.com/Forums/en-US/eae2d05f-32e8-4541-87fb-70fc12697d15/ms-ole-db-provider-for-db2-v5?forum=sqldataaccess

https://social.msdn.microsoft.com/Forums/en-US/eae2d05f-32e8-4541-87fb-70fc12697d15/ms-ole-db-provider-for-db2-v5?forum=sqldataaccess