Linked Servers Trigger Proglems

[andyd273]

I have a linked server setup between MS SQL Server 2000 and MySql, and it works great for normal things like selects, inserts, deletes, etc.

However, when I tried setting up a trigger to update it automatically, I ran into problems.
The trigger looks like:

Create trigger subscription_insert on dbo.tblCustomerSubscriptions
for insert
as
set xact_abort on
insert into openquery(webdb, 'select * from tblCustomerSubscriptions') (CustomerSubscriptionID,SubscriptionID,CustomerID,SubscriptionDate,LabelName)
select CustomerSubscriptionID,SubscriptionID,CustomerID,SubscriptionDate,LabelName from INSERTED

I have identical tables set up on both the local MS SQL Server, and the remote MySql database, with the only difference being that the primary key CustomerSubscriptionID is auto-incrementing on the MS Sql Server, and isnt on MySql so that I can assign it the local value.

Local:
CREATE TABLE [tblCustomerSubscriptions] (
[CustomerSubscriptionID] [int] IDENTITY (1, 1) NOT NULL ,
[SubscriptionID] [int] NULL ,
[CustomerID] [int] NULL ,
[SubscriptionDate] [datetime] NULL ,
[LabelName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tblCustomerSubscriptions] PRIMARY KEY CLUSTERED
(
[CustomerSubscriptionID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Remote:
Field Type Null Key Default Extra
CustomerSubscriptionID int(4) NULL PRI 0 NULL
SubscriptionID int(4) YES NULL NULL NULL
CustomerID int(4) NULL NULL 0 NULL
SubscriptionDate date YES NULL NULL NULL
LabelName char(50) YES NULL NULL NULL


when I try to insert into the local table with the trigger in place:

insert into tblCustomerSubscriptions (SubscriptionID,CustomerID,SubscriptionDate,LabelName) values (1,41614,'1/31/2006','tester guy')

I get the error:
Server: Msg 8524, Level 16, State 1, Procedure subscription_insert, Line 7
The current transaction could not be exported to the remote provider. It has been rolled back.


Any way to fix this?