Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: 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]

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    mmmm - not much interest in this thread

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

    Code:
    
    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

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Just for completeness I'll close this off in case anyone else comes up against this.

    I expanded the above code to Backup views succesfully etc & ended up with 3 SP's [Backup Objects][Drop Objects][Recreate Objects]

    On the Publication theres an option to run user defined .sql scripts before & after initialization which I used to call the above.

    Up to the moment it works exceedingly well - so I guess I rereached my own solution.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Jul 2008
    Posts
    2
    Hey, GWilliy-

    It looks like I'm facing the same exact problem you were dealing with 6mo ago. I was thinking of doing something along the same lines as your SPs, and I figured if it gave you such trouble it must not be as straightforward as it initially seems. Could you be so kind as to spare me the headaches you endured and share the SPs you wrote?

    Cheers, and many, many thanks...


    -utp

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Yup No Probs Utex

    1 Mo
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Here's the Files - Just apply them at your Subscriber (except the Cmd_ Scripts)

    amend the Cmd_ scripts to include the subscriber DB name

    Put the Cmd_scripts on the publishers (or subscribers) HDDrive & add the name & path to these when you create your publication.

    Quite straight forward and not really ever failed on me yet.

    Good Luck
    Attached Files Attached Files
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jul 2008
    Posts
    2

    Thanks!

    Worked beautifully! Thanks again!

    -utp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •