SQL Server 2000 - Linked Server

Question

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

And this is my trigger,

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

Please help me.

Regard, Mubarak

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! If you post error messages, please use the blockquotes ( ` " ` ) to properly format the error message. With this, you never need any <br/> in your post! — marc_s

Dion Dion · Accepted Answer · 2011-07-11T14:32:12

I had to do a few things to get it working. Also using a Win2K SQL server and importing data from MySql.

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

Then the following article was very helpful: http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

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:

Creating a Linked Server in SSMS for a MySQL database

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.

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

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

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

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

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

Stop SQL Server and SQL Server Agent

Start SQL Server and SQL Server Agent

I didn't find I needed to restart the SQL server.

SQL Server 2000 - Linked Server

1 Answers