2005 Replication with SCHEMABINDING Subscriber Views

Hi Folks

Is there an easy way around this ?

One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables.

Main table has 4 Million Rows

ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object .... [schemaBound View]

GWmmmm - not much interest in this thread :eek:

well for info I'm trying to backup any schemabound objects with the following code (Showing the OBJECT_DEFINITION Usage).


ALTER PROCEDURE [dbo].[usp_SchemaBoundObjects_Backup]
AS
SET NOCOUNT ON
IF(SELECT OBJECT_ID('SchemaBoundObjects')) IS NULL
BEGIN
CREATE TABLE SchemaBoundObjects
(ObjName SysName,ObjType VarChar(50),ObjText nVarChar(4000))
END
ELSE
BEGIN
TRUNCATE TABLE SchemaBoundObjects
END
INSERT INTO SchemaBoundObjects
SELECT DISTINCT OBJECT_NAME(o.object_id) ObjName
,o.type_desc ObjType
,OBJECT_DEFINITION (o.object_id) ObjText
FROM sys.sql_dependenciesAS d
JOIN sys.objectsAS o
ON d.object_id= o.object_id
AND o.type IN('FN','IF','TF','V','P')
WHERE d.class = 1
INSERT INTO SchemaBoundObjects

-- Does'nt work
SELECT si.name,'INDEX',OBJECT_DEFINITION (OBJECT_ID(si.name)) ObjText
FROM sys.indexes si
JOIN sys.views sv
ON sv.object_id= si.object_id
WHERE sv.name IN (SELECT ObjName FROM SchemaBoundObjects)


Programatically ReInitialize Subscription, spike the Distributor - Then Recreate the Objects (which I have the Code for).

Now if I can just sort out the sodding View Indexes which don't work Grrr :(

C'mon folks Join in anytime :p

GW