5

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber.

The object I want to replicate is an indexed view. The base tables exist only on the publisher.

The replication fails due to the following error:

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber

It is true that the view doesn't exist in the subscription database. How can i create it without the base tables?

Improve this question

1 Answer 1

Reset to default
1

The clue to this is in the error, which is telling you that it can't create the view on the subscriber, because the subscriber is missing tables and/or columns that the view relies on.

You can't create the view without the base tables, so make sure you replicate the tables and/or columns involved in the view to the subscriber too, and then the view itself can be replicated.

Improve this answer
4
  • Not exactly. I know I can't replicate a view without the base tables unless it is an indexed view which is persistent physically on the disk.
    – Łukasz Kastelik
    Commented Jun 16, 2015 at 9:13
  • The MSDN 'sp_addarticle' document reference doesn't give any indication there are exceptions for object references. msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx, nor does the 'Create Indexed View' article msdn.microsoft.com/en-us/library/ms191432.aspx both of which state the referenced objects must exist in the database the view sits on.
    – VorTechS
    Commented Jun 16, 2015 at 9:45
  • 4
    I managed to replicate the indexed view as a table. It requires a unique clustered index on the view to work.
    – Łukasz Kastelik
    Commented Jun 16, 2015 at 10:22
  • 1
    @BuahahaXD create that as an answer and mark it for future reference. Also, welcome to the forum.
    – Ali Razeghi - AWS
    Commented Oct 31, 2017 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.