Skip to main content
added 387 characters in body; edited title
Source Link
Adam
  • 11
  • 1
  • 3

Replicating a View to a Table - unable to change the article type of my View. Issues with schema binding, view indexing

I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server. Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS. I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end. I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property. When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

It seems that type is a valid propertySo I go to index the view, but my views are schema-only articlesI can't because it's not schemabound. How can

So then I fixgo to schema bind the view but I can't because the view is attached to a remote database.

Cannot schema bind view 'dbo.MyView' because name 'RemoteDb.dbo.MyTable' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

And it appears it is impossible to set schema binding on a view that queries a remote database. What the heck are my options here?

Thanks

Replicating a View to a Table - unable to change the article type of my View

I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server. Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS. I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end. I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property. When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

It seems that type is a valid property, but my views are schema-only articles. How can I fix that?

Thanks

Replicating a View to a Table. Issues with schema binding, view indexing

I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server. Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS. I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end. I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property. When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

So I go to index the view, but I can't because it's not schemabound.

So then I go to schema bind the view but I can't because the view is attached to a remote database.

Cannot schema bind view 'dbo.MyView' because name 'RemoteDb.dbo.MyTable' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

And it appears it is impossible to set schema binding on a view that queries a remote database. What the heck are my options here?

Thanks

Source Link
Adam
  • 11
  • 1
  • 3

Replicating a View to a Table - unable to change the article type of my View

I am rather new to this.

On my development server, I have 3 views that get live data from another database on a private internal server. Sample structure:

DevDB
   --> Tables
   --> Views
      --> dbo.Customers
   --> Stored Procedures

On my production server, those same views are set up as tables

ProdDB
   --> Tables
      --> dbo.Customers
   --> Views
   --> Stored Procedures

Up until now, I have just been periodically updating the Customers table on Prod using an import task in SSMS. I've been wanting to automate this process, and read that you should use Replication for this.

I selected the appropriate views on the publisher end. I understand that I can have views replicate as tables by setting the article's type to indexed view logbased.

See "Publishing Views" at this link: http://msdn.microsoft.com/en-us/library/ms152559%28SQL.90%29.aspx

However, I am having trouble setting the type property. When I attempt to set it, I get an error.

exec sp_changearticle @publication = N'MyPub',
    @article = N'MyView',
    @property = N'type',
    @value = N'indexed view logbased',
    @force_invalidate_snapshot = 1

I get this error:

'type' is not a valid property for a schema only article'.

It seems that type is a valid property, but my views are schema-only articles. How can I fix that?

Thanks