Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update/roll back two tables in separate Dbs from Stored Procedure - Take 2 Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 2:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:59 AM
Points: 10, Visits: 36
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
Post #1345034
Posted Tuesday, August 14, 2012 9:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:46 PM
Points: 316, Visits: 1,498
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.
Post #1345123
Posted Tuesday, August 14, 2012 10:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:11 PM
Points: 35,769, Visits: 32,432
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1345129
Posted Wednesday, August 15, 2012 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:59 AM
Points: 10, Visits: 36
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.
Post #1345235
Posted Wednesday, August 15, 2012 8:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
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



Making the reference to the other thread easier for others:

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



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1345287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse