SQL Server 2000-链接服务器 [英] SQL Server 2000 - Linked Server
问题描述
对于我们的应用程序，我们使用SQL Server 2000& MySQL的.如果要在SQL Server 2000中进行任何修改，我想更新MySQL数据库.为此，我创建了MySQL链接服务器.它工作正常，但在触发器内部显示错误消息，如
For our application, we using SQL Server 2000 & MySQL. I want to update the MySQL database if any modifications in SQL Server 2000. For this, I have created the Linked Server for MySQL. It works fine, but inside the trigger it shows the error message like
[OLE/DB提供程序返回 讯息:[MySQL] [ODBC 3.51 驱动程序]没有可选功能 支持]
讯息7391，层级16 状态1，程序， 第6行
无法执行该操作，因为OLE DB提供程序'MSDASQL'无法开始分布式事务."
[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
请帮助我.
关于， 穆巴拉克
推荐答案
我必须做一些事情才能使其正常工作.还使用Win2K SQL Server并从MySql导入数据.
I had to do a few things to get it working. Also using a Win2K SQL server and importing data from MySql.
我们所做的一件事是安装 3.51.22 版本的MySql ODBC驱动程序(mysql-connector-odbc-3.51.22-win32.msi).
One thing we did was to install the 3.51.22 version of the MySql ODBC driver (mysql-connector-odbc-3.51.22-win32.msi).
然后，以下文章非常有帮助: http://www.sqlservercentral. com/Forums/Topic340912-146-1.aspx
Then the following article was very helpful: http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx
最主要的是在创建链接服务器时关闭事务并引用 DSN .为了便于参考，我将在下面的这篇文章中复制并粘贴SQLServerCentral文章中的说明:
The main thing was to switch off transactions as well as to refer to the DSN when creating a Linked Server. For ease of reference I'll copy and paste the instructions from the SQLServerCentral article in this post below:
在SSMS中为MySQL数据库创建链接服务器
Creating a Linked Server in SSMS for a MySQL database
- 从mysql.com下载MySQL ODBC驱动程序
-
在SQL Server所在的服务器上安装MySQL ODBC驱动程序-双击Windows Installer文件并按照说明进行操作.
- Download the MySQL ODBC driver from mysql.com
Install MySQL ODBC driver on Server where SQL Server resides -Double Click Windows Installer file and follow directions.
使用MySQL ODBC驱动程序创建DSN开始->设置->控制面板->管理工具->数据源(ODBC)-单击 在系统DSN选项卡上-单击添加-选择MySQL ODBC驱动程序 -在登录"选项卡上单击完成":-为您的DSN键入一个描述性名称. -在服务器"文本框中键入服务器名称或IP地址. -在用户文本框中输入连接到MySQL数据库所需的用户名. -输入连接到MySQL所需的密码 数据库输入密码文本框. -选择您想要的数据库 开始.在高级"选项卡上:在标志1下:-选中不优化" 列宽. -检查返回的匹配行-检查允许大结果 -检查使用压缩协议-检查BIGINT列是否为INT-检查标志2下的安全:-检查连接时不提示-检查忽略# 在表名下的标志3中:-检查返回表名是否 SQLDescribeCol-检查立即禁用事务 点击测试"按钮
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
在SSMS中为MySQL数据库SSMS创建链接服务器(SQL Server Management Studio->展开服务器对象-右键单击链接 服务器->在常规页面上选择新的链接服务器:-链接 服务器:键入链接服务器的名称-服务器类型:选择 其他数据源-提供程序:选择Microsoft OLE DB提供程序用于 ODBC驱动程序-产品名称:类型MySQLDatabase-数据源:类型 您在安全性页面"上创建的DSN的名称-将登录名映射到 远程用户并提供远程用户密码-单击添加 在本地服务器登录到远程服务器登录映射下:-选择一个 本地登录从下拉框中-键入远程用户的名称 -输入远程用户的密码
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
更改提供程序的属性MSDASQL展开提供程序->右键单击MSDASQL->选择属性-启用嵌套查询 -仅启用零级(这是踢脚)-启用允许进程中-启用支持赞"运算符
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
更改SQL Server外围应用配置中的功能设置-启用OPENROWSET和OPENDATASOURCE支持.
Change settings in SQL Server Surface Area Configuration for Features -Enable OPENROWSET and OPENDATASOURCE support.
更改服务和连接的SQL Server外围应用配置中的设置-通过启用本地和远程连接 TCP/IP和命名管道
Change settings in SQL Server Surface Area Configuration for Services and Connections -Enable Local and Remote connections via TCP/IP and named pipes
停止SQL Server和SQL Server代理
Stop SQL Server and SQL Server Agent
我没有发现需要重新启动SQL Server.
I didn't find I needed to restart the SQL server.
这篇关于SQL Server 2000-链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
