Supprimer un abonnement par extractionDelete a Pull Subscription

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database (Managed Instance uniquement) nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

Cette rubrique explique comment supprimer un abonnement par extraction de données (pull) dans SQL Server 2017SQL Server 2017 à l'aide de SQL Server Management StudioSQL Server Management Studio, de Transact-SQLTransact-SQLou d'objets RMO (Replication Management Objects).This topic describes how to delete a pull subscription in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO).

Dans cette rubriqueIn This Topic

Utilisation de SQL Server Management StudioUsing SQL Server Management Studio

Supprimez un abonnement par extraction de données (pull) sur le serveur de publication (à partir du dossier Publications locales de SQL Server Management StudioSQL Server Management Studio) ou sur l'Abonné (à partir du dossier Abonnements locaux ).Delete a pull subscription at the Publisher (from the Local Publications folder in SQL Server Management StudioSQL Server Management Studio) or the Subscriber (from the Local Subscriptions folder). La suppression d'un abonnement ne supprime pas les objets ou les données de ce dernier : ils doivent être supprimés manuellement.Deleting a subscription does not remove objects or data from the subscription; they must be removed manually.

Pour supprimer un abonnement extrait sur le serveur de publicationTo delete a pull subscription at the Publisher

  1. Connectez-vous au serveur de publication dans SQL Server Management StudioSQL Server Management Studio, puis développez le nœud du serveur.Connect to the Publisher in SQL Server Management StudioSQL Server Management Studio, and then expand the server node.

  2. Développez le dossier Réplication , puis développez le dossier Publications locales .Expand the Replication folder, and then expand the Local Publications folder.

  3. Développez la publication associée à l'abonnement à supprimer.Expand the publication associated with the subscription you want to delete.

  4. Cliquez avec le bouton droit sur l'abonnement puis cliquez sur Supprimer.Right-click the subscription, and then click Delete.

  5. Dans la boîte de dialogue de confirmation, indiquez si vous souhaitez vous connecter à l'Abonné pour supprimer les informations d'abonnement.In the confirmation dialog box, select whether to connect to the Subscriber to delete subscription information. Si vous désactivez la case à cocher Se connecter à l'Abonné , vous devez vous connecter ultérieurement à l'Abonné pour supprimer les informations.If you clear the Connect to Subscriber check box, you should connect to the Subscriber later to delete the information.

Pour supprimer un abonnement extrait sur l'AbonnéTo delete a pull subscription at the Subscriber

  1. Connectez-vous à l'Abonné dans SQL Server Management StudioSQL Server Management Studio, puis développez le nœud du serveur.Connect to the Subscriber in SQL Server Management StudioSQL Server Management Studio, and then expand the server node.

  2. Développez le dossier Réplication , puis développez le dossier Abonnements locaux .Expand the Replication folder, and then expand the Local Subscriptions folder.

  3. Cliquez avec le bouton droit sur l'abonnement à supprimer, puis cliquez sur Supprimer.Right-click the subscription you want to delete, and then click Delete.

  4. Dans la boîte de dialogue de confirmation, indiquez si vous souhaitez vous connecter au serveur de publication pour supprimer les informations d'abonnement.In the confirmation dialog box, select whether to connect to the Publisher to delete subscription information. Si vous désactivez la case à cocher Se connecter au serveur de publication , vous devez vous connecter ultérieurement au serveur de publication pour supprimer les informations.If you clear the Connect to Publisher check box, you should connect to the Publisher later to delete the information.

Utilisation de Transact-SQLUsing Transact-SQL

Les abonnements par extraction peuvent être supprimés par programme en utilisant des procédures stockées de réplication.Pull subscriptions can be deleted programmatically using replication stored procedures. Les procédures stockées utilisées dépendent du type de publication auquel l'abonnement appartient.The stored procedures used will depend on the type of publication to which the subscription belongs.

