Replication Problem

Question

Post reply

Replication Problem

Vivek Shukla

SSCrazy

Points: 2146
More actions
October 16, 2009 at 4:08 am

#212124

I've got replication up and running now but when i check subscriber status after setting up subscription it shows me following errors.
"
Error messages:
The schema script 'dtblGlobalHisto4ec959fd_86.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Cannot DROP TABLE 'dbo.dtblGlobalHistoryTemplate' because it is being referenced by object 'vwTypeHistoryTypeIsUserHistory'. (Source: MSSQLServer, Error number: 3729)
Get help: http://help/3729
"
It seems like it is having trouble applying schema scripts to subscriber and because of this when i look at syncronization status it is showing "The server 'TESTBRANCH1\TestDB' is not a Subscriber. (.Net SqlClient Data Provider)".
Any suggestions regarding this.
Vivek Shukla - MCTS SQL Server 2008

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

On your subscriber, do you have foreign key constraints enabled? If you do, drop the foreign key and then try again.

-Roy

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 2

Hi Roy

Thanks for your reply but the error was occuring because there was some metadata left behind when i deleted old subscriptions and publications.

Thanks for getting back to me though.

Vivek

Vivek Shukla - MCTS SQL Server 2008

[sujith] SSC Enthusiast Points: 141 More actions · Answer 3

Hey Vivek,

Can you please let me know how you deleted the metadata of your old subscriptions?

Thanks,

Sujith

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 4

Hi Sujith

Please use followoing stored procedure.

sp_mergemetadataretentioncleanup

Regards,

Vivek

Vivek Shukla - MCTS SQL Server 2008

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

Hi,

The clue is in the error message as the name follows a simple view naming convention.

Vivek Shukla (10/16/2009)
"
Cannot DROP TABLE 'dbo.dtblGlobalHistoryTemplate' because it is being referenced by object 'vwTypeHistoryTypeIsUserHistory'. (Source: MSSQLServer, Error number: 3729)
Get help: http://help/3729

It looks like the schema script cannot be applied because there is a Schemabound view pointing at the table on the subscriber. You can duplicate the error using this script.

/* Create view against already existing temp table */

CREATE VIEW dbo.vwTestRename WITH SCHEMABINDING

AS

SELECT [Col1]

FROM [dbo].[Test]

GO

USE [Test]

GO

/****** Object: Table [dbo].[TestRename2] Script Date: 11/23/2010 07:39:47 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test) AND type in (N'U'))

DROP TABLE [dbo].[Test]

GO

You can use sp_depends on the subscriber to find the dependancy.

sp_depends 'Test'