sql server replication:
We have a replicated environment with hundreds of subscribers (SQL2000 SP3a).
Our server (EMOCADB..MDS) subscribes to a 'LoanProduct' subscription which
publishes 'LoanProducts' and 'UserData' to a dozen sub databases on the same
server.
These sub databases then publish 'LoanProducts' and 'UserData' to hundreds
of subscribers.
The database replication structure on the EMOCADB server is a follows:
MDS
|
---MDS_CBRE_COM
|
---MDS_GWM_NZ
|
---MDS_ISO_N
|
---MDS_ISO_Q
|
---MDS_REA_N
|
---MDS_REA_Q
|
---MDS_RWFS_N
|
---MDS_RWFS_Q
|
---etc....
All subscribers to MDS_REA_N, MDS_RWFS_N and MDS_RWFS_Q have begun failing;
-------------------------------------------------------------------------------------------------
error message: "The Merge process could not retrieve column information for
table 'dbo.tblUserGroup'"
error details: "(Source: Merge Replication Provider (Agent); Error number
-2147201016)
"Could not find stored procedure ''.
"Source: EMOCADB (Data source); Error number: 2812)"
-------------------------------------------------------------------------------------------------
We have discoverd that there are 11 stored procedures where select_proc is
null with the following query:
"select select_proc, pubid, artid from sysmergearticles where select_Proc is
null"
We can repair the above problem by copying the procedure name from its pair:
"update sysmergearticles set select_proc = (select select_proc from
sysmergearticles b where b.artid = sysmergearticles.artid and b.select_proc
is not null)
where select_proc is null"
The above update allows replication of all subscribers to complete.
When our static snapshots run over night all the select_proc's become null
again.
We have tried applying SP3 again by first repairing the null select_procs
(as above) then running SP3.
The log files show many errors starting like: "Cannot add rows to sysdepends
for the current stored procedure because
it depends on the missing object 'dbo.sp_generate_agent_parameter'"
We can repair all these missing objects by generating a script for each
missing object from a backup.
Even after we repair these missing objects and re-run SP3, it completes with
errors in the sp_vupgrade_replication.out log:
"sp_MS_marksystemobject: Invalid object name '(null)'." for the two sub
databases
All new subscriptions to these databases fail with:
-------------------------------------------------------------------------------------------------
error message: "Line 1: Incorrect syntax near ','."
error details: "Line1: Incorrect syntax near ','
"Source: EMOCATEST (Data source); Error number: 170)"
---------------------------------------------------------------------
"The Process could not deliver the snapshot to the Subscriber."
"(Source: Merge Replication Provider (Agent): Error number: -2147201001"
-----------------------------------------------------------------------------------------
1) How do we get SQLServer back to a working state?
2) How can we fix these databases so that generating the static snapshot
does not destroy the stored procedures?
3) How can we allow new subscribers to replciate to these databases?
Any help would be appreciated.
Cheers,
