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

Question

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

seidensc

SSC-Addicted

Points: 402
More actions
August 14, 2012 at 2:44 pm

#266005

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 1

Designate one db the "owner" of the table, and force all inserts, updates and deletes to go there. Use a trigger and linked server to update the other tablein a synchronous fashion.

Or

Just have one copy of the table and use a synonym and a linked server, to make it look like two tables.

Or

inside the sp use service broker to send a message to the other server with the parameters, and execute the same logic there asynchronously.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

I guess my questions would be...

1. Do the tables have to be "dual mastered"? That is, can both servers make an update to the email table? Or just one?

2. What do you want to have happen when one of the two servers are down?

3. What do you want to have happen when the down server comes back up?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

seidensc SSC-Addicted Points: 402 More actions · Answer 3

First of all thank you for your replies.

1. Both servers update the email table. HR adds employee data, Campus Solutions adds student data. A student can also be an employee and vice versa. The key is EMPLID which is shared across systems. An employee will have a EMPL type and a student will have a CAMP type. There are other types as well (like HOME, BUSN, OTHR, etc.) and on the student side the types can change over the life cycle of the student.

2. When one of the servers are down we can roll back everything and attempt the update the next time the Email Update program is executed -or- (black box) update the down server when it comes back up.

3. Make any updates in the order they were received.

Regarding SCC Veteran's reply: the table is delivered and used by the ERP system it is a part of so I'm not able to change the current definition or process updates.

The service broker may hold some promise but I will have to research it and see what is available in our environment.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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

Making the reference to the other thread easier for others:

http://www.sqlservercentral.com/forums/Topic1316040-338-1.aspx