On the Fly Schema Changes in Merge Replication

I have always assumed that I must completely disable replication to make any
sort of change to published articles, but these article seem to say
otherwise:
http://msdn.microsoft.com/library/de...limpl_22pf.asp
http://msdn.microsoft.com/library/de...lctem_6l61.asp
I would like to add a column to an existing published article (during low
activity hours), but it seems to me a new snapshot would need to be created
(plus re-initialization of subscribers). My subscribers can be unavailable
for days (off-line but updating and inserting all the while). I can
understand that subscriber updates to the article may be lost at
re-initialization, but are other tables adversly effected?
I'm confused - why do you think that a snapshot would be necessary? The
article may have misled you - if the column already exists and has data in
it, then you're right, but if it an entirely new column, sp_repladdcolumn
will do the trick without new snapshots, reinitialization etc.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
I suppose my assumptions about a snapshot were incorrect. I thought that
any schema change would require a new snapshot. So I will not need to worry
about my off-line subscribers losing data.
Where can I find more information about what information is kept in a
snapshot?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehwOt0sgFHA.3000@.tk2msftngp13.phx.gbl...
> I'm confused - why do you think that a snapshot would be necessary? The
> article may have misled you - if the column already exists and has data in
> it, then you're right, but if it an entirely new column, sp_repladdcolumn
> will do the trick without new snapshots, reinitialization etc.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Run the snapshot agent then have a look in the distribution working folder -
you'll see a bunch of text files to create teh schema, bcp data files, index
creation scripts etc. The exact files created depend mostly on the article
properties selected.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the help!

ON SCHEMA :: schema_name

Hi,

Could you explain what a scope qualifier is (the two "::" colons) and what it is used for?

Thanks.It is used to scope permissions to a particular object: I.e. GRANT ALTER ON SCHEMA::mySchema to nielsb gives the user nielsb rights to create tables etc in that schema. This is not limited to only schemas, but are used for almost all database objects.
Niels|||Thank you Niels!|||

SQL 2K5

This :: syntax is covered in the BOL topic for the GRANT, DENY, and REVOKE statements.

To elaborate on Niels's post, you need ALTER permission on a schema and CREATE TABLE permission to create a table.

Also - note that ALTER permission on a container like a schema or database gives the grantee the ability to DROP contained objects as well. So if I give you ALTER permission on schema S, then you can DROP tables (or procs or views or ...) in S.

sql