Hi all,
Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!
Hi, Stephanie,
If I understand your question correctly, you wan to create a trigger on subscriber db right after replication agent created replicating table on subscriber database. If so, you can use post snapshot script.
To specify a post snapshot script in Management Studio, right click a publication under replication node, choose Properties context menu to bring up Publication Properties dialog, on the left pane treeview, click snapshot, you should see the input field to specify Pre snapshot script and post snapshot script under "Run Additional scripts" section.
Hope that helps,
Zhiqiang
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thx for reply 
I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
The replication from oracle to MSSQL2005 and the snapshot format is set to 'character' automatically. Is it possible to create trigger in such case? Thanks in advance
|||Stephanie,
You're right, for snapshot using Character mode, the "run additional scripts" option is not available under Publication Properties dialog (disabled in the UI).
In this case, you can add an extra step to create your subscriber table trigger after the distribution or merge agent step in your synchronization job. Following are the steps:
1. Find your sync job under Management Studio --> Object Explorer --> SQL Server Agent --> Jobs --> Your distribution/merge agent job name
2. Right click the sync job, pick Properties from context menu, this will bring up Job Properties dialog
3. On the left side pane under "Select a page", click Steps
4. Insert a new step right after step "Run Agent" to execute your script to create the subscriber table trigger,
Example:
step name: Run Post snapshot script to create subscriber table trigger
type: Operating System (cmdexec)
RunAs: SQL Server Agent Service Account
Command: sqlcmd.exe -E -Ssubscriberinstancename -dsubscriberdbname -Q"create trigger postsnapshottrigger on [subscriberdbname].[dbo].[subscribertable] for update,insert,delete AS select * from sys.databases"
5. Change the Advanced Option for the new step:
On Success action: Quit the job reporting success
On failure action: Go to the next step
6. Change the Advanced Option for step "Run Agent":
On success action: Go to the next step
Leave on failure action unchanged
The original setting for "On success action" is "quite the job reporting success", this will result in the new step you just added being ignored thus we need this change.
7. Reinitialize your subscription and rerun your sync job
I have tested this scenario on a character mode merge publication and it worked just fine (I'm not using Oracle in this test).
If you're not using SQL Server agent to run your jobs, for example, using a batch file to run sync command directly, you can just add the command to create trigger after sync command.
Let me know if this helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||The table is re-created after re-initialize but the trigger didn't be created. I found that the agent job is keeping 'running'...
Just want to confirm that the distribution job with the 1step 'Distribution agent startup message' , right?
Thanks in advance
|||
Hi, stephanie,
I just realized there is one issue with my approach if your distribution agent is set to run continuously (there is a -continuous prameter for the distribution command for step Run Agent), this way your new step will never get a chance to run since the Run Agent step is running continuously.
If your oracle publication can be re-created, you can walk through the New Oracle Publication Wizard and at the page of "Wizard Actions", uncheck "Create the publication" option and check "Generate a script file with steps to create the publication", once the script is created, modify the @.post_snapshot_script pointing to the location of your script to create the trigger, then run this generated script to create your oracle publication.
If you don't want to re-create your oracle publication, you can modify the generated snapshot files to create the trigger after the subscriber table is created (there should be a .sch file for your replicating table), then re-initialize your publication. Note this manual step need to be done each time you re-generated the snapshot files
You can also create a new SQL Server agent job to just create the trigger and have it to run continuously. The logic of your script should be: if subscriber table exist and if the trigger doesn't exist, then create the trigger. This will have some system overhead and your trigger may miss some transactions if updates to the table occurred and before the trigger is created.
Let me know if it helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
I prefer option 1 but i found that there is no '@.post_snapshot_script ' in the scripted generated... should I append this option into the script? Also, there are 3 parts in the script, running
sys.sp_addlogreader_agent, exec sp_addpublication,
exec sp_addpublication_snapshot and sp_addarticle
where should I append it? Thanks in advance.
|||
@.post_snapshot_script is a parameter of sp_addpublication so it should be appended to the sp_addpublication call, see Books Online help topic for sp_addpublication for details of this parameter.
Let me know if you encounter any issues.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks a lot, it perfectly solve our problem!
|||That's great!
By the way, I've submitted a feature request to have new publication wizard support the addition of pre/post snapshot script, this issue may be addressed in a future release of SQL Server so you configure pre/post snapshot script without needing the manual step of modifying and running the replication script as a workaround.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
No comments:
Post a Comment