Replication issue with Views

Question

Replication issue with Views

adamcrv

Hall of Fame

Points: 3056
More actions
February 22, 2005 at 6:19 pm

#87537

Hi all,
while testing the load of some scripts on a test database here, i have come across a problem with replication of views
especially where a view is referenced by another view
the error is as such (good for googlers )
"Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber"
I believe it is because view2 relies on view1 but is getting synced first before view1 is created
I have looked at some microsoft kb's but the issues presented are assumed to be fixed in sp1 or sp2
clearly this isnt the case, I thought maybe something to do with the sysdepends table??
anyone run into this issue?
cheers
Adam

------------------------------
Life is far too important to be taken seriously

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 1

If your using transactional I may have a solution. Break up the replication into two subscriptions and force the snapshots to initiate in a particular order. Once the snapshots are applied and the objects are in the subscriber, I believe the transactional replication piece will work fine.

adamcrv Hall of Fame Points: 3056 More actions · Answer 2

hi

Thanks for the reply

im actually having a problem with merge replication

a developer at our office has actually come across the solution, well actually identification of the problem,

running the script to create the view will only show dependencies for the tables, but if the view relies on another view, this dependeancy doesnt actually get recognized, it should but it doesnt. SQL server bug

In order to make the dependancy work you just have to open the script and save it. this will then create an entry in the dependancies selection for the view!!

I would suggest that most people woudnt see this issue and may actually through testing look at the view and may save it, meaning its not a very easy to identify issue IMHO.

sorta manual fix, but still gets the job done!

Thanks for the reply though, will keep it in mind if i do transactional replication in the future

Cheers

Adam

------------------------------
Life is far too important to be taken seriously

aria-231041 SSC Veteran Points: 285 More actions · Answer 3

This is the exact error I am getting, while working with transactional replication. I setup a subcription to just replicate the tables only and that worked fine. I added in the views and am now receiving the

"Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber"

In looking at the views giving me the error, the only thing I noticed consistently is that they are all pulling a text field, but the tables those text fields are from loaded fine.

This is my first time with replication, so any help would be appreciated

Aria

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 4

Can you copy the EXACT create for the view into QA and create it on the subscriber database? Are you sure both subscriptions are going to the same subscriber database?

aria-231041 SSC Veteran Points: 285 More actions · Answer 5

Yes, I am sure. I restored a backup of the "publishing" database to the "subscribing" database to make sure the exact tables/views/etc...exist. I checked the view that is currently erroring to make sure it exists in both databases. Then I created a new subscription (just one). The snapshot went find and then it moved to the distribution step. When it stopped due to the error above on one of the views, I went to the subscribing database to check the view again and it had been removed. So i copied the create statement from the same view on the publisher, ran it in QA for the subscriber and checked to make sure it was there - it was. I started the synch again and it errored on the same view.

adamcrv Hall of Fame Points: 3056 More actions · Answer 6

Follow up to this from me,

we actually dont replicate the views anymore, rather we script the views and run them as a post replication/snapshot script..this works well.

I can elaborate if needs be.

Thanks

------------------------------
Life is far too important to be taken seriously

yitzstokes SSC Veteran Points: 243 More actions · Answer 7

adamcrv -

I would appreciate the elaboration

I am trying to replication both views and stored procedures

adamcrv Hall of Fame Points: 3056 More actions · Answer 8

Essentially you need to script out your views and SP's (take care that if there are dependancies you sort them out)

Save the script into a folder in REPLDATA

open your publication look at the properies and goto the snapshot tab, inser the location of the script eg \\sqlserver\blah\views.sql

and apply it, you can then run the snapshot again and reinit your sbscribers and your views will be into the subscriber dataabses without issue..let me know if you need more info on anything here..

HTH mate

Cheers

------------------------------
Life is far too important to be taken seriously

Ustun Kaya SSC Enthusiast Points: 106 More actions · Answer 9

I had the same issue while trying to implement transactional replication. After a day search, I found that transactional replication was not replicating tables without primary keys, which means that some objects were not transferred. You can't immediately see this on server management studio, because it just shows you the selected articles by default, you need to unselect the checkbox saying "show only checked objects in the list".

May save many people's time...