115 lines
3.9 KiB
Transact-SQL
115 lines
3.9 KiB
Transact-SQL
/*
|
|
Connect to hcimon and run this query.
|
|
Then, insert the publisher and the distributors entry in this fragment
|
|
|
|
https://www.sqlservercentral.com/forums/topic/how-to-import-replication-monitor-my-publishers-into-ssms-across-versions
|
|
|
|
|
|
<?xml version="1.0" encoding="utf-8"?>
|
|
<PublisherGroups>
|
|
<ViewSetting Value="group" />
|
|
<Group Name="My Publishers">
|
|
|
|
<!-- copy Publisher nodes here -->
|
|
|
|
</Group>
|
|
|
|
<!-- copy Distributor nodes here -->
|
|
|
|
</PublisherGroups>
|
|
|
|
and save it in the local folder
|
|
C:\Users\ua208700\AppData\Roaming\Microsoft\Microsoft SQL Server\160\Tools\SQL Monitor\rmsetting.xml
|
|
|
|
*/
|
|
USE [ControlCenter]
|
|
|
|
DECLARE @tpl VARCHAR(MAX)='
|
|
<Publisher>
|
|
<name>@srv@\@inst@</name>
|
|
<Distributor>@srv@\@inst@</Distributor>
|
|
<Interval>5</Interval>
|
|
<AutoRefresh>True</AutoRefresh>
|
|
<AutoConnect>False</AutoConnect>
|
|
<IsOraclePublisher>False</IsOraclePublisher>
|
|
<LastSelectedAgentType>Snapshot Agent</LastSelectedAgentType>
|
|
<LastSelectedFilterType>0</LastSelectedFilterType>
|
|
<LastSelectedPubType>0</LastSelectedPubType>
|
|
<LastSelectedTab>0</LastSelectedTab>
|
|
<GridSettings />
|
|
</Publisher>
|
|
'
|
|
DECLARE @tplDis VARCHAR(MAX)='
|
|
<DistributorsSetting Name="@srv@\@inst@">
|
|
<Interval>5</Interval>
|
|
<AutoRefresh>True</AutoRefresh>
|
|
<AutoConnect>False</AutoConnect>
|
|
<LastSelectedAgentType>Snapshot Agent</LastSelectedAgentType>
|
|
<LastSelectedFilterType>0</LastSelectedFilterType>
|
|
<LastSelectedPubType>-1</LastSelectedPubType>
|
|
<LastSelectedTab>0</LastSelectedTab>
|
|
<GridSettings />
|
|
</DistributorsSetting>
|
|
'
|
|
/****** Script for SelectTopNRows command from SSMS ******/
|
|
SELECT [SE_designation]
|
|
,[SE_OU_code]
|
|
,[SE_DNS]
|
|
,[SE_instance_name]
|
|
,e.[EN_designation]
|
|
,REPLACE(
|
|
REPLACE(@tpl,'@inst@', s.[SE_instance_name])
|
|
,'@srv@'
|
|
,CASE
|
|
--WHEN [e].[EN_designation]='amavita' THEN 'SAMNB'
|
|
--WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SCVNB'
|
|
--WHEN [e].[EN_designation]='Sun Store' THEN 'SSUNB'
|
|
WHEN [e].[EN_designation]='amavita' THEN 'SWAMA'
|
|
WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SWCVI'
|
|
WHEN [e].[EN_designation]='Sun Store' THEN 'SWSUN'
|
|
ELSE ''
|
|
END +RIGHT(CAST([s].[SE_OU_code] AS CHAR(3)),3)+'VM01'
|
|
)AS xmlPub
|
|
,REPLACE(
|
|
REPLACE(@tplDis,'@inst@', s.[SE_instance_name])
|
|
,'@srv@'
|
|
,CASE
|
|
--WHEN [e].[EN_designation]='amavita' THEN 'SAMNB'
|
|
--WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SCVNB'
|
|
--WHEN [e].[EN_designation]='Sun Store' THEN 'SSUNB'
|
|
WHEN [e].[EN_designation]='amavita' THEN 'SWAMA'
|
|
WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SWCVI'
|
|
WHEN [e].[EN_designation]='Sun Store' THEN 'SWSUN'
|
|
ELSE ''
|
|
END +RIGHT(CAST([s].[SE_OU_code] AS CHAR(3)),3)+'VM01'
|
|
)AS xmlDist
|
|
,CASE
|
|
--WHEN [e].[EN_designation]='amavita' THEN 'SAMNB'
|
|
--WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SCVNB'
|
|
--WHEN [e].[EN_designation]='Sun Store' THEN 'SSUNB'
|
|
WHEN [e].[EN_designation]='amavita' THEN 'SWAMA'
|
|
WHEN [e].[EN_designation]='Coop-Vitality' THEN 'SWCVI'
|
|
WHEN [e].[EN_designation]='Sun Store' THEN 'SWSUN'
|
|
ELSE ''
|
|
END +RIGHT(CAST([s].[SE_OU_code] AS CHAR(3)),3)+'VM01'
|
|
+'\'+[s].[SE_instance_name]
|
|
AS ou
|
|
FROM [dbo].[Server] s
|
|
JOIN dbo.[Entity] e ON [e].[EN_id] = [s].[SE_entity]
|
|
WHERE [e].[EN_designation] IN ('Coop-Vitality', 'amavita', 'sun store')
|
|
--and left(SE_DNS, 9) in (
|
|
--'AMA272APS',
|
|
--'AMA269APS',
|
|
--'AMA299APS',
|
|
--'AMA301APS',
|
|
--'AMA302APS',
|
|
--'AMA303APS',
|
|
--'AMA305APS',
|
|
--'AMA622APS',
|
|
--'AMA624APS',
|
|
--'AMA625APS',
|
|
--'SUN841APS',
|
|
--'SUN851APS',
|
|
--'SUN871APS',
|
|
--'SUN891APS')
|
|
ORDER BY SE_DNS |