Microsoft.SQLServer.Replication.Windows.Discovery SQL Queries (v7.0.28.0)

Tyson Paul0 commentsManagement PacksSQLQUERYReplicationSQL



Now that the SQL management packs rely heavily on DLLs it’s not easy to determine what the workflows are doing or how they do it. These are the queries used for many of the workflows within this management pack.

-- Microsoft.SQLServer.Replication.Windows.Discovery (v7.0.28.0)

--DistributorQuery
DECLARE @tsql NVARCHAR(MAX) = N'
declare @DISTRIBUTOR_INFO TABLE
(
	name_p sysname
	,distribution_db sysname
	,security_mode INT
	,login nvarchar(255)
	,password nvarchar(255)
	,active INT
	,working_directory nvarchar(MAX)
	,trusted INT
	,thirdparty_flag INT
	,publisher_type nvarchar(MAX)
	,publisher_data_source nvarchar(MAX)
'
IF (@@MICROSOFTVERSION / 0x01000000) >= 15  -- SQL Server 2019 and above
	SET @tsql = @tsql + ',storage_connection_string nvarchar(MAX)'

SET @tsql = @tsql + N'); 
INSERT INTO @DISTRIBUTOR_INFO EXEC sp_helpdistpublisher;

SELECT [NAME], (SELECT di.name_p + '', '' FROM @DISTRIBUTOR_INFO di WHERE distr_dbs.[name] = di.distribution_db FOR XML PATH('''')) [INSTANCES]
FROM sys.databases distr_dbs
WHERE distr_dbs.is_distributor = 1
GROUP BY NAME'

EXEC(@tsql)



--SubscriptionQuery
DECLARE @Databases TABLE (
	DatabaseName sysname
)

DECLARE @UnfilteredDatabases TABLE (
	DatabaseName sysname
)

DECLARE @IsSnapshotGlobal bit 
SET @IsSnapshotGlobal = 1;
DECLARE @ExecError int
declare @sql nvarchar(max),  @dbname sysname, @quotedName nvarchar(max);

INSERT INTO @UnfilteredDatabases SELECT name FROM sys.databases  where name not in ('master','model','msdb','tempdb') and is_distributor <> 1

Declare temp Cursor LOCAL For 
  Select db.DatabaseName 
	From @UnfilteredDatabases db;
Open temp;

Fetch next From temp into @dbname;
		
While @@Fetch_Status=0 Begin
	set @quotedName = QUOTENAME(@dbname ,'"')
	set @sql = N'USE '+ @quotedName;
	BEGIN TRY
		EXEC (@sql);
		insert into @Databases values (@dbname);
	END TRY
	BEGIN CATCH
	END CATCH;
	Fetch next From temp into @dbname;
End

Close temp;
Deallocate temp;

declare @query nVARCHAR(MAX);

set @query = 'DECLARE @IsSnapshot bit; '+
'set @IsSnapshot = ' + cast (@IsSnapshotGlobal as nvarchar) + '; '

SET @query = @query + N'DECLARE @SUBSCRIPTION_INFO TABLE (
				publisher			sysname not null,
				publisher_db		sysname not null,
				publication			sysname null,
				replication_type	int not NULL,
				subscription_type	int not NULL,
				last_updated		datetime null,
				subscriber_db		sysname not null,
				update_mode			smallint null,
				last_sync_status	int null,
				last_sync_summary	sysname null,
				last_sync_time		datetime null
			   ) ;
				
			  DECLARE @SUBSCRIPTION_PULL_INFO_EXTEND TABLE (
				"publisher" nvarchar(max),
				"publisher database" nvarchar(max),
				"publication" nvarchar(max),
				"independent_agent" bit,
				"subscription type" int,
				"distribution agent" nvarchar(max),
				"publication description" nvarchar(255),
				"last updating time" datetime,
				"subscription name" nvarchar(386),
				"last transaction timestamp" varbinary(16),
				"update mode" tinyint,
				"distribution agent job_id" int,
				"enabled_for_synmgr" int,
				"subscription guid" binary(16),
				"subid" binary(16),
				"immediate_sync" bit,
				"publisher login" nvarchar(max),
				"publisher password" nvarchar(524),
				"publisher_security_mode" int,
				"distributor" nvarchar(max),
				"distributor_login" nvarchar(max),
				"distributor_password" nvarchar(524),
				"distributor_security_mode" int,
				"ftp_address" nvarchar(max),
				"ftp_port" int,
				"ftp_login" nvarchar(max),
				"ftp_password" nvarchar(524),
				"alt_snapshot_folder" nvarchar(255),
				"working_directory" nvarchar(255),
				"use_ftp" bit,
				"publication_type" int,
				"dts_package_name" nvarchar(max),
				"dts_package_location" int,
				"offload_agent" bit,
				"offload_server" nvarchar(max),
				"last_sync_status" int,
				"last_sync_summary" nvarchar(max),
				"last_sync_time" datetime,
				"job_login" nvarchar(max),
				"job_password" nvarchar(max)
			   );


				 DECLARE @SUBSCRIPTION_MERGE_PULL_INFO_EXTEND TABLE (
						"subscription_name" nvarchar(1000),
						"publication" nvarchar(max),
						"publisher" nvarchar(max),
						"publisher_db" nvarchar(max),
						"subscriber" nvarchar(max),
						"subscription_db" nvarchar(max),
						"status" int,
						"subscriber_type" int,
						"subscription_type" int,
						"priority" float(8),
						"sync_type" tinyint,
						"description" nvarchar(255),
						"merge_jobid" binary(16),
						"enabled_for_syncmgr" int,
						"last_updated" nvarchar(26),
						"publisher_login" nvarchar(max),
						"publisher_password" nvarchar(max),
						"publisher_security_mode" int,
						"distributor" nvarchar(max),
						"distributor_login" nvarchar(max),
						"distributor_password" nvarchar(max),
						"distributor_security_mode" int,
						"ftp_address" nvarchar(max),
						"ftp_port" int,
						"ftp_login" nvarchar(max),
						"ftp_password" nvarchar(max),
						"alt_snapshot_folder" nvarchar(255),
						"working_directory" nvarchar(255),
						"use_ftp" bit,
						"offload_agent" bit,
						"offload_server" nvarchar(max),
						"use_interactive_resolver" int,
						"subid" uniqueidentifier,
						"dynamic_snapshot_location" nvarchar(255),
						"last_sync_status" int,
						"last_sync_summary" nvarchar(max),
						"use_web_sync" bit,
						"internet_url" nvarchar(260),
						"internet_login" nvarchar(128),
						"internet_password" nvarchar(524),
						"internet_security_mode" int,
						"internet_timeout" int,
						"hostname" nvarchar(128),
						"job_login" nvarchar(512),
						"job_password" nvarchar(max)
					 );

				DECLARE @AGGREGATED_SUBSCRIPTIONS_INFO TABLE (
					SubscriptionName  nVARCHAR(MAX),
					Type nVARCHAR(MAX),
					publisher nVARCHAR(MAX),
					distributor nVARCHAR(MAX),
					PublicationDatabase nVARCHAR(MAX),
					PublicationName nVARCHAR(MAX),
					SnapshotLocation nVARCHAR(MAX),
					SnapshotFolder nVARCHAR(MAX),
					--Subscriber nVARCHAR(MAX),
					SubscriptionDatabase nVARCHAR(MAX),
					SubscriptionDescription nVARCHAR(MAX),
					UpdatableSubscription BIT,
					UseWindowsSynchronizationManager  BIT,
					ReplicationType nVARCHAR(MAX),
					IsSnapShotDiscovery bit
				);

				DECLARE @IS_SUBSCRIPTION_EXIST BIT;
				DECLARE @subscription_type INT;
				DECLARE @update_mode INT;
				DECLARE @publisher NVARCHAR(MAX);
				DECLARE @publisher_db NVARCHAR(MAX);
				DECLARE @publication NVARCHAR(MAX);
				DECLARE @subscription_db NVARCHAR(MAX);
				DECLARE @replication_type INT;
				SET @IS_SUBSCRIPTION_EXIST = 0;


				';


select  @query = @query + 'BEGIN TRY '+
							'USE ' + QUOTENAME(DatabaseName, '"') + '; ' + N'
						   INSERT INTO  @SUBSCRIPTION_INFO exec sp_MSenumsubscriptions @subscription_type = ''both''
						   SELECT @IS_SUBSCRIPTION_EXIST = (CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END) FROM @SUBSCRIPTION_INFO
						   IF( @IS_SUBSCRIPTION_EXIST = 1 )
						   BEGIN
							   DECLARE SUBSCRIPTION_CURSOR CURSOR FOR  SELECT publisher, publisher_db, publication,  subscriber_db, subscription_type, update_mode, replication_type  FROM @SUBSCRIPTION_INFO 
							   OPEN SUBSCRIPTION_CURSOR;
							   FETCH NEXT FROM SUBSCRIPTION_CURSOR INTO @publisher, @publisher_db , @publication , @subscription_db , @subscription_type , @update_mode, @replication_type;
							   WHILE @@FETCH_STATUS = 0
							   BEGIN
									IF @subscription_type = 1
										IF @replication_type <> 2
											   BEGIN
											   	   INSERT INTO  @SUBSCRIPTION_PULL_INFO_EXTEND exec sp_helppullsubscription @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication;
											   	   INSERT INTO @AGGREGATED_SUBSCRIPTIONS_INFO
											   	       SELECT 
											   	           ''['' + @subscription_db  + ''] - '' + ''['' +@publisher+ ''].['' +@publisher_db+'']: '' + @publication,
														   ''Pull'',
											   	   		   "publisher",
											   	   		   "distributor",
											   	   		   "publisher database",
											   	   		   "publication",
											   	   		   (CASE  WHEN "working_directory" IS  NULL THEN N''ALTERNATIVE'' ELSE N''DEFAULT'' END) AS D,
											   	   		   ISNULL("working_directory", "alt_snapshot_folder") AS A,
											   	   		   @subscription_db AS subscription_db,
											   	   		   "subscription name" AS [DESCRIPTION],
											   	   		   CASE  WHEN "update mode" =  0 THEN 0 ELSE 1 END,
											   	   		   CASE WHEN "enabled_for_synmgr" = 0 THEN 0 ELSE 1 END,
														   ''Transactional or Snapshot'',
														   NULL AS "IsSnapShotDiscovery"
											   	   	   FROM @SUBSCRIPTION_PULL_INFO_EXTEND
											   	   DELETE FROM @SUBSCRIPTION_PULL_INFO_EXTEND;
											   END
										 ELSE
											BEGIN
											   	   INSERT INTO  @SUBSCRIPTION_MERGE_PULL_INFO_EXTEND exec sp_helpmergepullsubscription  @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication;
											   	   INSERT INTO @AGGREGATED_SUBSCRIPTIONS_INFO
											   	       SELECT 
											   	           ''['' + @subscription_db  + ''] - '' + ''['' +@publisher+ ''].['' +@publisher_db+'']: '' + @publication,
														   ''Pull'',
											   	   		   "publisher",
											   	   		   "distributor",
											   	   		   "publisher_db",
											   	   		   "publication",
											   	   		   (CASE  WHEN "working_directory" IS  NULL THEN N''ALTERNATIVE'' ELSE N''DEFAULT'' END) AS D,
											   	   		    ISNULL(ISNULL("working_directory", "alt_snapshot_folder"), "dynamic_snapshot_location") AS A,
											   	   		   @subscription_db AS subscription_db,
											   	   		   "description" AS [DESCRIPTION],
											   	   		   0 ,
											   	   		   CASE WHEN "enabled_for_syncmgr" = 0 THEN 0 ELSE 1 END,
														   ''Merge'',
														   NULL AS "IsSnapShotDiscovery"
											   	   	   FROM @SUBSCRIPTION_MERGE_PULL_INFO_EXTEND
											   	   DELETE FROM @SUBSCRIPTION_MERGE_PULL_INFO_EXTEND;
											   END
									ELSE
									    BEGIN
										    INSERT INTO @AGGREGATED_SUBSCRIPTIONS_INFO
										        SELECT 
													   ''['' + @subscription_db  + ''] - '' + ''['' +@publisher+ ''].['' +@publisher_db+'']: '' + @publication,
													   CASE  WHEN @subscription_type = 0 THEN ''Push'' ELSE ''Anonymous'' END,
										    		   @publisher as "publisher",
										    		   '''',
										    		   @publisher_db as "publisher_db",
										    		   @publication as "publication",
										    		   ''DEFAULT'' AS D,
										    		   '''',
										    		   @subscription_db AS subscription_db,
										    		   '''' AS [DESCRIPTION],
										    		   CASE  WHEN @update_mode =  0 THEN 0 ELSE 1 END,
										    		   0,
													   CASE  WHEN @replication_type =  2 THEN ''Merge'' ELSE ''Transactional or Snapshot'' END,
													   NULL AS "IsSnapShotDiscovery"
										    	 
										    
							        END

									FETCH NEXT FROM SUBSCRIPTION_CURSOR INTO @publisher, @publisher_db , @publication , @subscription_db , @subscription_type , @update_mode, @replication_type;
								END

								CLOSE SUBSCRIPTION_CURSOR;
								DEALLOCATE SUBSCRIPTION_CURSOR;
						   END
						   '+'
						   END TRY
						   BEGIN CATCH
						   END CATCH
						   '+
						   'DELETE FROM @SUBSCRIPTION_INFO;'
from @Databases

set @query = @query + 'insert into @AGGREGATED_SUBSCRIPTIONS_INFO (IsSnapShotDiscovery) VALUES (@IsSnapshot); '

set @query = @query + 'select * from @AGGREGATED_SUBSCRIPTIONS_INFO; '


--SELECT @query
EXEC ( @query)



--DetectDistributorNameFromPublisherQuery
exec sp_helpdistributor



--SubscriberQuery
DECLARE @DatabaseName nvarchar(max);

DECLARE @Databases TABLE (
	DatabaseName sysname
)

DECLARE @UnfilteredDatabases TABLE (
	DatabaseName sysname
)

DECLARE @ExecError int
declare @sql nvarchar(max),  @dbname sysname, @quotedName nvarchar(max);

INSERT INTO @UnfilteredDatabases SELECT name FROM sys.databases  where name not in ('master','model','msdb','tempdb') and is_distributor <> 1

Declare temp Cursor LOCAL For 
  Select db.DatabaseName 
	From @UnfilteredDatabases db;
Open temp;

Fetch next From temp into @dbname;
		
While @@Fetch_Status=0 Begin
	set @quotedName = QUOTENAME(@dbname ,'"')
	set @sql = N'USE '+ @quotedName;
	BEGIN TRY
		EXEC (@sql);
		insert into @Databases values (@dbname);
	END TRY
	BEGIN CATCH
	END CATCH;
	Fetch next From temp into @dbname;
End

Close temp;
Deallocate temp;


DECLARE DBNAME_CURSOR CURSOR FOR  SELECT DatabaseName FROM @Databases;

OPEN DBNAME_CURSOR;
FETCH NEXT FROM DBNAME_CURSOR INTO @DatabaseName;

declare @IS_SUBSCRIPTION_EXIST BIT;
SET @IS_SUBSCRIPTION_EXIST = 0;

WHILE @IS_SUBSCRIPTION_EXIST = 0 AND @@FETCH_STATUS = 0
BEGIN
	declare @query nvarchar(max)
	declare @ParmDefinition nVARCHAR(40);
	SET @ParmDefinition = N'@IS_SUBSCRIPTION_EXIST_OUT BIT OUTPUT';
	SET @query = 'USE ' + QUOTENAME(@DatabaseName, '"') + '; ' +
	+ N'DECLARE @SUBSCRIPTION_INFO TABLE (
				publisher			sysname not null,
				publisher_db		sysname not null,
				publication			sysname null,
				replication_type	int not NULL,
				subscription_type	int not NULL,
				last_updated		datetime null,
				subscriber_db		sysname not null,
				update_mode			smallint null,
				last_sync_status	int null,
				last_sync_summary	sysname null,
				last_sync_time		datetime null
				) ;
	INSERT INTO  @SUBSCRIPTION_INFO exec sp_MSenumsubscriptions @subscription_type = ''both''
	SELECT @IS_SUBSCRIPTION_EXIST_OUT = (CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END) FROM @SUBSCRIPTION_INFO'
	EXECUTE sp_executesql @query, @ParmDefinition, @IS_SUBSCRIPTION_EXIST_OUT = @IS_SUBSCRIPTION_EXIST OUTPUT;
	FETCH NEXT FROM DBNAME_CURSOR INTO @DatabaseName
END 
CLOSE DBNAME_CURSOR;
DEALLOCATE DBNAME_CURSOR;

select @IS_SUBSCRIPTION_EXIST IS_SUBSCRIPTIONS_EXIST, 
CASE WHEN (SERVERPROPERTY('EngineEdition') = 4) THEN cast (1 as bit) ELSE cast (0 as bit) END as IS_EXPRESS



--PublicationQuery
DECLARE @Databases TABLE (
	DatabaseName sysname
)

DECLARE @UnFilteredDatabases TABLE (
	DatabaseName sysname
)

DECLARE @IsSnapshotGlobal bit;
set @IsSnapshotGlobal = 1;

INSERT INTO @UnFilteredDatabases
SELECT DISTINCT name as DatabaseName FROM 
(SELECT name FROM sys.databases WHERE is_published = 1 
UNION ALL 
SELECT name FROM sys.databases WHERE is_merge_published = 1) a

DECLARE @ExecError int
declare @sql nvarchar(max),  @dbname sysname, @quotedName nvarchar(max);

Declare temp Cursor LOCAL For 
  Select db.DatabaseName 
	From @UnFilteredDatabases db;
Open temp;
		
Fetch next From temp into @dbname;
		
While @@Fetch_Status=0 Begin
	set @quotedName = QUOTENAME(@dbname ,'"')
	set @sql = N'USE '+ @quotedName;
	BEGIN TRY
		EXEC (@sql);
		insert into @Databases values (@dbname);
	END TRY
	BEGIN CATCH
	END CATCH;
	Fetch next From temp into @dbname;
End

Close temp;
Deallocate temp;

declare @query nvarchar(max)
set @query = 'DECLARE @IsSnapshot bit; '+
'set @IsSnapshot = ' + cast (@IsSnapshotGlobal as nvarchar) + '; '
set @query = @query + 'CREATE TABLE #TranPublTemp_PublicationDiscovery ( ' +
'pubid int, ' +
'name sysname, ' +
'restricted int, ' +
'"status" int, ' +
'task int, ' +
'"replication frequency" int, ' +
'"synchronization method" int, ' +
'"description" nvarchar(max), ' +
'immediate_sync int, ' +
'enabled_for_internet int, ' +
'allow_push int, ' +
'allow_pull int, ' +
'allow_anonymous int, ' +
'independent_agent int, ' +
'immediate_sync_ready int, ' +
'allow_sync_tran int, ' +
'autogen_sync_procs int, ' +
'snapshot_jobid varbinary(255), ' +
'"retention" int, ' +
'"has subscription" int, ' +
'allow_queued_tran int, ' +
'snapshot_in_defaultfolder int, ' +
'alt_snapshot_folder nvarchar(255), ' +
'pre_snapshot_script nvarchar(255), ' +
'post_snapshot_script nvarchar(255), ' +
'compress_snapshot int, ' +
'ftp_address nvarchar(255), ' +
'ftp_port int, ' +
'ftp_subdirectory nvarchar(255), ' +
'ftp_login nvarchar(255), ' +
'allow_dts int, ' +
'allow_subscription_copy int, ' +
'centralized_conflicts int, ' +
'conflict_retention int, ' +
'conflict_policy int, ' +
'queue_type int, ' +
'backward_comp_level int, ' +
'publish_to_AD int, ' +
'allow_initialize_from_backup int, ' +
'replicate_ddl int, ' +
'enabled_for_p2p int, ' +
'publish_local_changes_only int, ' +
'enabled_for_het_sub int, ' +
'enabled_for_p2p_conflictdetection int, ' +
'originator_id int, ' +
'p2p_continue_onconflict int, ' +
'allow_partition_switch int, ' +
'replicate_partition_switch int ' +
'); ' 

set @query = @query + 'CREATE TABLE #MergePublTemp_PublicationDiscovery ( id int, ' +
'name sysname, ' +
'description nvarchar(max), ' +
'"status" int, ' +
'"retention" int, ' +
'sync_mode int, ' +
'allow_push int, ' +
'allow_pull int, ' +
'allow_anonymous int, ' +
'centralized_conflicts int, ' +
'priority int, ' +
'snapshot_ready int, ' +
'publication_type int, ' +
'pubid nvarchar(255), ' +
'snapshot_jobid varbinary(255), ' +
'enabled_for_internet int, ' +
'dynamic_filters int, ' +
'has_subscription int, ' +
'snapshot_in_defaultfolder int, ' +
'alt_snapshot_folder nvarchar(255), ' +
'pre_snapshot_script nvarchar(max), ' +
'post_snapshot_script nvarchar(max), ' +
'compress_snapshot int, ' +
'ftp_address nvarchar(255), ' +
'ftp_port int, ' +
'ftp_subdirectory nvarchar(255), ' +
'ftp_login nvarchar(255), ' +
'conflict_retention int, ' +
'keep_partition_changes int, ' +
'allow_subscription_copy int, ' +
'allow_synctoalternate int, ' +
'validate_subscriber_info int, ' +
'backward_comp_level int, ' +
'publish_to_activedirectory int, ' +
'max_concurrent_merge int, ' +
'max_concurrent_dynamic_snapshots int, ' +
'use_partition_groups int, ' +
'num_of_articles int, ' +
'replicate_ddl int, ' +
'publication_number int, ' +
'allow_subscriber_initiated_snapshot int, ' +
'allow_web_synchronization int, ' +
'web_synchronization_url nvarchar(255), ' +
'allow_partition_realignment int, ' +
'retention_period_unit int, ' +
'has_downloadonly_articles int, ' +
'decentralized_conflicts int, ' +
'generation_leveling_threshold int, ' +
'automatic_reinitialization_policy int ' +
'); '

set @query = @query + 'DECLARE @Temp TABLE ( ' +
	'DatabaseName nvarchar(255), ' +
	'PublicationName nvarchar(255), ' +
	'"Description" nvarchar(max), ' +
	'"Type" int, ' +
	'"Retention" int, ' +
	'RetentionInterval int, ' +
	'SynchronizationMethod int, ' +
	'AltSnapshotFolder nvarchar(255), ' +
	'SnapshotInDefaultFolder int, ' +
	'IsSnapShotDiscovery bit ' +
'); ' 

select  @query = @query + 'BEGIN TRY ' +
						  'USE ' + QUOTENAME(DatabaseName, '"') + '; ' +
						  'BEGIN TRY ' +
                          'INSERT INTO #TranPublTemp_PublicationDiscovery EXEC sp_helppublication; ' +
						  'END TRY ' +
						  'BEGIN CATCH ' +
						  -- to support SQL Server 14 sp2+ schema
						  'ALTER TABLE #TranPublTemp_PublicationDiscovery ADD allow_drop int; ' +
                          'INSERT INTO #TranPublTemp_PublicationDiscovery EXEC sp_helppublication; ' +
						  'END CATCH ' +
						  'INSERT INTO #MergePublTemp_PublicationDiscovery EXEC sp_helpmergepublication; ' +
						  'INSERT INTO @Temp ' +
						  'SELECT ' + QUOTENAME(DatabaseName, '''') + ' as DatabaseName, ' + 
						  'name as PublicationName, '+
						  '"description" as "Description", '+
						  '"replication frequency" + 1 as "Type", '+
						  '"retention" as "Retention", '+
						  '5 as RetentionInterval, '+
						  '"synchronization method" + 1 as "SynchronizationMethod", '+
						  'alt_snapshot_folder as AltShapshotFolder, '+
						  'snapshot_in_defaultfolder as SnapshotInDefaultFolder, '+
						  'null as IsSnapShotDiscovery ' +
						  'FROM #TranPublTemp_PublicationDiscovery '+
						  'UNION ALL '+
						  'SELECT ' + QUOTENAME(DatabaseName, '''') + ' as DatabaseName, ' + 
						  'name as PublicationName, '+
						  '"description" as "Description", '+
						  '3 as "Type", '+
						  '"retention" as "Retention", '+
						  'retention_period_unit + 1 as RetentionInterval, '+
						  'sync_mode + 1 as "SynchronizationMethod", '+
						  'alt_snapshot_folder as AltShapshotFolder, '+
						  'snapshot_in_defaultfolder as SnapshotInDefaultFolder, '+
						  'null as IsSnapShotDiscovery ' +
						  'FROM #MergePublTemp_PublicationDiscovery; '+
						  'END TRY ' +
						  'BEGIN CATCH ' +
						  'END CATCH ' +
						  'TRUNCATE TABLE #TranPublTemp_PublicationDiscovery; ' +
						  'TRUNCATE TABLE #MergePublTemp_PublicationDiscovery; '
from @Databases

set @query = @query + 'insert into @Temp (IsSnapShotDiscovery) VALUES (@IsSnapshot); ' + 
                      'select * from @Temp; ' + 
					  'DROP TABLE #TranPublTemp_PublicationDiscovery; ' +
				      'DROP TABLE #MergePublTemp_PublicationDiscovery; '

exec (@query)



--PublisherQuery
exec sp_helpdistributor
←

Leave a Reply

Your email address will not be published. Required fields are marked *