creating triggers in transactional replication on the subscriber side

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?

Thanks,

Arslan.

are these triggers defined at the publisher, or were you wanting to create new triggers? Assuming the latter, you can put them in a SQL file and reference @.post_snapshot_script in sp_addpublication.|||

If what you would like to do is to replicate triggers defined on the published table, you can take a look at @.schema_option 0x100 in sp_addarticle. If you are using UI, there is also a "copy user triggers" option in article properties dialog.

Peng

Creating Linked Server issues

I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.

I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.

Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:

Create Procedure RunSp_AM_subscription(@.servername Varchar(30), @.IAdbname Varchar(20), @.Attachedbname Varchar(20))
AS

DECLARE @.publicationname Varchar(30)
SET @.publicationname = 'REPUBLISH-'+@.IAdbname+''

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

When I call it locally ot works fine, but not otherwise......... :eek:

When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.

Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent...........

Thanks.

'Walealso how would I even call the proc assuming I'm able to create the linked server.

Creating index on table at subscriber

I have a database for a 3rd party application that I need to report from.
Since I can't do anything to the 3rd party database, I am replicating the
relevant tables to my own database and running the queries there.
One of the tables (Action table) has two fields (Datestamped, Timestamped),
which are critical to some complicated queries I'm running. However neither
of these fields have indexes in the 3rd party database (since they obviously
aren't important to the 3rd party application).
I want to create indexes at the subscriber, but am worried about the
following.
1) Can I do it without breaking the replication or the databases?
2) What will the maintenance be like for this? Will I need to recreate the
indexes every time I need to rebuild the replication?
Thanks in advance
Derek
sp_addscriptexec is only good for UNC type snapshots, ie it will not work
for snapshots delivered by FTP.
The recommendation to deploy this index via a post snapshot command by Paul
is good one.
You could also deliver a custom object script using sp_addarticle, where the
custom script would have the index in it; however you may find that this
will slow down your snapshot delivery.
Here is an example of this:
http://groups.google.com/groups?selm...&output=gplain
The downside of having additional indexes on the subscriber tables is that
it will slow down update activity on your subscriber. In general minimze
your indexes on your subscriber tables. You probably have no option in your
case.
For index maintenance, auto update stats always helps (again this may lead
to some performance degradation with heavy updates), so you might want to
consider nightly dbcc dbreindex or indexdefrag.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
> Derek,
> the easiest way to do this is to have a post-snapshot
> script to run which adds the indexes. If initialization
> has already taken place, you can add the index manually
> or using sp_addscriptexec (transactional and merge)
> without breaking the replication setup.
> HTH,
> Paul Ibison
|||I haven't been able to get this working yet, but haven't yet had the time to
investigate fully and am now knocking off for the day (in Australia).
However I'm having trouble keeping the replication going once I have changed
indexes. Today I have gotten this error:
The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a clustering
key at the same time.
Is this related?
"Hilary Cotter" wrote:

> sp_addscriptexec is only good for UNC type snapshots, ie it will not work
> for snapshots delivered by FTP.
> The recommendation to deploy this index via a post snapshot command by Paul
> is good one.
> You could also deliver a custom object script using sp_addarticle, where the
> custom script would have the index in it; however you may find that this
> will slow down your snapshot delivery.
> Here is an example of this:
> http://groups.google.com/groups?selm...&output=gplain
> The downside of having additional indexes on the subscriber tables is that
> it will slow down update activity on your subscriber. In general minimze
> your indexes on your subscriber tables. You probably have no option in your
> case.
> For index maintenance, auto update stats always helps (again this may lead
> to some performance degradation with heavy updates), so you might want to
> consider nightly dbcc dbreindex or indexdefrag.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
>
>
|||Derek,
I found this whch is relevant for your situation:
http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
From BOL for Update it states "If an update query could alter more than one
row while updating both the clustering key and one or more text, image, or
Unicode columns, the update operation fails and SQL Server returns an error
message."
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That seems to have worked.
What I have ended up doing is instead of deleting all the indexes and
creating my own, I have simply added some that I needed. Not quite as good,
but should do the job.
Derek
"Paul Ibison" wrote:

> Derek,
> I found this whch is relevant for your situation:
> http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
> From BOL for Update it states "If an update query could alter more than one
> row while updating both the clustering key and one or more text, image, or
> Unicode columns, the update operation fails and SQL Server returns an error
> message."
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Creating Custom Scripts Problem

When I ran the sp_script_synctran_commands to generate my custom scripts for my Immediate Updating subscriber on the publisher, it appears the script generated that I need to run on the subscriber is being truncated.
What can I do about that?
in QA go to Tools - Options and click on the results tab. Make sure Maximum
characters per column is 8192.
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uK2JrZx5FHA.3188@.TK2MSFTNGP15.phx.gbl...
When I ran the sp_script_synctran_commands to generate my custom scripts for
my Immediate Updating subscriber on the publisher, it appears the script
generated that I need to run on the subscriber is being truncated.
What can I do about that?
|||Thanx that worked!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23Y2FwNz5FHA.2676@.TK2MSFTNGP15.phx.gbl...
in QA go to Tools - Options and click on the results tab. Make sure Maximum
characters per column is 8192.
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uK2JrZx5FHA.3188@.TK2MSFTNGP15.phx.gbl...
When I ran the sp_script_synctran_commands to generate my custom scripts for
my Immediate Updating subscriber on the publisher, it appears the script
generated that I need to run on the subscriber is being truncated.
What can I do about that?