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
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?