1

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

  • 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 '14 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 '14 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? – Aaron Bertrand Aug 22 '14 at 20:57
3

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, privacy policy and cookie policy

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