Pour supprimer un abonnement par extraction à une publication transactionnelle ou d'instantanéTo delete a pull subscription to a snapshot or transactional publication

  1. Dans la base de données d’abonnement de l’Abonné, exécutez sp_droppullsubscription (Transact-SQL).At the Subscriber on the subscription database, execute sp_droppullsubscription (Transact-SQL). Spécifiez @publication, @publisher et @publisher_db.Specify @publication, @publisher, and @publisher_db.

  2. Dans la base de données de publication du serveur de publication, exécutez sp_dropsubscription (Transact-SQL).At the Publisher on the publication database, execute sp_dropsubscription (Transact-SQL). Spécifiez @publication et @subscriber.Specify @publication and @subscriber. Affectez la valeur all à @article.Specify a value of all for @article. (Facultatif) Si le serveur de distribution n’est pas accessible, affectez la valeur 1 à @ignore_distributor pour supprimer l’abonnement sans supprimer les objets connexes au niveau du serveur de distribution.(Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.

Pour supprimer un abonnement par extraction à une publication de fusionTo delete a pull subscription to a merge publication

  1. Dans la base de données d’abonnement de l’Abonné, exécutez sp_dropmergepullsubscription (Transact-SQL).At the Subscriber on the subscription database, execute sp_dropmergepullsubscription (Transact-SQL). Spécifiez @publication, @publisher et @publisher_db.Specify @publication, @publisher, and @publisher_db.

  2. Dans la base de données de publication du serveur de publication, exécutez sp_dropmergesubscription (Transact-SQL).At the Publisher on the publication database, execute sp_dropmergesubscription (Transact-SQL). Spécifiez @publication, @subscriber et @subscriber_db.Specify @publication, @subscriber, and @subscriber_db. Affectez la valeur pull à @subscription_type.Specify a value of pull for @subscription_type. (Facultatif) Si le serveur de distribution n’est pas accessible, affectez la valeur 1 à @ignore_distributor pour supprimer l’abonnement sans supprimer les objets connexes au niveau du serveur de distribution.(Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.

Exemples (Transact-SQL)Examples (Transact-SQL)

L'exemple suivant supprime un abonnement par extraction à une publication transactionnelle.The following example deletes a pull subscription to a transactional publication. Le premier lot est exécuté sur l'Abonné, le second sur le serveur de publication.The first batch is executed at the Subscriber and the second is executed at the Publisher.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- This is the batch executed at the Subscriber to drop 
-- a pull subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB     AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks2012';

USE [AdventureWorks2012Replica]
EXEC sp_droppullsubscription 
  @publisher = @publisher, 
  @publisher_db = @publicationDB, 
  @publication = @publication;
GO
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);

USE [AdventureWorks2012]
EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = N'all',
  @subscriber = @subscriber;
GO

L'exemple suivant supprime un abonnement par extraction à une publication de fusion.The following example deletes a pull subscription to a merge publication. Le premier lot est exécuté sur l'Abonné, le second sur le serveur de publication.The first batch is executed at the Subscriber and the second is executed at the Publisher.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- This batch is executed at the Subscriber to remove 
-- a merge pull subscription.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publication_db AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @publisher = $(PubServer);
SET @publication_db = N'AdventureWorks2012';

USE [AdventureWorks2012Replica]
EXEC sp_dropmergepullsubscription 
  @publisher = @publisher, 
  @publisher_db = @publication_db, 
  @publication = @publication;
GO
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- This batch is executed at the Publisher to remove 
-- a pull or push subscription to a merge publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorks2012Replica';

USE [AdventureWorks2012]
EXEC sp_dropmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB;
GO

Utilisation d'objets RMO (Replication Management Objects)Using Replication Management Objects (RMO)

Vous pouvez supprimer par programme des abonnements par extraction à l'aide d'objets RMO (Replication Management Objects).You can delete pull subscriptions programmatically by using Replication Management Objects (RMO). Les classes RMO utilisées pour supprimer un abonnement par extraction dépendent du type de publication auquel l'abonnement par extraction est souscrit.The RMO classes that you use to delete a pull subscription depend on the type of publication to which the pull subscription is subscribed.

Pour supprimer un abonnement par extraction à une publication transactionnelle ou d'instantanéTo delete a pull subscription to a snapshot or transactional publication

  1. Créez une connexion à l'Abonné et au serveur de publication à l'aide de la classe ServerConnection .Create connections to both the Subscriber and Publisher by using the ServerConnection Class.

  2. Créez une instance de la classe TransPullSubscription et définissez les propriétés PublicationName, DatabaseName, PublisherNameet PublicationDBName .Create an instance of the TransPullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. Utilisez la connexion à l'Abonné créée à l'étape 1 pour définir la propriété ConnectionContext .Use the Subscriber connection from step 1 to set the ConnectionContext property.

  3. Vérifiez la propriété IsExistingObject pour vous assurer que l'abonnement existe.Check the IsExistingObject property to verify that the subscription exists. Si la valeur de cette propriété est false, les propriétés de l'abonnement ont été définies de manière incorrecte à l'étape 2, ou l'article n'existe pas.If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.

  4. Appelez la méthode Remove .Call the Remove method.

  5. Créez une instance de la classe TransPublication en utilisant la connexion au serveur de publication de l'étape 1.Create an instance of the TransPublication class by using the Publisher connection from step 1. Spécifiez Name, DatabaseName et ConnectionContext.Specify Name, DatabaseName and ConnectionContext.

  6. Appelez la méthode LoadProperties .Call the LoadProperties method. Si cette méthode retourne false, les propriétés spécifiées à l'étape 5 sont incorrectes ou la publication n'existe pas sur le serveur.If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.

  7. Appelez la méthode RemovePullSubscription .Call the RemovePullSubscription method. Spécifiez le nom de l'Abonné et la base de données d'abonnement pour les paramètres subscriber et subscriberDB .Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

Pour supprimer un abonnement par extraction à une publication de fusionTo delete a pull subscription to a merge publication

  1. Créez une connexion à l'Abonné et au serveur de publication à l'aide de la classe ServerConnection .Create connections to both the Subscriber and Publisher by using the ServerConnection Class.

  2. Créez une instance de la classe MergePullSubscription et définissez les propriétés PublicationName, DatabaseName, PublisherNameet PublicationDBName .Create an instance of the MergePullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. Utilisez la connexion créée l'étape 1 pour définir la propriété ConnectionContext .Use the connection from step 1 to set the ConnectionContext property.

  3. Vérifiez la propriété IsExistingObject pour vous assurer que l'abonnement existe.Check the IsExistingObject property to verify that the subscription exists. Si la valeur de cette propriété est false, les propriétés de l'abonnement ont été définies de manière incorrecte à l'étape 2, ou l'article n'existe pas.If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.

  4. Appelez la méthode Remove .Call the Remove method.

  5. Créez une instance de la classe MergePublication en utilisant la connexion au serveur de publication de l'étape 1.Create an instance of the MergePublication class by using the Publisher connection from step 1. Spécifiez Name, DatabaseName et ConnectionContext.Specify Name, DatabaseName and ConnectionContext.

  6. Appelez la méthode LoadProperties .Call the LoadProperties method. Si cette méthode retourne false, les propriétés spécifiées à l'étape 5 sont incorrectes ou la publication n'existe pas sur le serveur.If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.

  7. Appelez la méthode RemovePullSubscription .Call the RemovePullSubscription method. Spécifiez le nom de l'Abonné et la base de données d'abonnement pour les paramètres subscriber et subscriberDB .Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

Exemples (RMO)Examples (RMO)

Cet exemple supprime un abonnement par extraction à une publication transactionnelle et supprime l'inscription de l'abonnement au niveau du serveur de publication.This example deletes a pull subscription to a transactional publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksProductTran";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
TransPublication publication;
TransPullSubscription subscription;

try
{
    // Connect to the Subscriber.
    subscriberConn.Connect();

    // Define the pull subscription.
    subscription = new TransPullSubscription();
    subscription.ConnectionContext = subscriberConn;
    subscription.PublisherName = publisherName;
    subscription.PublicationName = publicationName;
    subscription.PublicationDBName = publicationDbName;
    subscription.DatabaseName = subscriptionDbName;

    // Define the publication.
    publication = new TransPublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = publisherConn;

    // Delete the pull subscription, if it exists.
    if (subscription.IsExistingObject)
    {
        if (publication.LoadProperties())
        {
            // Remove the pull subscription registration at the Publisher.
            publication.RemovePullSubscription(subscriberName, subscriptionDbName);
        }
        else
        {
            // Do something here if the publication does not exist.
            throw new ApplicationException(String.Format(
                "The publication '{0}' does not exist on {1}.",
                publicationName, publisherName));
        }
        // Delete the pull subscription at the Subscriber.
        subscription.Remove();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The subscription to {0} does not exist on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here.
    throw new ApplicationException(String.Format(
        "The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
    subscriberConn.Disconnect();
    publisherConn.Disconnect();
}
' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksProductTran"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As TransPublication
Dim subscription As TransPullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New TransPullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New TransPublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then
       
        If publication.LoadProperties() Then
            ' Remove the pull subscription registration at the Publisher.
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

Cet exemple supprime un abonnement par extraction à une publication de fusion et supprime l'inscription de l'abonnement au niveau du serveur de publication.This example deletes a pull subscription to a merge publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksSalesOrdersMerge";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
MergePublication publication;
MergePullSubscription subscription;

try
{
    // Connect to the Subscriber.
    subscriberConn.Connect();

    // Define the pull subscription.
    subscription = new MergePullSubscription();
    subscription.ConnectionContext = subscriberConn;
    subscription.PublisherName = publisherName;
    subscription.PublicationName = publicationName;
    subscription.PublicationDBName = publicationDbName;
    subscription.DatabaseName = subscriptionDbName;

    // Define the publication.
    publication = new MergePublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = publisherConn;

    // Delete the pull subscription, if it exists.
    if (subscription.IsExistingObject)
    {
        // Delete the pull subscription at the Subscriber.
        subscription.Remove();

        if (publication.LoadProperties())
        {
            publication.RemovePullSubscription(subscriberName, subscriptionDbName);
        }
        else
        {
            // Do something here if the publication does not exist.
            throw new ApplicationException(String.Format(
                "The publication '{0}' does not exist on {1}.",
                publicationName, publisherName));
        }
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The subscription to {0} does not exist on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here.
    throw new ApplicationException(String.Format(
        "The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
    subscriberConn.Disconnect();
    publisherConn.Disconnect();
}
' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergePullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New MergePullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New MergePublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then

        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()

        If publication.LoadProperties() Then
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

Voir aussiSee Also

S’abonner à des publications Subscribe to Publications
Bonnes pratiques en matière de sécurité de la réplicationReplication Security Best Practices