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 error checking

I have a situation where It′s necessary to update my customer′s database.
So I′ve built a litle executable that executes an updating script using the
osql tool during the installation process.
In the end, the program checks for any entry in a specific table containing
error lgo information.
To prevent users from using the application if any error occurs, I′ve
written some errors checking in the script, as follows:
DECLARE @.errorsave int
IF NOT EXISTS(SELECT nm_version FROM VerInfo where nm_version = 2)
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
ALTER TABLE .... /* first change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
ALTER TABLE .... /* second change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
-- update version info block
INSERT INTO [dbo].[VerInfo] (nm_version) VALUES (2)
COMMIT TRANSACTION
END
CONTROL_ERROR:
IF @.@.TRANCOUNT>0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO [dbo].[LogInfo]([TX_Description])
SELECT description FROM [master].[dbo].[sysmessages]
WHERE error = @.errorsave
END
GO
The problem is:
There are some specific situations (like drop column) where, if an error
occurs, the script just stops running and does not perform the error log
saving routine, while, in other situations (like drop table) that saves the
error into "LogInfo" table, as expected.check out this link for a good discussion on SQL error handling:
http://www.sommarskog.se/error-handling-I.html