Script to drop articles from transactional replication

--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec sp
dropsubscription @.publication = ''' + p.publication + ''', @.article = ''' +
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'
--Sorry, had a typo
--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec
sp_dropsubscription @.publication = ''' + p.publication + ''', @.article = '''
+
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'

script to create replication

Is it possible to create distribution, replication... from script? I would
like to do this on a new sql server. Is there a macro to record the step I
do and I can bring that to production server and run it? Thanks.
The easiest method is to create the distributor and publications using EM
then get EM to script it out. Edit the resulting script with the new
computername and run it there.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK. I know how to create distributor and publications using EM but I don't
know how to use EM to script it out. Please advice. Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23EmyenJFHA.3336@.TK2MSFTNGP10.phx.gbl...
> The easiest method is to create the distributor and publications using EM
> then get EM to script it out. Edit the resulting script with the new
> computername and run it there.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can right-click the replication folder (or an individual publication)
and select 'generate sql script'.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Script replication

I have configured transactional replication between two sql servers 2000 by
wizard, i have one publisher and one subscriber (distributor is on same
server as publisher)
I need to script all part of replication but for installing later and when i
try it it's not successed.
Can somebody provide me what really i need to script on punlisher on
subscriber .... ...... ...
Also if on istallation the servers name will be differen from those that i
did script.
Thanks
Message posted via http://www.droptable.com
The easiest thing to do is to get Enterprise manager to generate the script
for you. One thing I've started doing is running some sqldmo each evening to
produce such textfiles automatically. This way I can be sure to have a
backed up version of my replication settings:
Dim objDMO As SQLServer
Dim objReplication As Replication2
Dim fso As FileSystemObject
Dim ts As TextStream
Dim sFilename As String
Set objDMO = New SQLDMO.SQLServer
Set fso = New FileSystemObject
sDirectoryPath = "c:\"
objDMO.LoginSecure = True
objDMO.Connect "crazyfrog"
Set objReplication = objDMO.Replication
sFilename = sDirectoryPath & objDMO.Name & CStr(Year(Now())) &
CStr(Month(Now())) & CStr(Day(Now())) & ".txt"
Set ts = fso.OpenTextFile(sFilename, ForWriting, True)
ts.Write objReplication.Script(SQLDMORepScript_InstallDistr ibutor Or
SQLDMORepScript_InstallPublisher)
ts.Close
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul, however i got problem with this script, first of all where i
should run this script via DTS?
Please can u explain me in more detail how to use this script
TNX
Message posted via http://www.droptable.com
|||You'd be best running this in a programming environment like VB or VB.NET.
You could also run it in an activex script task, but then you'll have to use
a non object-oriented environment and use variants instead. If you just need
a one-off, then EM is the easiest way to go.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, thanks.
iwould like to perform it via DTS, if i only remove all classes name on
variables define it's not help, can u advice me something,
TNX
Message posted via http://www.droptable.com
|||here you go...
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objDMO
Dim objReplication
Dim fso
Dim tf
Set objDMO = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject("Scripting.FileSystemObject")
objDMO.LoginSecure = True
objDMO.Connect "crazyfrog"
Set objReplication = objDMO.Replication
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\testfile.txt", True)
tf.Write objReplication.Script(1048576)
tf.Close
Main = DTSTaskExecResult_Success
End Function
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very usefull, i tried it works perfect.
Message posted via http://www.droptable.com
|||how to perform the script above only for specified publication, e.g. i have
few publishers on one sql server
TNX
Message posted via http://www.droptable.com
|||Have a look here: http://www.replicationanswers.com/script6.asp
You'll have to edit the text in the same way I did to make it use variants.
Rgds,
Paul Ibison, SQL Server MVP
"akej via droptable.com" <forum@.droptable.com> wrote in message
news:5086609F212F0@.droptable.com...
> how to perform the script above only for specified publication, e.g. i
> have
> few publishers on one sql server
> TNX
> --
> Message posted via http://www.droptable.com
|||Thanks in advance
Message posted via http://www.droptable.com

Script code for "Reinitialize All Subscripers" function SQL 2005

Hello All
We are running Merge replication on SQL 2005 with Pocket PC Clients.
My question is, can i script (T-SQL) the same function as if i rightclick on
the Replication Publication, and select "Reinitialize All Subscripers" and
then click on "Create New SnapShot" + "Upload Changes"?
Which system stored procedures should i use? I need to do the same just with
T-SQL.
Thanks
use sp_reinitsubscription
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...
> Hello All
> We are running Merge replication on SQL 2005 with Pocket PC Clients.
> My question is, can i script (T-SQL) the same function as if i rightclick
> on
> the Replication Publication, and select "Reinitialize All Subscripers" and
> then click on "Create New SnapShot" + "Upload Changes"?
> Which system stored procedures should i use? I need to do the same just
> with
> T-SQL.
> Thanks
|||or sp_reinitpullsubscription if you have pull subscriptions
Hilary Cotter wrote:[vbcol=seagreen]
> use sp_reinitsubscription
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
> news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...

SCOPE_IDENTITY on Subscriber

We are doing immediate updating transactional replication between a
single publisher and a single subscriber.
When we try to select SCOPE_IDENTITY or @.@.IDENTITY on the Subscriber
we always receive NULL as the result. When we call them on the
Publisher we receive the correct value.
We have the Identity fields setup on the Publisher but on the
Subscriber they are only basic data types as described in the SQL
Server books online.
So my question is, is this functionality by design? And if so, how
can we obtain the Identity values of newly inserted records on the
subscriber?
Or should we be receiving the Identity values back on the subscriber
but something configured incorrectly.
Thanks
Joseph Palermo
this sounds correct.
The way it works is that an update/insert/delete which occurs on the
subscriber is first applied on the publisher and then the publisher - so the
publisher sets the identity value.
"Joseph Palermo" <joe.groups@.bigrawr.com> wrote in message
news:3d78705f.0405051554.65b69c62@.posting.google.c om...
> We are doing immediate updating transactional replication between a
> single publisher and a single subscriber.
> When we try to select SCOPE_IDENTITY or @.@.IDENTITY on the Subscriber
> we always receive NULL as the result. When we call them on the
> Publisher we receive the correct value.
> We have the Identity fields setup on the Publisher but on the
> Subscriber they are only basic data types as described in the SQL
> Server books online.
> So my question is, is this functionality by design? And if so, how
> can we obtain the Identity values of newly inserted records on the
> subscriber?
> Or should we be receiving the Identity values back on the subscriber
> but something configured incorrectly.
> Thanks
> Joseph Palermo
|||Also, if you want the identity values to be created on the
subscriber, then you can use queued updating subscribers,
in which case the identity ranges will be managed on each
subscriber separately. This would give you access to the
@.@.identity value (or scope_identity()).
HTH,
Paul Ibison