Replicating Tables and new objects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Replicating Tables and new objects

Author

Topic

ejbatu
Starting Member

21 Posts

Posted - 2010-09-29 : 09:45:09

Hi,

Is is possible to do a one time restore on a destination DB then replicate only tables, and whenever a new objects (New table, new views, new functions and new stored procedure) are created in the publisher DB, automatically, replicate them on the destination DB? If so, how and which option?

Thanks,

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-29 : 09:58:28

not natively. i guess you could write a DDL trigger to add the article and run the snapshot agent.

it will fail if you create a table without a PK

all in all i wouldn't recommend it though. maybe as a nightly job...

ejbatu
Starting Member

21 Posts

Posted - 2010-09-29 : 12:39:57

Thanks Russell,

How would you design the DDL Trigger? Can you give an example.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-29 : 13:34:33

It would look something like this. But please use for reference purposes only. Actually implementing this is not a good idea. Why? Because YOU need to control when the snapshot agent runs. And YOU need to review new tables for suitability for publishing.

Create Trigger myTrigger
on database
for create_table
AS

DECLARE @data xml
DECLARE @table sysname

SET @data = EVENTDATA()

SET @table = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

Exec sp_addArticle @publication = 'publication', @article = @table;
Exec msdb..sp_start_job 'name of snapshot job'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources