Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
Thesubscription argument is the location identiļ¬er.
Insome circumstances it makes sense to add the KeyPool table to an
existingpublication and use the same argument to subscribe to each
publication. Here we keep the location and rep_key valuesdistinct to
providea more general solution.
Filling and replenishing the key pool
Everytime a user adds a new customer, their pool of available primary keys
isdepleted by one. Theprimary key pool table needs to be periodically
replenishedat the consolidated database using a procedure such as the
following:
CREATE PROCEDURE ReplenishPool AS
BEGIN
DECLARE @CurrTable VARCHAR(40)
DECLARE @MaxValue INTEGER
DECLARE EachTable CURSOR FOR
SELECT table_name, max(value)
FROM KeyPool
GROUP BY table_name
DECLARE @CurrLoc VARCHAR(6)
DECLARE @NumValues INTEGER
DECLARE EachLoc CURSOR FOR
SELECT location, count(*)
FROM KeyPool
WHERE table_name = @CurrTable
GROUP BY location
OPEN EachTable
WHILE 1=1 BEGIN
FETCH EachTable INTO @CurrTable, @MaxValue
IF @@sqlstatus != 0 BREAK
OPEN EachLoc
WHILE 1=1 BEGIN
FETCH EachLoc INTO @CurrLoc, @NumValues
IF @@sqlstatus != 0 BREAK
-- make sure there are 10 values
WHILE @NumValues < 10 BEGIN
SELECT @MaxValue = @MaxValue + 1
SELECT @NumValues = @NumValues + 1
INSERT INTO KeyPool
(table_name, location, value)
VALUES (@CurrTable, @CurrLoc, @MaxValue)
END
END
CLOSE EachLoc
END
CLOSE EachTable
END
go
177