maintaining triggers on initial snapshot...

I have transactional replication setup with an updatable subscriber. I want
the trigger on the publisher to transfer over to the destination during the
initial snapshot (the drop/create is where I lose the trigger). In the GUI,
properties of the publisher, Articles tab, clicking the elispse then on the
Snapshot tab, there is a checkbox 'User Triggers'. But when I check this and
reinitialize the snapshot and subscriber, the table get dropped and
recreated on the subscriber (as it should) but again WITHOUT the triggers
that are on the publisher.
Any help is greatly appreciated.
Thanks.
Appendage to initial post...
So i discovered the @.schema_option = 0x100 Replicates user triggers on a
table article, if defined.
and when i script out my publisher i see
@.schema_option = 0x000000000000CEF3 and
@.schema_option = 0x000000000000CFF3
(the first one is for a table that does not have the user triggers option
set and the second one does so it makes sense that one has E and the other
F. But this is still not working.
On the publisher I have a trigger:
CREATE TRIGGER tr_CustomerAsset_BreakoutClassifyFieldsAndMatchEqu ipment ON
dbo.CustomerAsset
but after snapshot, this trigger is not at the subscriber.
Thanks everyone!
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>
|||This should work. I would try to re-apply them using a post snapshot script.
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
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:eoOv3bOZHHA.4264@.TK2MSFTNGP05.phx.gbl...
>I have transactional replication setup with an updatable subscriber. I want
>the trigger on the publisher to transfer over to the destination during the
>initial snapshot (the drop/create is where I lose the trigger). In the GUI,
>properties of the publisher, Articles tab, clicking the elispse then on the
>Snapshot tab, there is a checkbox 'User Triggers'. But when I check this
>and reinitialize the snapshot and subscriber, the table get dropped and
>recreated on the subscriber (as it should) but again WITHOUT the triggers
>that are on the publisher.
> Any help is greatly appreciated.
> Thanks.
>

Maintaining Replication

I am trying to maintain replication from an app I have written.
Occasionally, a subscription will have a problem and error out. When the
subscription is restarted, it often will not start resynching until if feels
like it.
What I am looking to do is check the status of all subscriptions and start
synchronization for those that have stopped. Also, I want to do this without
resorting to EM.
Thanks,
Austin
Are you using SQL Server 2000 or SQL Server 2005.
2005 has a rich interface called RMO that you can use to build monitoring
applications, In 2000 you can use DMO to but to a lesser extent in terms of
functionality,
"Austin Summers" wrote:

> I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this without
> resorting to EM.
> Thanks,
> --
> Austin
|||You can have a replication alert fire a job on agent failure, or add a 4th
job step to fire when the agent fails, or even have the third job step loop
back to the first job step on step 3 job failure.
Depending on your app you can build logic in here to decide on how to
respond to different classes of errors. For instance if you are using
ActiveX scripts use the status message and parse it looking for errors.
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
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>I am trying to maintain replication from an app I have written.
> Occasionally, a subscription will have a problem and error out. When the
> subscription is restarted, it often will not start resynching until if
> feels
> like it.
> What I am looking to do is check the status of all subscriptions and start
> synchronization for those that have stopped. Also, I want to do this
> without
> resorting to EM.
> Thanks,
> --
> Austin
|||Hilary,
How do you modify the agent steps at subscription setup? I am using
sp_addmergesubscription to create the subscription. Is there another sp I
can run to make further changes?
Primarily, this will affect MSDE users who disconnect a laptop and then
reconnect needing their data synched both ways. Also, I am finding that if
the publisher cannot find a subscriber, it varies greatly how long it will
keep searching. One test kept trying for a week, another test stopped after
15 minutes. Is there a stored procedure that will startup bi-directional
synchronization?
Thanks,
Austin
"Hilary Cotter" wrote:

> You can have a replication alert fire a job on agent failure, or add a 4th
> job step to fire when the agent fails, or even have the third job step loop
> back to the first job step on step 3 job failure.
> Depending on your app you can build logic in here to decide on how to
> respond to different classes of errors. For instance if you are using
> ActiveX scripts use the status message and parse it looking for errors.
> --
> 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
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:4DD99011-3B98-49DE-BCDE-D818F964D209@.microsoft.com...
>
>
|||You can't, you could manually create a profile and then point it at this
profile. ActiveX controls allow you to do this. Have a look at sp_start_job
to start the replication process.
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
"Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...[vbcol=seagreen]
> Hilary,
> How do you modify the agent steps at subscription setup? I am using
> sp_addmergesubscription to create the subscription. Is there another sp I
> can run to make further changes?
> Primarily, this will affect MSDE users who disconnect a laptop and then
> reconnect needing their data synched both ways. Also, I am finding that
> if
> the publisher cannot find a subscriber, it varies greatly how long it will
> keep searching. One test kept trying for a week, another test stopped
> after
> 15 minutes. Is there a stored procedure that will startup bi-directional
> synchronization?
> Thanks,
> --
> Austin
>
> "Hilary Cotter" wrote:
|||Hilary,
Thanks, sp_start_job works nicely. But, I can't seem to find a status flag
that tells me if a subscription has stopped. Does one exist or should I just
run the restart job whether it is needed or not (and simply trap the error)?
Also, I am getting different behavior from subscriptions setup the same way.
On one, the users takes home their laptop and the agent keeps checking all
night and reconnects when the user returns in the morning. On my other
subscription, it tries to reconnect the default 10 times and then quits. I
can't find where that setting is.
Thanks,
Austin
"Hilary Cotter" wrote:

> You can't, you could manually create a profile and then point it at this
> profile. ActiveX controls allow you to do this. Have a look at sp_start_job
> to start the replication process.
> --
> 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
> "Austin Summers" <AustinSummers@.discussions.microsoft.com> wrote in message
> news:89C88E48-F1C9-4B2F-8867-8803D7CB022D@.microsoft.com...
>
>

Maintaining Replication

Is there any nightly maintenance tasks I can run that will keep my
replication going smooth? I remember reading somewhere about re-indexing
replication tables, but could somebody offer some more insight as to what I
could do to keep everything afloat?
Best Regards,
Brad
There is no real maintenance you can do. You might want to check for
expiring subscriptions, query the history tables for this info.
For merge replication you may wish to reindex contents, tombstone, and
genhistory.
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
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23inwUB2pFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Is there any nightly maintenance tasks I can run that will keep my
> replication going smooth? I remember reading somewhere about re-indexing
> replication tables, but could somebody offer some more insight as to what
I
> could do to keep everything afloat?
> Best Regards,
> Brad
>
|||I have always found replication (snapshot & transactional) to be a very
fussy animal. It is fine if it works however if it comes across
something it doesn't like that takes system resources (i.e. - db
backup, index rebuild, etc) it stops working and sulks like a petulant
child!
To solve most of these replication "tantrums" I have used the Query
timeout setting on the Distribution Agent and set it to 30 mins (or
more) and basically letting the system sort it self out and telling
replication to wait then try again - bit like giving a child a
lollipop ;o)
Obviously everyone has a different replication systems but I have found
that this has worked well for me.
Rgds,
qh

