proper way to drop publication?

Hi There

According to BOL, the correct procedure for deleting a publication are as follows:
These must be run on the publication database.

Execute sp_dropsubscription to delete all snapshot subscriptions.

Execute sp_droppublication to delete the publication and all of its articles.

My only issue is after step 1 the subscription still exists at the subscriber and it marked as failed (does not exist), i always have to go delete the subscription afterwards at the subscriber. AM i missing something? WHy does sp_dropsubscription not do this? Or is there another sp i should be running first?

Thanx

Hi Dietz,

The actual step to cleanup subscription depending on the subscription is push or pull. Basically, for push, you need to call sp_dropsubscription at publisher followed by sp_subscription_cleanup at subscriber. For Pull, you need to call sp_droppullsubscription at subscriber followed by sp_dropsubscription at pubisher.

Please refer to BOL topic "How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".

Thanks,|||Great thanx Peng, i wonder why they do not mention any of those sp's under the How to Delete Publications and Articles (Transact-SQL) BOL topic.
Also i cannot find

"How to: Delete a Pull Subscription (Replication Transact-SQL Programming)" and "How to: Delete a Push Subscription (Replication Transact-SQL Programming)".in BOL only TSQL references not Replication Transact sql programming.

I am using SS2000 not SS2005?

Thanx again|||Sorry, dietz. I made a bad assumption to think your system is SQL 2005. My previous post really means for SQL 2005.

In SQL 2000 BOL, there is similar topic on how to drop subscription, depending on pull/push of the subscription. You probably already see it in "How To"->"Transact SQL" section.

Peng
sql

Programmatically deleting Indexes

How do you programmatically delete ALL indexes for all tables in SQL Server?
Is this possible? I was thinking about using SQL DMO to do this.
adv-thanks-anceYou can iterate through the Indexes collection of a table and then use the
Remove method to drop the index.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||ALL of the indexes for the ENTIRE SQL Server (including all databases) ?
Might want to test that out prior to implementing it on a production
machine.
Check out the Remove method of the Indexes collection in SQL-DMO.
T-SQL - could try something like this for one db (not fully tested):
SELECT 'DROP INDEX [' + OBJECT_NAME(ID) + '.' + NAME + ']'
FROM SYSINDEXES
WHERE NAME NOT LIKE 'SYS%' AND NAME NOT LIKE 'DT%' AND NAME NOT LIKE '_WA%'
AND OBJECT_NAME(ID) NOT LIKE 'SYS%'
AND OBJECT_NAME(ID) NOT LIKE 'MS%' AND OBJECT_NAME(ID) NOT LIKE 'DT%'
AND INDID >= 1 AND INDID < 255
ORDER BY OBJECT_NAME(ID), NAME
Then copy/paste/run the resultset in a query window in QA. Since an index
associated with a constraint cannot be dropped outside of dropping the
constraint, you might have to remove some of the entries listed and drop the
constraint manually. But this might help get you started.
HTH
Jerry
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||The thing about dropping and re-creating indexes is that it involves
interrupting access to the database tables. Perhaps what you are wanting to
do is periodically defragment indexes. DBCC INDEXDEFRAG does not interrupt
queries and is typically faster than REINDEX.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||Take a look at this example:
http://milambda.blogspot.com/2005/0...in-current.html
...and tailor it to suit your needs.
ML|||If you use this be sure you increase or have ample room in the transaction
log for that database.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:4F244CDB-0144-4EB9-ADAD-7F36D423CB42@.microsoft.com...
> Take a look at this example:
> [url]http://milambda.blogspot.com/2005/07/defragment-all-indexes-in-current.html[/url
]
> ...and tailor it to suit your needs.
>
> ML|||Unlike REINDEX, which rebuilds the entire index in one large transaction,
INDEXDEFRAG only re-aranges the physical position of specific index pages as
needed, and each unit of work is in a seperate transaction, so it would
typically use less transaction logging.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EFAA[
/url]
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23YMV3N6wFHA.2540@.TK2MSFTNGP09.phx.gbl...
> If you use this be sure you increase or have ample room in the transaction
> log for that database.
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:4F244CDB-0144-4EB9-ADAD-7F36D423CB42@.microsoft.com...
>

Programmatically Deleting an Instance

I'm trying to just get a basic NS app up and running. While I can get through the samples just fine, it seems there are no samples that programmatically manage Instances/Applications, etc. I've googled around and not found anything either. Essentially it looks to me that everyone uses the IDF/ADF files. The SDK has some very sparse documentation on it, so I've started there and tried to get things working.

I have code to create the instance/application programmatically, but when I want to drop the whole thing and create it again, upon calling instance.Create() I get an exception (actually this is what the innerException says) saying:

Notification Services failed to read the NSVersionInfo table.

I’m following the example in the SDK (doing Disable/UnregisterLocal/Drop). Interestingly when I do the Disable/Unregister/Drop in SQL Management Studio it works fine. It's also interesting to note that when I ignore the exception it comes up once more, but after that the instance and associated databases seem to be created.

Here’s the short version of my code (getFreshInstance() is the entry method):

private void configureInstance(nmo.Instance instance)
{
prototypeApplication = new nmo.Application(instance, applicationName);

AddDeliveryChannels(instance);
configureApp(prototypeApplication);
instance.Applications.Add(prototypeApplication);
}

private void configureApp(nmo.Application app)
{
AddGenerator(app);
AddDistributor(app);
}

private void checkAndDrop(string name)
{
if (notificationServices.Instances.Contains(name))
{
nmo.Instance instance = new nmo.Instance(notificationServices, name);
instance.Refresh();

instance.Disable();
instance.UnregisterLocal();
instance.Drop();
}
}

private nmo.Instance getFreshInstance()
{
nmo.Instance instance = new nmo.Instance(notificationServices, instanceName);

checkAndDrop(instanceName);

configureInstance(instance);

instance.Create();
instance.RegisterLocal();
instance.Enable();

return instance;
}

Anyone know what I'm doing wrong?

Ok, seems you've asked a question not many folks in the wild know the answer to :-).

I've tracked down what is probably the answer care of Shyam Pather:

He’s creating a new in-memory object to reflect his instance, like this:

Nmo.Instance instance = new Nmo.Instance(…, “InstanceName”);

Instead, he should do something like:

Smo.Server s = new Smo.Server(…);

Nmo.Instance instance = s.NotificationServices.Instances[“InstanceName”];

That will get him an instance object that’s already populated with the metadata from the database and actually represents the existing instance.

The FlightNMO sample that comes with SQL Server 2005 should also provide you a working example of how to make the API calls.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I just gave that snippet a try and unfortunately, I ended up with the same error when I attempt to enable. The need for this functionality in my project has been removed by a change in design, so I'm no longer really worried about this, but I would still be curious to know the cause of this or hear confirmation of someone else running into this.

Now that you mention it, I vaguely remember seeing the FlightNMO sample when I first looked at NS, but had since forgotten it. Thanks! I'm sure that'll prove quite useful later. As far as this particular functionality though, I'm afraid FlightNMO doesn't ever delete the instance, only create.

Thanks for your help,

-Francis