SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

fralo
Posting Yak Master

142 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]
   AFTER UPDATE, INSERT
   NOT FOR REPLICATION
AS 
  
BEGIN
  DECLARE @empid varchar(20);
  DECLARE @capcode01 varchar(20);

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

  -- 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
    FROM EMPMAST
   WHERE empid = @empid
    
  COMMIT TRANSACTION
	 
   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' )

END

sunitabeck
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 http://msdn.microsoft.com/en-us/library/ms190974(v=sql.100).aspx 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

fralo
Posting Yak Master

142 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000