SQL 2005

I have a linked MySQL server. I have written a script which updates
the MySQL server correctly when run from the Management Studio using
OPENQUERY.

However, when I try to incorporate this script as a trigger on the
table, SQL server returns.

"Msg 7391, Level 16, State 2, Procedure TRIGGERNAME, Line 51
The operation could not be performed because OLE DB provider "MSDASQL"
for linked server "LINKEDSERVER" was unable to begin a distributed
transaction."

I have found
http://groups.google.com/group/micro...7255ab5a3a1fe0
but will admit to not understanding most of it.

1. Why does making the script into a trigger turn it into a
'distributed transaction'? The only updates in the trigger are against
the MySQL database.

2. I have tried 'begin transaction mysql' with the appropriate commit
at the end, no diff.

3. I have reduced the trigger to one update statement. No change.

4. I have used SET XACT_ABORT ON at the top of the trigger. No change.
Any suggestions?

TIA

Iain

All SQL Server DML statements run in a transaction, either explicit or
implicit, and a trigger executes in the transaction context of the statement
that fired the trigger. Transactional consistency must be maintained for
both the SQL Server data and data in any external DBMS updated within the
transaction. This is accomplished using distributed transactions.

Unfortunately, I don't know much about MySQL. Maybe someone else can jump
in.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Iain Sharp" <iains (AT) pciltd (DOT) co.uk> wrote

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
In addition to Dan's post, getting distributed transactions to work can
be a nightmare even when both databases are SQL Server.

Distributed transactions are handled by MSDTC which is part of the
operating system, not SQL Server.

To start with, there has to be support in the OLE DB provider, or when
you use MSDASQL, in the ODBC driver for distributed transactions. How this
works with MySQL, I have no idea. But it can be an uphill battle to solve
that, particularly if the databases are on different machines. (As this
requires two instances of MSDTC to talk with each other.)

I think you should investigate alternate solution. One approach is to
use Service Broker, but this can easily result in the same problem. That
is, you don't want to commit getting the message of the queue until you
have updated the table. But then you have a distributed transaction
again. But you use Service Broker as a triggering mechanism. That
is, get the message off the queue into a local table, commit. Then
process all unprocess all unprocessed rows in the table, update
the MySQL database, if this has not been done. Mark the row as processed.
Note that you must be table to handle the case that the MySQL row has
been updated, although your worktable says it hasn't. That is, you are
rolling your own two-phase commit.

Not very fun, but I rather do it than fighting MSDTC.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 16 Mar 2010 22:42:33 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:


Ewww. I'm going to geo the simple bu t anooying method.

I have a script which compares the two databases and updates the MySQL
one to what I need to be. Since this is a limited subset of the SQL
server database, I think I can optimise these scripts to run
reasonably quickly (10-30 secs)

So I'm going to schedule them to run every 3 minutes, not perfect but
good enough.

Iain

Iain Sharp (iains (AT) pciltd (DOT) co.uk) writes:
Far better that wrestling distributed transactions in my opinion.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx