Sql-server – Replicating a View to a Table. Issues with schema binding, view indexing

sql serversql-server-2005

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

Best Answer

When you right click on Tasks and use the Import Data/Export Data task, you're building an SSIS package in the background. In the penultimate screen, you're usually greeted with

enter image description here

Instead of Run immediately, check "Save SSIS Package". I generally save to the File System with the Package Protection Level of Do not save sensitive data. Your preference may be different but the important thing is to note where you save this.

You may now use your scheduling tool of choice to invoke the execution of this SSIS package and keep your production environment synchronized with your values in your development environment.

Related Question