sql server replication: Hi,

I have a problem at the moment with replication.

Publisher & distributer - SQL2000 Server
Subscriber - SQL2000 Server
Transational Replication


Here is a simplified schema.

CREATE TABLE ClientData ( CID CHAR(17) primary key , Code1 VARCHAR(6) NOT
NULL , otherFields varchar(10) Null )
CREATE TABLE InternalLookup ( ILookupID INT PRIMARY KEY ,
InternalDescription VARCHAR(50) NOT NULL )

CREATE TABLE ClientLookup ( Code1 VARCHAR(6) PRIMARY KEY , ClientDescription
VARCHAR(50) NOT NULL , ILookupID INT NULL )

CREATE TABLE InternalData ( IID INT PRIMARY KEY , CID CHAR(17) NOT NULL
UNIQUE , ILookupID INT NULL )

All these tables are located and maintained locally on our lan servers.


ClientData - contains the records sent by the client, this is imported from
files they ftp to us. It has children tables that are related by
CID(char17).

ClientLookup - is a distinct list of Code1 Records and their descriptions,
ILookupID is edited Manually based on out own internal lookup descriptions.

InternalLookup - is our own internally maintained list of records, these
belong to a 3NF schema and contains multiple client information.

InternalData - Our own version of the client data, converted to 3NF, child
tables are now linked by IID, not CID.

InternalData contains apx to 500,000 records, with the distribution of Code1
at about 5-10%.

InternalData is replicated to a read-only subscriber which is hosted on a
remote webserver.


Sometimes the client sends Code1 in ClientData before we get the record in
ClientLookup and so, for a period of time we do not know which record in our
system it needs to be mapped to.


When we find out what Code1 is, it is mapped in InternalLookup and
InternalData is updated for the affected records - which is where the
problem lies.

Because the records are updated in one update statement - a lock is put on
the subscriber, the commands are sent to the subscriber one command at a
time, and then the lock is released.

This freezes the front end until the subscriber has received all the
commands for that transaction - other than upgrading to SQL2k5 Enterprise
and using Row versioning, is there anyway to stop this lock being placed?
Creating a loop to write the updates would be possible, but it would take an
age to update the table.



Thanks

Please see my reply to your post 'replication locking...'.
Cheers,
Paul Ibison, SQL Server MVP