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?

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.

  • 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. – BuahahaXD Jun 16 '15 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 Jun 16 '15 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. – BuahahaXD Jun 16 '15 at 10:22
  • 1
    @BuahahaXD create that as an answer and mark it for future reference. Also, welcome to the forum. – Ali Razeghi Oct 31 '17 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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