a server procedure can not rollback or commit  a  multi-database
  transaction

  Explanation:   You  attempted  to  ROLLBACK  or  COMMIT   in   a
  multistatement,  or stored procedure which executes as part of a
  transaction  which  spans  multiple  databases.   This  is   not
  permitted because the server procedure is not aware of the other
  databases in the transaction.

  User Action:  Instead execute a  single  statement  ROLLBACK  or
  COMMIT which is perform by the client and informs all associated
  databases.