Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transaction has been aborted
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

161 Posts

Posted - 10/01/2012 :  10:00:37  Show Profile  Reply with Quote
The following trigger requests data from sql server and updates a MYSQL table on another server. The code works and the data is updated, but it nevertheless returns an error:

"Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted."

I've searched and searched on the internet, trying a few different things but nothing seems to work. Your help is greatly appreciated.

ALTER TRIGGER [dbo].[DXPagerUpdate]
   ON [dbo].[ip_PagerProgramming]
  DECLARE @empid varchar(20);
  DECLARE @capcode01 varchar(20);

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.

  -- Get values from virtual INSERTED table to populate MySQL
  SELECT @empid = empid, @capcode01 = capcode01 FROM INSERTED

  -- Get values from EMPMAST to populate MySQL database

  DECLARE @fname varchar(20);
  DECLARE @lname varchar(20);
  DECLARE @email varchar(40);
  DECLARE @unit varchar (40);

  SELECT @fname = fname, @lname = lname, @email = email, @unit = unit
   WHERE empid = @empid
   UPDATE MYSQL...pagers
      SET capcode = @capcode01 
    WHERE pager = @empid

    IF @@ROWCOUNT = 0
    Insert into MYSQL...pagers (pager, valid, capcode, divert, 
           ptype, fn, netid, [repeat], numpages, list, fname, lname, 
           department, email, email2, preset1, preset2, preset3, 
           preset4, preset5, calls, dbase)
    Values (@empid, 'Valid', @capcode01, '', 'Pocsag Alpha 1200' , 
            1, 'None', NULL, 0, 'Yes', @fname, @lname, @unit, 
            @email, '', '', '', '', '', '', NULL, 'ECSO' )


Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  10:33:58  Show Profile  Reply with Quote
It is the commit statement in the trigger that is generating the error. See here. From When triggers that include COMMIT or ROLLBACK TRANSACTION statements are executed from a batch, they cancel the whole batch. In SQL Server 2008 and SQL Server 2005, an error is also returned.

Cross-server triggers is something that I am not familiar with and honestly something that scares me. Hopefully someone else will offer better suggestions.
Go to Top of Page

Posting Yak Master

161 Posts

Posted - 10/01/2012 :  10:38:13  Show Profile  Reply with Quote
Thanks. But I had to add the COMMIT TRANSACTION to clear up this error:

"OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".
Msg 7391, Level 16, State 2, Procedure DXPagerUpdate, Line 41
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction."

And yes, I have downloaded the driver onto the server.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000