Retry table update until success

[johnc83]

Hi all,

I've hit on this subject on this site before as part of other requests but now I am needing to make some progress on it...

I have a Database Table (tblCustomers) at our office in town A. There is an identical table in town B. Whenever there is an insert/update/delete command sent to town A, I need the same update to be sent to town B.

Due to lots of connection drops between sites, I cannot guarantee that when the town A update takes place, the connection to town B will be active.

Because of this I need the system to do something like this..

UPDATE [TownA].[dbo].[tblCustomers]
   SET [Cust_Name] = 'My Biggest Customer'
       WHERE [Cust_Code] = 'BIGCUST'

then a corresponding command to be held somewhere (maybe in a table) and constantly retried until it is successful, upon which time it is deleted from that table. (to be added again when there is another update)

Could anyone give me a starting point for this task please?

Thanks in advance for any replies..

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[mrdenny]

John,
Merge replication will do exactly what you are trying to do.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[johnc83]

Hi Denny, thanks for the reply. I am using SQL Server 2005 Express. Is this merge replication possible with this?

If not, then is there a workaround?

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[mrdenny]

Yes SQL Express can be in a Merge Replication, but only as a subscriber. You would need to have a Workgroup or higher edition as the publisher.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog