Replication of SPs/Views/UDFs - Third party tool?

Hi,
I am using merge replication with anonymous pull subscriber. My problem is
getting a way to consistenly replicate database objects (SPs/Views/UDFs).
Replication kind of support it, but then you are not able to modify
(drop/alter) the objects and it doesn't work anyways because it depends on
the sysdependencies information (which is totally messed up).
So, I cannot use replication for that. I have tested DTS and it fails
because sysdependencies doesn't hold dependencies accurately enough.
I am starting to think on getting a third party tool or program my own...
I know I can include before snapshot scripts to create my objects; the
problem is that my database is highly dynamic, ie: objects are
created/modified/dropped too frequently.
Any advice?
Thanks, Jos Araujo.
use sp_addscriptexec to deploy your changes in your procs, views, and other
objects. This will only work for non ftp deployed subscriptions.
Getting dependencies correct is a problem no matter what RDBMS you use.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using merge replication with anonymous pull subscriber. My problem is
> getting a way to consistenly replicate database objects (SPs/Views/UDFs).
> Replication kind of support it, but then you are not able to modify
> (drop/alter) the objects and it doesn't work anyways because it depends on
> the sysdependencies information (which is totally messed up).
> So, I cannot use replication for that. I have tested DTS and it fails
> because sysdependencies doesn't hold dependencies accurately enough.
> I am starting to think on getting a third party tool or program my own...
> I know I can include before snapshot scripts to create my objects; the
> problem is that my database is highly dynamic, ie: objects are
> created/modified/dropped too frequently.
> Any advice?
> Thanks, Jos Araujo.
>
|||I would do that (use sp_addscriptexec); however there is SQL code being
autocreated by my application.
For instance, there are "rules" that the user defines, that are supposed to
affect the records, those rules are "translated" to stored procedures that
the application creates.
Of course, i could change a lot of things to get it working with
sp_addscriptexec, however, it would really easier to just have an
application to "synchronize" these objects.
Thanks, Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> use sp_addscriptexec to deploy your changes in your procs, views, and
other[vbcol=seagreen]
> objects. This will only work for non ftp deployed subscriptions.
> Getting dependencies correct is a problem no matter what RDBMS you use.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
is[vbcol=seagreen]
(SPs/Views/UDFs).[vbcol=seagreen]
on[vbcol=seagreen]
own...
>
|||This is not an automated approach, but you can put your objects in a
different publication, manually create the snapshot when needed and then
reinit the subscribers to push your changes.
Scott
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
>I would do that (use sp_addscriptexec); however there is SQL code being
> autocreated by my application.
> For instance, there are "rules" that the user defines, that are supposed
> to
> affect the records, those rules are "translated" to stored procedures that
> the application creates.
> Of course, i could change a lot of things to get it working with
> sp_addscriptexec, however, it would really easier to just have an
> application to "synchronize" these objects.
> Thanks, Jos.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> other
> is
> (SPs/Views/UDFs).
> on
> own...
>
|||Thanks...
"Scott Wallace" <scott.wallace@.astyles.com> wrote in message
news:eb6eajlqEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> This is not an automated approach, but you can put your objects in a
> different publication, manually create the snapshot when needed and then
> reinit the subscribers to push your changes.
> Scott
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
problem[vbcol=seagreen]
depends[vbcol=seagreen]
the
>

Replication of SPs

Hello everybody,
Does anybody know why SQl Server replication creates lots of other SPs while
creating real SPs, and all the names are like "sp_MSdel_MyTableNames", that
sp_MSdel_ part is repeated in all of them.
Thanks,
Mathew
These are replication stored procedures which the distribution agent uses to
apply the commands that form the transactions on the subscribers.
The naming convention is used to indicate the article (MyTableNames) which
they are used for, the purpose (upd, del, ins) and ownership (MS).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:62E5A659-2AEC-4D09-98D2-9D6369C728FD@.microsoft.com...
> Hello everybody,
> Does anybody know why SQl Server replication creates lots of other SPs
> while
> creating real SPs, and all the names are like "sp_MSdel_MyTableNames",
> that
> sp_MSdel_ part is repeated in all of them.
> Thanks,
> Mathew
|||By default, transactions are applied to the subscriber by using
parameterized stored procedures. So, you will get 3 procs created for each
table that you are publishing (sp_MSins, sp_MSupd, and sp_MSdel). The
sp_MSins procs are for inserts. The sp_MSupd procs are for updates. The
sp_MSdel procs are for deletes.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:62E5A659-2AEC-4D09-98D2-9D6369C728FD@.microsoft.com...
> Hello everybody,
> Does anybody know why SQl Server replication creates lots of other SPs
> while
> creating real SPs, and all the names are like "sp_MSdel_MyTableNames",
> that
> sp_MSdel_ part is repeated in all of them.
> Thanks,
> Mathew