Menghapus Langganan Pull
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:
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 , , DatabaseNamePublisherName, 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 , , DatabaseNamePublisherName, 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
Lihat Juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk