Replication of dependent objects...

Hi,
I have been trying to create a publication of all my views in my database.
The problem that i have, when the subscriber tries to apply the snapshot is
that some views are dependent to others views... so, the subscriber fails
(because the dependency has not been created yet).
This is happening in snapshot and merge replication... I know i can
workaround it, by just creating multiple publications (one per each level of
dependencies that i have), however, this adds more tasks to my already long
maintance list of TO-DOs...
Anyone has one idea? Should i just learn to live with this problem?
Thanks, Jos.
replication depends on sysdepends which is not always accurate. You can try
to fix sysdepends, or replication your stored procedures/functions/views
using a post snapshot command.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been trying to create a publication of all my views in my database.
> The problem that i have, when the subscriber tries to apply the snapshot
is
> that some views are dependent to others views... so, the subscriber fails
> (because the dependency has not been created yet).
> This is happening in snapshot and merge replication... I know i can
> workaround it, by just creating multiple publications (one per each level
of
> dependencies that i have), however, this adds more tasks to my already
long
> maintance list of TO-DOs...
> Anyone has one idea? Should i just learn to live with this problem?
> Thanks, Jos.
>
|||Jos,
I have had the same issue and as Hilary says, the main option is to fix the
dependencies. Correcting the order even applies to a script run after the
snapshot, in the case of views, which (unlike stored procecures) don't use
deferred name resolution.
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
HTH,
Paul Ibison
|||Thanks for your answer...
However, what is a "post snapshot command"?
Do you mean to not include my sp/functions/views in the original snapshot,
and then running the scripts to create the objects?
Thanks again... Jos
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> replication depends on sysdepends which is not always accurate. You can
try[vbcol=seagreen]
> to fix sysdepends, or replication your stored procedures/functions/views
> using a post snapshot command.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
database.[vbcol=seagreen]
> is
fails[vbcol=seagreen]
level
> of
> long
>
|||I'll sure check out the link you've provided...
Thanks a lot... Jos.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||If your post snapshot script is jumbled you will get warning messages while
running the script referring to the sysdepends problem, but your
distribution agent won't fail. So it will work.
How do you fix the dependencies on the publisher so the snapshot script is
generated correctly in the first place? AFAIK - there is no way to fix
sysdepends.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||"AFAIK - there is no way to fix sysdepends."
Yeah... i have been looking for a way to recreate the dependencies, but the
only one that I have found is to recreate all objects (which is crazy).
It seems so logical that there should be a SP that recreate the dependencies
information for a given object (the same SP that MSSQL should use to create
that information in the first place)... but either it doesn't exist, or
nobody knows it exists ...
Jos.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If your post snapshot script is jumbled you will get warning messages
while[vbcol=seagreen]
> running the script referring to the sysdepends problem, but your
> distribution agent won't fail. So it will work.
> How do you fix the dependencies on the publisher so the snapshot script is
> generated correctly in the first place? AFAIK - there is no way to fix
> sysdepends.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> the
the[vbcol=seagreen]
use
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||evidently this problem plagues other RDBMS's.
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:O2PhDYtYEHA.4068@.TK2MSFTNGP10.phx.gbl...
> "AFAIK - there is no way to fix sysdepends."
> Yeah... i have been looking for a way to recreate the dependencies, but
the
> only one that I have found is to recreate all objects (which is crazy).
> It seems so logical that there should be a SP that recreate the
dependencies
> information for a given object (the same SP that MSSQL should use to
create[vbcol=seagreen]
> that information in the first place)... but either it doesn't exist, or
> nobody knows it exists ...
> Jos.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> while
is[vbcol=seagreen]
fix[vbcol=seagreen]
> the
> use
in
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||right click on your publication, select publication properties, in the
snapshot tab there is an option for pre and post snapshot scripts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for your answer...
> However, what is a "post snapshot command"?
> Do you mean to not include my sp/functions/views in the original snapshot,
> and then running the scripts to create the objects?
> Thanks again... Jos
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> try
> database.
snapshot
> fails
> level
>
|||thanks
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyBcx1vYEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> right click on your publication, select publication properties, in the
> snapshot tab there is an option for pre and post snapshot scripts.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...
snapshot,[vbcol=seagreen]
can[vbcol=seagreen]
procedures/functions/views[vbcol=seagreen]
> snapshot
already
>