seidensc (8/14/2012)

---

I posted this question 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