sql-server - SQL Server 2000 - 链接服务器

Question

对于我们的应用程序，我们使用 SQL Server 2000 和 MySQL。如果在 SQL Server 2000 中有任何修改，我想更新 MySQL 数据库。为此，我创建了 MySQL 的链接服务器。它工作正常，但在触发器内部，它显示了类似的错误消息

[OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver]Optional feature not supported]
Msg 7391, Level 16, State 1, Procedure , Line 6
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction."

这是我的触发器，

alter trigger upd_test_enum
on mtest
for insert
as
begin
   insert into emsdev...test_enum (id, name, is_active) values (4, 'Test4', 0)
end

请帮我。

看待，
穆巴拉克

Answer 1

我必须做一些事情才能让它工作。还使用 Win2K SQL 服务器并从 MySql 导入数据。

我们做的一件事是安装 3.51.22 MySql ODBC 驱动程序的版本 (mysql-connector-odbc-3.51.22-win32.msi)。

那么下面这篇文章很有帮助:http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

主要是关闭交易以及引用 DSN 创建链接服务器时。为了便于引用，我将复制并粘贴下面这篇文章中 SQLServerCentral 文章中的说明:

Creating a Linked Server in SSMS for a MySQL database

1. Download the MySQL ODBC driver from mysql.com

2. Install MySQL ODBC driver on Server where SQL Server resides -Double Click Windows Installer file and follow directions.

3. Create a DSN using the MySQL ODBC driver Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) -Click on the System DSN tab -Click Add -Select the MySQL ODBC Driver -Click Finish On the Login Tab: -Type a descriptive name for your DSN. -Type the server name or IP Address into the Server text box. -Type the username needed to connect to the MySQL database into the user text box. -Type the password needed to connect to the MySQL database into the password text box. -Select the database you'd like to start in. On the Advance Tab: Under Flags 1: -Check Don't Optimize column width. -Check Return Matching Rows -Check Allow Big Results -Check Use Compressed protocol -Check BIGINT columns to INT -Check Safe Under Flags 2: -Check Don't Prompt Upon Connect -Check Ignore # in Table Name Under Flags 3: -Check Return Table Names for SQLDescribeCol -Check Disable Transactions Now Test your DSN by Clicking the Test button

4. Create a Linked Server in SSMS for the MySQL database SSMS (SQL Server Management Studio -> Expand Server Objects -Right Click Linked Servers -> Select New Linked Server On the General Page: -Linked Server: Type the Name for your Linked Server -Server Type: Select Other Data Source -Provider: Select Microsoft OLE DB Provider for ODBC Drivers -Product name: Type MySQLDatabase -Data Source: Type the name of the DSN you created On The Security Page -Map a login to the Remote User and provide the Remote Users Password -Click Add under Local server login to remote server login mappings: -Select a Local Login From the drop down box -Type the name of the Remote User -Type the password for the Remote User

5. Change the Properties of the Provider MSDASQL Expand Providers -> Right Click MSDASQL -> Select Properties -Enable Nested queries -Enable Level zero only (this one's the kicker) -Enable Allow inprocess -Enable Supports 'Like' operator

6. Change settings in SQL Server Surface Area Configuration for Features -Enable OPENROWSET and OPENDATASOURCE support.

7. Change settings in SQL Server Surface Area Configuration for Services and Connections -Enable Local and Remote connections via TCP/IP and named pipes

8. Stop SQL Server and SQL Server Agent

9. Start SQL Server and SQL Server Agent

我没有发现我需要重新启动 SQL 服务器。