Replicate a view?

[huskerdoug]

Here is my scenario:

I have a view on one server (A). I would like to replicate the view to another server (B) that is used by our intranet.



Let’s say this view has the following fields: patientNumber, lastName, firstName



The table has the same three fields but also has: id (primary key), update (nullable), date (nullable)



I haven’t been successful replicating a view, so I set up DTS to create a table on the same server as the view (A), and then I would simply replicate to the same table on server (B). But, the problem I’m running into is DTS will not insert the data into the table because the id column in the table will not accept null values.



The purpose of the app is to provide read only data (the view info) from our billing system that will feed a table (as above) that can be updated from an active server page.

 

[SQLSister]

I see several problems with your scenario. If you link the servers, you can run the update through triggers. This way, every time a record is changed, it will reflect in the other table rather than scheduling a DTS package to run at specified intervals. Look up linking servers and triggers in Book online.

You can set up a DTS package to let the updated table insert it's own identity field, but I don't really know that a periodic DTS is what you need in the described scenario.

Your second table has a problem that you really need to fix before you go much farther. All the addtional fields you have in this table are reserved words in SQL. You will run into programming problems unless you change them.

Not sure what you mean by saying you want to populate the table from server a and then let users change it on server b. You need to think about the design here, are users going to delete or change records here but not in the base table? This could lead to data that is unuseable because things don't match or replacing changed data with old data when the base tables change.

 

[huskerdoug]

You are very much correct about the reserved words. They are not true field names, I just grabbed some names to illustrate my point.

Your last statement, I only wish to update, via web page, two columns. Neither of these columns are contained in the view.

Thank you for your help!