Menghapus Langganan Pull

Artikel
09/25/2024

Berlaku untuk: SQL Server Azure SQL Managed Instance

Topik ini menjelaskan cara menghapus langganan pull di SQL Server dengan menggunakan SQL Server Management Studio, Transact-SQL, atau Replication Management Objects (RMO).

Dalam Topik Ini

Untuk menghapus langganan penarikan, gunakan:

SQL Server Management Studio

T-SQL

Objek Manajemen Replikasi (RMO)

Menggunakan SQL Server Management Studio

Hapus langganan pull di Publisher (dari folder Publikasi Lokal di SQL Server Management Studio) atau Pelanggan (dari folder Langganan Lokal). Menghapus langganan tidak menghapus objek atau data dari langganan; mereka harus dihapus secara manual.

Untuk menghapus langganan pull di Publisher

Sambungkan ke Publisher di SQL Server Management Studio, lalu perluas simpul server.
Perluas folder Replikasi, lalu perluas folder Publikasi Lokal.
Perluas publikasi yang terkait dengan langganan yang ingin Anda hapus.
Klik kanan langganan, lalu klik Hapus.
Dalam kotak dialog konfirmasi, pilih apakah akan tersambung ke Pelanggan untuk menghapus informasi langganan. Jika Anda menghapus kotak centang Sambungkan ke Pelanggan , Anda harus tersambung ke Pelanggan nanti untuk menghapus informasi.

Untuk menghapus langganan penarikan di Pelanggan

Sambungkan ke Pelanggan di SQL Server Management Studio, lalu perluas simpul server.
Perluas folder Replikasi, lalu perluas folder Langganan Lokal.
Klik kanan langganan yang ingin Anda hapus, lalu klik Hapus.
Dalam kotak dialog konfirmasi, pilih apakah akan tersambung ke Publisher untuk menghapus informasi langganan. Jika Anda menghapus kotak centang Sambungkan ke Publisher , Anda harus menyambungkan ke Publisher nanti untuk menghapus informasi.

Menggunakan T-SQL

Langganan penarikan dapat dihapus secara terprogram menggunakan prosedur tersimpan replikasi. Prosedur tersimpan yang digunakan akan bergantung pada jenis publikasi tempat langganan berada.

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Di Pelanggan pada database langganan, jalankan sp_droppullsubscription (Transact-SQL). Tentukan @publication, @publisher, dan @publisher_db.
Di Publisher pada database publikasi, jalankan sp_dropsubscription (Transact-SQL). Tentukan @publication dan @subscriber. Tentukan nilai semua untuk @article. (Opsional) Jika Distributor tidak dapat diakses, tentukan nilai 1 untuk @ignore_distributor menghapus langganan tanpa menghapus objek terkait di Distributor.

Untuk menghapus langganan penarikan ke publikasi gabungan

Di Pelanggan pada database langganan, jalankan sp_dropmergepullsubscription (Transact-SQL). Tentukan @publication, @publisher, dan @publisher_db.
Di Publisher pada database publikasi, jalankan sp_dropmergesubscription (Transact-SQL). Tentukan @publication, @subscriber, dan @subscriber_db. Tentukan nilai tarik untuk @subscription_type. (Opsional) Jika Distributor tidak dapat diakses, tentukan nilai 1 untuk @ignore_distributor menghapus langganan tanpa menghapus objek terkait di Distributor.

Contoh (Transact-SQL)

Contoh berikut menghapus langganan penarikan ke publikasi transaksi. Batch pertama dijalankan di Pelanggan dan yang kedua dijalankan di 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'AdventureWorks2022';

USE [AdventureWorks2022Replica]
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 [AdventureWorks2022]
EXEC sp_dropsubscription 
  @publication = @publication, 
  @article = N'all',
  @subscriber = @subscriber;
GO

Contoh berikut menghapus langganan penarikan ke publikasi gabungan. Batch pertama dijalankan di Pelanggan dan yang kedua dijalankan di 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'AdventureWorks2022';

