How to update/roll back two tables in separate Dbs from Stored Procedure

/****** Object: Table [dbo].[PS_EMAIL_ADDRESSES] Script Date: 08/14/2012 14:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PS_EMAIL_ADDRESSES](
[EMPLID] [varchar](11) COLLATE Latin1_General_BIN NOT NULL,
[E_ADDR_TYPE] [varchar](4) COLLATE Latin1_General_BIN NOT NULL,
[EMAIL_ADDR] [varchar](70) COLLATE Latin1_General_BIN NOT NULL,
[PREF_EMAIL_FLAG] [varchar](1) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--- Determine if any addresses of this type already exist (any college) ---
SELECT @InputAddrTypeCount = COUNT(*)
FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType

IF @InputAddrTypeCount = 0
BEGIN
--- For every EMPLID a preferred flag is set once to indicate a student's preferred email address ---
BEGIN TRANSACTION
SET @Preferred = (CASE
WHEN EXISTS(SELECT * FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND PREF_EMAIL_FLAG = 'Y')
THEN 'N'
ELSE 'Y'
END)

INSERT INTO EMAIL_ADDRESSES (EMPLID, E_ADDR_TYPE, EMAIL_ADDR, PREF_EMAIL_FLAG)
VALUES (@InputEmplId, @InputAddrType, @InputEmailAddr, @Preferred)

-- Check for SQL Server errors --
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN(4)
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN(0)
END
END
ELSE IF @InputAddrTypeCount = 1
BEGIN
--- Determine if the Domain portion of the input email address matches the existing email address ---
--- Don't update the address if the original address is from another domain (i.e., college)
SELECT @CollegeAddrCount = COUNT(*)
FROM EMAIL_ADDRESSES
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType
AND LOWER(EMAIL_ADDR) LIKE ('%' + @InputEAddrDomain)

IF @CollegeAddrCount <> 1
BEGIN
RETURN(3)
END

BEGIN TRANSACTION
UPDATE EMAIL_ADDRESSES
SET EMAIL_ADDR = @InputEmailAddr
WHERE EMPLID = @InputEmplId
AND E_ADDR_TYPE = @InputAddrType

-- Check for SQL Server errors --
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN(5)
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN(0)
END
END
ELSE --@InputAddrTypeCount > 1
BEGIN
-- Error and exit if more than one address of this type is found ---
RETURN(2)
END

seidensc (8/14/2012)

I posted this question [url=http://www.sqlservercentral.com/forums/Topic1316040-338-1.aspx][/url] awhile back and received some great responses. I have encountered an issue with the proposed solution which I detail below.

To recap we have a stored procedure that is used to update email addresses in our HRMS database. Unfortunately, the HRMS system is splitting into two separate systems (i.e., separate databases on two different physical servers) and the same table will be utilized in both systems.

I'm starting over here because I've been told by our DBA that Peer-to-Peer Transactional replication will not work because the ERP system table does not have a primary key defined. I am not able to add a key either because it would adversely affect the ERP system.

Considering the issues with transactional replication what would be my next best option to keep both database tables in sync when updating an email address via a stored procedure? Our Business rules dictate that the change must be made right away. The databases are linked together.

If the next best solution is via TSQL could you provide a code sample?

The create statement for the table in its current form looks like the following:

/****** Object:  Table [dbo].[PS_EMAIL_ADDRESSES]    Script Date: 08/14/2012 14:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PS_EMAIL_ADDRESSES](
      [EMPLID] [varchar](11) COLLATE Latin1_General_BIN NOT NULL,
      [E_ADDR_TYPE] [varchar](4) COLLATE Latin1_General_BIN NOT NULL,
      [EMAIL_ADDR] [varchar](70) COLLATE Latin1_General_BIN NOT NULL,
      [PREF_EMAIL_FLAG] [varchar](1) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

We currently update the table in the following manner in a stored procedure:

--- Determine if any addresses of this type already exist (any college) --- 
SELECT @InputAddrTypeCount = COUNT(*)             
FROM EMAIL_ADDRESSES            
WHERE EMPLID = @InputEmplId            
AND E_ADDR_TYPE = @InputAddrType            
            
IF @InputAddrTypeCount = 0
BEGIN
  --- For every EMPLID a preferred flag is set once to indicate a student's preferred email address ---
  BEGIN TRANSACTION
  SET @Preferred =  (CASE 
    WHEN EXISTS(SELECT * FROM EMAIL_ADDRESSES            
      WHERE EMPLID = @InputEmplId 
      AND PREF_EMAIL_FLAG = 'Y') 
    THEN 'N' 
      ELSE 'Y' 
    END)

  INSERT INTO EMAIL_ADDRESSES (EMPLID, E_ADDR_TYPE, EMAIL_ADDR, PREF_EMAIL_FLAG)          
  VALUES (@InputEmplId, @InputAddrType, @InputEmailAddr, @Preferred)          
   
   -- Check for SQL Server errors --          
  IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)          
  BEGIN
    ROLLBACK TRANSACTION             
    RETURN(4)          
  END  
  ELSE
  BEGIN
    COMMIT TRANSACTION
    RETURN(0)
  END        
END  
ELSE IF @InputAddrTypeCount = 1
BEGIN
  --- Determine if the Domain portion of the input email address matches the existing email address ---
  --- Don't update the address if the original address is from another domain (i.e., college)
  SELECT @CollegeAddrCount = COUNT(*)
  FROM EMAIL_ADDRESSES            
  WHERE EMPLID = @InputEmplId     
  AND E_ADDR_TYPE = @InputAddrType   
  AND LOWER(EMAIL_ADDR) LIKE ('%' + @InputEAddrDomain)

  IF @CollegeAddrCount <> 1
  BEGIN            
     RETURN(3)  
  END 

  BEGIN TRANSACTION
  UPDATE EMAIL_ADDRESSES            
  SET EMAIL_ADDR = @InputEmailAddr        
  WHERE EMPLID = @InputEmplId            
  AND E_ADDR_TYPE = @InputAddrType
        
  -- Check for SQL Server errors --          
  IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)          
  BEGIN
    ROLLBACK TRANSACTION          
    RETURN(5)          
  END  
  ELSE
  BEGIN
    COMMIT TRANSACTION
    RETURN(0)
  END  
END
ELSE --@InputAddrTypeCount > 1  
  BEGIN
  --  Error and exit if more than one address of this type is found ---            
  RETURN(2)
  END

Thank you for your response!
Steve