Create or Change Merge Articles

We are setting up a merge replication with laptops as subscribers. I read
an article about altering columns in a table but want to verify the correct
sequence of events to add or change articles after Publisher is created.
For a new table:
CREATE TABLE command
sp_addarticle
For a revised table, view or stored proc:
sp_droparticle
DROP ...
CREATE ...
sp_addarticle
For removing table, view or stored proc:
sp_droparticle
DROP ...
Also, I'm not real clear on where or when the snapshot needs to be
re-created. Does this have to be done before the new, changed or deleted
articles will get to the subscribers? Thank you.
David
David,
please take a look at this : http://www.replicationanswers.com/AddColumn.asp
When adding a table to a publication, you'll need to run the snapshot agent.
For TR this'll create the new article only in the snapshot. For merge
this'll create the whole snapshot. In either case only the new table will be
synchronised.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I read this, but no mention of what to do with a VIEW. There is a
sp_repladdcolumn but I think that only applies to TABLE objects. If that is
true, then do I have to drop the article, etc. as I mentioned? Thanks.
p.s. I only care about MERGE replication.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
> David,
> please take a look at this :
> http://www.replicationanswers.com/AddColumn.asp
> When adding a table to a publication, you'll need to run the snapshot
> agent. For TR this'll create the new article only in the snapshot. For
> merge this'll create the whole snapshot. In either case only the new table
> will be synchronised.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I don't replicate any other objects other than tables (and occasionally the
execution of stored procedures).
Normally schema changes on the other objects aren't replicated even when you
do a sp_refreshpublication. For UNC subscribers I use sp_addscriptexec, for
FTP subscriber I use ADO.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
> OK, I read this, but no mention of what to do with a VIEW. There is a
> sp_repladdcolumn but I think that only applies to TABLE objects. If that
> is true, then do I have to drop the article, etc. as I mentioned? Thanks.
> p.s. I only care about MERGE replication.
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
>
|||David,
there isn't really a distinction between tables and views - they're all
articles.
Actually, like Hilary, I don't have views mixed with other articles in a
publication. I sometimes use sp_addscriptexec but usually isolate the views
into another publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||damn you're good
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O0%23EGdqEGHA.1028@.TK2MSFTNGP11.phx.gbl...
> David,
> there isn't really a distinction between tables and views - they're all
> articles.
> Actually, like Hilary, I don't have views mixed with other articles in a
> publication. I sometimes use sp_addscriptexec but usually isolate the
> views into another publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I had a good mentor
|||
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBsuvYtEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>I had a good mentor
>
|||I am new at replication so I don't understand this. Doesn't the subscriber
(in this case the laptops) need to have the views and stored procs in order
to run the applications that I have with links to the views and using the
stored procedures? Or are they always part of the snapshot (or ?) that
the subscriber gets normally? I will only make changes to the views and
stored procedures at the Publisher, not at the Subscribers. Thank you for
any help on this. Let me know if my assumption was wrong.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I don't replicate any other objects other than tables (and occasionally the
>execution of stored procedures).
> Normally schema changes on the other objects aren't replicated even when
> you do a sp_refreshpublication. For UNC subscribers I use
> sp_addscriptexec, for FTP subscriber I use ADO.
> --
> 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
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
>
|||I am a little confused by your question. Merge replication will put in place
on the subscriber the procs and views it needs, sync or no sync
subscription.
If your application which will be accessing the subscriber database needs
views, procs, etc you are responsible for putting these in place. You can
use replication, a post snapshot command, sp_addscriptexec (for unc deployed
subscribers only), or another manual method. My least favorite way to do
this is using replication because of dependency issues and having the
publication pick up changes even after you refresh the publication.
So if you replicate a proc's schema, and you reinitialize the publication,
it won't replicate the changes to the proc- at least this was the case last
time I checked.
If I have misread your question please post back.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OLUBtp6EGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am new at replication so I don't understand this. Doesn't the subscriber
>(in this case the laptops) need to have the views and stored procs in order
>to run the applications that I have with links to the views and using the
>stored procedures? Or are they always part of the snapshot (or ?) that
>the subscriber gets normally? I will only make changes to the views and
>stored procedures at the Publisher, not at the Subscribers. Thank you for
>any help on this. Let me know if my assumption was wrong.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>

create database command in trigger

Hi,

As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.

I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.

I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger

CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'

Thanks,

IanPlease explain something more about your process|||Hi,

The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.

I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger

declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt

I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.

Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.

I hope this is some use.

Ian