SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
seidensc
seidensc
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 1499
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86868 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
seidensc
seidensc
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39670 Visits: 38562
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

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search