USE [AdventureWorks2022Replica]
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'AdventureWorks2022Replica';

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

Menggunakan Objek Manajemen Replikasi (RMO)

Anda dapat menghapus langganan penarikan secara terprogram dengan menggunakan Objek Manajemen Replikasi (RMO). Kelas RMO yang Anda gunakan untuk menghapus langganan penarikan bergantung pada jenis publikasi tempat langganan penarikan berlangganan.

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Buat koneksi ke Pelanggan dan Penerbit dengan menggunakan ServerConnection Kelas .
Buat instans TransPullSubscription kelas, dan atur PublicationNameproperti , , DatabaseName PublisherName, dan PublicationDBName . Gunakan koneksi Pelanggan dari langkah 1 untuk mengatur ConnectionContext properti.
IsExistingObject Periksa properti untuk memverifikasi bahwa langganan ada. Jika nilai properti ini salah, properti langganan di langkah 2 didefinisikan dengan tidak benar atau langganan tidak ada.
Panggil Remove metode.
Buat instans TransPublication kelas dengan menggunakan koneksi Publisher dari langkah 1. Tentukan Name, DatabaseName dan ConnectionContext.
Panggil LoadProperties metode. Jika metode ini mengembalikan false, properti yang ditentukan di langkah 5 salah atau publikasi tidak ada di server.
Panggil RemovePullSubscription metode. Tentukan nama Pelanggan dan database langganan untuk parameter pelanggan dan subscriberDB .

Untuk menghapus langganan penarikan ke publikasi gabungan

Buat koneksi ke Pelanggan dan Penerbit dengan menggunakan ServerConnection Kelas .
Buat instans MergePullSubscription kelas, dan atur PublicationNameproperti , , DatabaseName PublisherName, dan PublicationDBName . Gunakan koneksi dari langkah 1 untuk mengatur ConnectionContext properti.
IsExistingObject Periksa properti untuk memverifikasi bahwa langganan ada. Jika nilai properti ini salah, properti langganan di langkah 2 didefinisikan dengan tidak benar atau langganan tidak ada.
Panggil Remove metode.
Buat instans MergePublication kelas dengan menggunakan koneksi Publisher dari langkah 1. Tentukan Name, DatabaseName dan ConnectionContext.
Panggil LoadProperties metode. Jika metode ini mengembalikan false, properti yang ditentukan di langkah 5 salah atau publikasi tidak ada di server.
Panggil RemovePullSubscription metode. Tentukan nama Pelanggan dan database langganan untuk parameter pelanggan dan subscriberDB .

Contoh (RMO)

Contoh ini menghapus langganan penarikan ke publikasi transaksi dan menghapus pendaftaran langganan di Publisher.

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

//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 = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

'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

Contoh ini menghapus langganan penarikan ke publikasi gabungan dan menghapus pendaftaran langganan di Publisher.

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

//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 = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

'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

Bagikan melalui

Menghapus Langganan Pull

Menggunakan SQL Server Management Studio

Untuk menghapus langganan pull di Publisher

Untuk menghapus langganan penarikan di Pelanggan

Menggunakan T-SQL

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Untuk menghapus langganan penarikan ke publikasi gabungan

Contoh (Transact-SQL)

Menggunakan Objek Manajemen Replikasi (RMO)

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Untuk menghapus langganan penarikan ke publikasi gabungan

Contoh (RMO)

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Menghapus Langganan Pull

Menggunakan SQL Server Management Studio

Untuk menghapus langganan pull di Publisher

Untuk menghapus langganan penarikan di Pelanggan

Menggunakan T-SQL

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Untuk menghapus langganan penarikan ke publikasi gabungan

Contoh (Transact-SQL)

Menggunakan Objek Manajemen Replikasi (RMO)

Untuk menghapus langganan penarikan ke rekam jepret atau publikasi transaksi

Untuk menghapus langganan penarikan ke publikasi gabungan

Contoh (RMO)

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: