Transation replication with updateable subscribers

I have a main database that I need to distribute a copy of to another server
for use by anotherb department to relieve load on the database server. Bothg
need to be except replicas at all times.

The database is currently hosted on a SQL Server 2005 Standard edition 64Bit
database server. The Subscriber will be a SQL server 2000 std edition
database server.

Initially I had created a transactional replication type publication and
when I created the subscriper from the 2005 SQL box it took off and I could
see my 45 GB database beginning to build on the SQL 2000 box in the directory
I had pointed at.

I continued to read about replication as it was building and realized that I
was supposed to create a publication that was a transactional publication
with updating subscribers to allow the SQL boxes to stay synced. I then
killed the subscriber, the publication and even recreated the snapshot, then
the new publication as a transactional publication with supdating
subbscribers. I then added the SQL2000 box as a subscriber again. The
problem is that now that I have done this it seems
to be taking forever to build the subscriber database. Its been 1 hr and
its only 21MB. Almost on an excact interval of 5 minutes the Replication
monitor errors out. If I go into the subscription watch list and if ZI
choose to view details on that subscription I can see that at an almost exact
interval of 5 minutes the same error keeps occurring.

"Insert Error: The column name or number of supplied value does not match
table definition."

And the underlying sql staement that is related is allways the same. It is:

CREATE procedure "dbo"."_GetKey_RunNumber" @TDate as VarChar(10), @Daily as
int, @NewKey AS INT OUTPUT
AS
BEGIN TRAN
DECLARE @numJobs int
DECLARE @numJobs2 int
SELECT @numJobs = max(highrun) from Next_RunNumber where tdate = @TDate
if @numJobs > 0
Update Next_RunNumber set @NewKey = highrun = highrun + 1 where tdate =
@TDate
else
BEGIN
if @Daily = 1
SELECT @numJobs2 = max(runnumber) from trips where tdate = @TDate
ELSE
SELECT @numJobs2 = max(runnumber) from trips where tdate >
(Transaction sequence number: 0x001A163C00016B48015200000000, Command ID:
2405)

The only way I could automatically keep things running was to go to the
distributor and start something called a "Common Job". Specifically the job
I started was the one called "Reinitialize subscriptions having data
validation errors". Until I started that job I would have to open the
replication monitor and click START JOB every time or it would just sit there.

Any help would be greatly appreciated.









.

Relevant Pages

  • Re: SQL2000 - Merge Replication - Timeout error
    ... The timeout is in your publisher and distributor server, ... Then, update the database statistics. ... Run profiler on the subscriber to see what is going on? ...
    (microsoft.public.sqlserver.replication)
  • SQL Server merge replication security
    ... Microsoft SQL Server replication. ... need to have the sysadmin role on their local database to initiate the ... The AppAdmin table cannot be removed from the publication as then the ...
    (Focus-Microsoft)
  • Re: transactional replication problem
    ... If the distribution agen can't connect to the subscriber, ... Looking for a SQL Server replication book? ... > server B. Problem is with only one database. ...
    (microsoft.public.sqlserver.replication)
  • Merge replication and deadlocks
    ... Distributor is in my publication server. ... I am running the agent continuosly in each subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: trans replication of large tables...
    ... decrease the time required to deploy the snapshot and if your line is ... > subscriber, the subscription is created specifying that the subscriber ... >> be in the same publication - or if they are queried in the same procs ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading