Replication - Views/SPs etc

Hi,
Hitting the following problem - do not know how to get
around it.
Have the main application running on a central SQL server -
I take replica with me + need the latest update of the
views and stored procedures etc.
First I tried to replicate - but replica falls over - as I
have nested views + creation at the subscriber does not
build the views in a 'smart way' so it 'misses' views and
stops.
Now tried script all the views (probably the correct way
in the begining) - but again same happens - sequence of
creation of views does not work - due to nesting of views.
Only way I can get through this at the moment - is to
compare the views in the main db and the subscriber db and
make these one by one. This does not look like the view I
should do this ..... Also cannot see when the file was
last updated (I am the only one updating)- so not always
sure (monday morning) which is the latest version ......
Any simple way out of this?
Thanks for any help?
Tim
Tim,
the problem you have with the views is one of dependencies. Using sp_depends
on a nesting view you can see which views SQL Server thinks it depends on
and this is not always correct. You can use sp_refreshview to update the
dependencies then replication will add the views in the correct order.
HTH,
Paul Ibison
|||Hi Paul,
Thanks! - this makes sense that this should be part of the
SQL Server tool-box.
Unfortunately I cannot find sp_refreshview to correct the
dependancy list - so that when scripting the views - these
are rebuilt fully - or maybe I just do not understand ....
If you could point me in the right direction!!
Thanks
Tim

>--Original Message--
>Tim,
>the problem you have with the views is one of
dependencies. Using sp_depends
>on a nesting view you can see which views SQL Server
thinks it depends on
>and this is not always correct. You can use
sp_refreshview to update the
>dependencies then replication will add the views in the
correct order.
>HTH,
>Paul Ibison
>
>.
>
|||It should be, but you do have the potential to create circular views and
replication can not sort that out. There are also issues when dealing with
complex dependency chains. The best solution to this is to literally do it
yourself via a pre-script. You are the only one who knows what the precise
creation order needs to be, I would not leave it to an auto generated
process.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Hi Mike,
Maybe this is a better situation in the end - as it forces
a more structured approach to the management of views.
Thanks for the help!
Tim

>--Original Message--
>It should be, but you do have the potential to create
circular views and
>replication can not sort that out. There are also issues
when dealing with
>complex dependency chains. The best solution to this is
to literally do it
>yourself via a pre-script. You are the only one who
knows what the precise
>creation order needs to be, I would not leave it to an
auto generated
>process.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
|||Tim,
it's not actually a replication setup option, but a normal system stored
procedure. Syntax is in the form:
exec sp_refreshview titleview
If you run this on your views in the correct order, then replication will
take care of the rest.
HTH,
Paul Ibison