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 tasks don't work in Production

Hallo,

In SSIS Version 9.00.2047.00 I have build a few packages using script tasks. These packages work well in development.

When I try to run them Using DTUtil the systems displays in the Package Excecution Progress-window the message: 'Error: The tasks "reading registry" cannot run on this edition of Integration services. It requires a higher level edition.'

Emptying the script task from its variables and script doesn't make a difference. I did not find an explanation in BOL. Concerning script tasks I only find

The Script task uses VSA as its engine for writing and running scripts. To run a script, you must have VSA installed on the computers where the package runs. But since I even get these messages when I try to run on the machine where I build the package,I guess this can't be the problem.

Any ideas?

Kind regards,

Paul

Check out the solutions by Daniel Read @. http://www.developerdotstar.com/community/node/312

Thanks,
Loonysan

|||It looks like you only installed Tools component on Production server. You need to install SSIS component (a separate checkbox during Setup and different licensing requirements) to run packages stand-alone (outside of the designer). Actually, you may not need to Tools component on the production server at all - Tools are needed to design and debug packages, but for execution the SSIS server component should be enough.|||

Hi Michael,

Thanks for your swift reply.

I admit i'm not a SQL server specilist but while installing SQL Server 2005 from CD (Microsoft SQL Server 2005 Enterprise Edition December 2005) or DVD the option 'Integration services' is disabled. So I can not single it out for installation. . Is there still an other installation dvd I need or can I download the SSIS server component from somewhere? I did not seem to find it in the download center

With kind regards,

Paul

|||What is the OS on the production machine? Server components in Enterprise Edition can only be installed on Server operating system, not on Windows XP or Windows 2000 workstation. For production server, use server OS like Windows 2003 Server.

Script a database fail

Dear experts,

I've a database in sql2005 and now I want to build a same one on another machine. I've searched thru google and told that i can use backup& restore or using script. I've tested using backup/restore and it works great now i want to give a test to script. I've script the database successfully to a sql script file, however, when i run it against the new database server, the new database was not created as expected. Could anybody explain why? Thanks in advance

Are you getting any error messages?

|||

How did you create script? Have you tried to create it with Database Publishing Wizard:http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
With this peace of software you can create script directly from Visual Studio and you can choose do you want only schema of your database, or do you want schema and data.