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:

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

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

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


  • Are you pushing the data from Dev to Prod on a periodic basis or are you periodically copying the data from the tables that comprise the Customers view into the physical table Customers?
    – billinkc
    Aug 22, 2014 at 17:48
  • @billinkc What I currently do is right-click on my production database > go to tasks > import data. Then I select the View on Dev as the source, and the Table on Prod as the destination. If I could simply automate this import task from a script I would but it doesn't seem there's an option to do so. I thought I could do it with replication, but hitting a brick wall here too.
    – Adam
    Aug 22, 2014 at 18:02
  • 1
    As you have discovered, replication is really not the tool you want to be using for this. Can you tell us where you read this recommendation? Aug 22, 2014 at 20:57

1 Answer 1


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.

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.