Maintainence of Replication

Hi Dear...

I have some questions regarding replication in SQL Server 2005.

1. Is it neccessary to have same schema on each side i.e on subsicriber and on publisher?

2. Do replication helps me to replicate the changed schema (of published articals) to the subscribers?

3. If a UDT is defined on publisher and one of its dependent is selected for publication do i need to define the UDT on subscriber manually or it will automatically done for me?

4. pls anyone who can give me the detail known issues and their alternative solutions in regard of maintainence of Replication.

Regards

Mubashar Ahmad

1. Subscriber can have a either the full table, a portion of the table or not at all. You cannot replicate to a totally different schema though.

2. Yes it will replicate ALTER statements.

3. I think you have to do that manaully. However you can set a script to run before the snapshot to create it for you automatically.

4. I dont think anyone can give you that.

Martin

|||

For #3 you have options, see @.schema_option for sp_addarticle.

|||thanks both of you... i ll keep this in mind when i come back to replication.. at the moment i am on long vacations...

Maintainence of Replication

Hi Dear...

I have some questions regarding replication in SQL Server 2005.

1. Is it neccessary to have same schema on each side i.e on subsicriber and on publisher?

2. Do replication helps me to replicate the changed schema (of published articals) to the subscribers?

3. If a UDT is defined on publisher and one of its dependent is selected for publication do i need to define the UDT on subscriber manually or it will automatically done for me?

4. pls anyone who can give me the detail known issues and their alternative solutions in regard of maintainence of Replication.

Regards

Mubashar Ahmad

1. Subscriber can have a either the full table, a portion of the table or not at all. You cannot replicate to a totally different schema though.

2. Yes it will replicate ALTER statements.

3. I think you have to do that manaully. However you can set a script to run before the snapshot to create it for you automatically.

4. I dont think anyone can give you that.

Martin

|||

For #3 you have options, see @.schema_option for sp_addarticle.

|||thanks both of you... i ll keep this in mind when i come back to replication.. at the moment i am on long vacations...

Maintainence of Replication

Hi Dear...

I have some questions regarding replication in SQL Server 2005.

1. Is it neccessary to have same schema on each side i.e on subsicriber and on publisher?

2. Do replication helps me to replicate the changed schema (of published articals) to the subscribers?

3. If a UDT is defined on publisher and one of its dependent is selected for publication do i need to define the UDT on subscriber manually or it will automatically done for me?

4. pls anyone who can give me the detail known issues and their alternative solutions in regard of maintainence of Replication.

Regards

Mubashar Ahmad

1. Subscriber can have a either the full table, a portion of the table or not at all. You cannot replicate to a totally different schema though.

2. Yes it will replicate ALTER statements.

3. I think you have to do that manaully. However you can set a script to run before the snapshot to create it for you automatically.

4. I dont think anyone can give you that.

Martin

|||

For #3 you have options, see @.schema_option for sp_addarticle.

|||thanks both of you... i ll keep this in mind when i come back to replication.. at the moment i am on long vacations...

Maintainence of Replication

Hi Dear...

I have some questions regarding replication in SQL Server 2005.

1. Is it neccessary to have same schema on each side i.e on subsicriber and on publisher?

2. Do replication helps me to replicate the changed schema (of published articals) to the subscribers?

3. If a UDT is defined on publisher and one of its dependent is selected for publication do i need to define the UDT on subscriber manually or it will automatically done for me?

4. pls anyone who can give me the detail known issues and their alternative solutions in regard of maintainence of Replication.

Regards

Mubashar Ahmad

1. Subscriber can have a either the full table, a portion of the table or not at all. You cannot replicate to a totally different schema though.

2. Yes it will replicate ALTER statements.

3. I think you have to do that manaully. However you can set a script to run before the snapshot to create it for you automatically.

4. I dont think anyone can give you that.

Martin

|||

For #3 you have options, see @.schema_option for sp_addarticle.

|||thanks both of you... i ll keep this in mind when i come back to replication.. at the moment i am on long vacations...