自動記錄滑鼠點選操作

為了一個需求，需要人工去點選幾個滑鼠的點選行為，找了幾種工具，覺得GS Auto Clicker最實用，極符合我的需求，尤其是多個固定位置的button點擊，解決了一個很耗時間的工作。

最後設定完預設hotkey F8為啟用鍵，就可以開始自動做工了。再按F8結束工作。

GS Auto Clicker - Free - download for Windows (digitaltrends.com) 

和SSL連線有關的設定

HTTPS連線，不管是在寫sql clr或者powershell，每次遇到以下二類SSL錯誤，都忘了要改啥。這次記錄下，以後可以參考。

System.Net.WebException: 基礎連接已關閉: 接收時發生未預期的錯誤。 ---> System.ComponentModel.Win32Exception: 用戶端和伺服器無法溝通，因為它們沒有公用的演算法

加入以下

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12| SecurityProtocolType.Tls13;

System.Net.Http.HttpRequestException: An error occurred while sending the request. ---> System.Net.WebException: 基礎連接已關閉: 無法為 SSL/TLS 安全通道建立信任關係。 ---> System.Security.Authentication.AuthenticationException: 根據驗證程序，遠端憑證是無效的。

加入以下：

ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };

順便再加一段sql clr assembly，單純的呼叫GET方法 WEB URL，回傳respone內容。

CREATE ASSEMBLY [MOJPS2024_CLR]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103006376E1660000000000000000E00022200B013000000A00000006000000000000D2280000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000802800004F00000000400000B802000000000000000000000000000000000000006000000C000000482700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D808000000200000000A000000020000000000000000000000000000200000602E72737263000000B80200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000B4280000000000004800000002000500EC2000005C06000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020064000000010000110020003C0000280600000A007E02000004252D17267E01000004FE0605000006730700000A258002000004280800000A0002280900000A74070000010A066F0A00000A74080000010B076F0B00000A730C00000A0C086F0D00000A0D0913042B0011042A2202280E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000034020000237E0000A00200000003000023537472696E677300000000A00500000400000023555300A4050000100000002347554944000000B4050000A800000023426C6F620000000000000002000001571502000902000000FA0133001600000100000014000000030000000200000005000000050000000E000000050000000200000001000000030000000100000000008F01010000000000060005014F02060025014F020600DC003C020F006F0200000600C902CC010A00F00014020E00DB02D0020E008E00D0020600E8013E000600C1004F020E004301EA020600AA007E020E00D3017E020E00B902EA020E000002D0020E007900D0020E00DF02D0020E009200D0020600C5013E000600F5013E00000000003500000000000100010001001000AC020000150001000100032110006B00000015000100030036001D0054001600010058005020000000009600DD015C000100C0200000000086182F0206000200C920000000009118350261000200C0200000000086182F0206000200D8200000000083000A006500020000000100B601000001002100000002002600000003002B0000000400300009002F02010011002F02060019002F020A0031002F02060051002F0206007900A1011B0059002F0221007900670127008900BA002D0089009E0033009100BA01380049002F023D00A1006F00430029002F02060020002300A1002E000B0070002E00130079002E001B00980063002B00A10010004700048000000000000000000000000000000000480000000400000000000000000000004B006200000000000400000000000000000000004B005600000000000400000000000000000000004B00CC01000000000300020000000000003C3E395F5F305F30003C61666E52746E4A736F6E3E625F5F305F30003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F004D4F4A5053323032345F434C520053797374656D2E44617461006D73636F726C6962003C3E630052656164546F456E6400536563757269747950726F746F636F6C547970650048747470576562526573706F6E736500476574526573706F6E7365005835303943657274696669636174650043726561746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B004D4F4A5053323032345F434C522E646C6C007365745F536563757269747950726F746F636F6C0075726C00476574526573706F6E736553747265616D0053797374656D0058353039436861696E0061666E52746E4A736F6E0053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E53656375726974792E43727970746F6772617068792E583530394365727469666963617465730053716C46756E6374696F6E730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E65740048747470576562526571756573740053797374656D2E4E65742E536563757269747900000000000000AD09D5857789084B8F8B5D8811853018000420010108032000010520010111110A0705121D122112250E0E050001011141052002011C1805000101122D05000112450E0420001249042000124D05200101124D0320000E0307010208B77A5C561934E0890306120C0306122D0400010E0E030000010A2004021C1231123511390801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730108010007010000000004010000000000000000006376E16600000000020000001C010000642700006409000052534453A32B3C934BC65F4AAA24487DD7955D0801000000443A5C50726F6A656374735C4D4F4A5C4D4F4A50535C50726F6A656374735C4D4F4A5053323032345F434C525C4D4F4A50535F53514C434C525C6F626A5C44656275675C4D4F4A5053323032345F434C522E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A82800000000000000000000C2280000002000000000000000000000000000000000000000000000B4280000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005C02000000000000000000005C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004BC010000010053007400720069006E006700460069006C00650049006E0066006F0000009801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000044001200010049006E007400650072006E0061006C004E0061006D00650000004D004F004A005000530032003000320034005F0043004C0052002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004D004F004A005000530032003000320034005F0043004C0052002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000D43800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = UNSAFE

GO

CREATE FUNCTION [dbo].[afnRtnJson]

(@url NVARCHAR (MAX) NULL)

RETURNS NVARCHAR (MAX)

AS

 EXTERNAL NAME [MOJPS2024_CLR].[SqlFunctions].[afnRtnJson]

GO

無法使用Database Mail

設定Database Mail時，顯示

Database Mail depends on Service Broker. Service Broker is not active in msdb. Do you want to activate Service Broker in msdb? If you do not activate Service Broker, Database Mail will queue e-mail messages, but will not be able to deliver the messages.

select is_broker_enabled from sys.databases where name = 'msdb';

回傳0，確實沒啟動。

ALTER DATABASE msdb SET ENABLE_BROKER ;

等了好久沒反應。

參考以下重建

https://learn.microsoft.com/zh-tw/troubleshoot/system-center/scom/troubleshoot-sql-server-service-broker-issues

use master

go

ALTER DATABASE msdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE ALTER DATABASE msdb SET MULTI_USER go ALTER DATABASE msdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE msdb SET ENABLE_BROKER ALTER DATABASE msdb SET MULTI_USER go

ReportViewer 工具列顯示中文

VS專案中要升級舊版的report viewer，已經不用再去安裝runtime，直接以NuGet方式安裝Microsoft.ReportingServices.ReportViewerControl.WebForms  150.1652.0 ，但部署後，工具列都是英文沒有中文。

但在bin下其實有本地化的目錄zh-CHT 所以應該有支援多語系，在bing和google了半天都找不到想要的，覺得這二個搜尋引擎好像愈來愈難找到想要的資訊，是我的錯覺嗎???

後來想到用保哥很推薦的felo search(https://felo.ai/)，還真的沒讓我失望耶。

輸入了 Microsoft.ReportingServices.ReportViewerControl.WebForms zh-CHT  詳列了做法。主要是在web.config加入以下設定。

<configuration>

  <system.web>

    <globalization culture="zh-CHT" uiCulture="zh-CHT" />

  </system.web>

</configuration>

將zh-CHT 目錄下的dll加入專案參考後，重新部署，工具列就出現中文了。

SQL AGENT無法啟動 無效的物件名稱 'syssubsystems'

有一天一台SQL 2019 主機安裝CU27後，SQL AGENT無法啟動，查看SQLAGENT.OUT記錄檔錯誤如下

2024-08-06 12:00:08 - ! [298] SQLServer 錯誤: 208，無效的物件名稱 'syssubsystems'。 [SQLSTATE 42S02] (ConnCacheSubsystems)

2024-08-06 12:00:08 - ! [449] 無法列舉子系統 (原因: 無效的物件名稱 'syssubsystems'。 [SQLSTATE 42S02] (錯誤 208))

再安裝CU28也是一樣問題無法啟動。

於是找了另外一台正常的DB查看，確實有syssubsystems這個資料表存在。

直接轉出create語法在有問題的那台DB把資料表建起來，重啟SQL AGENT服務就正常了。

CREATE TABLE [dbo].[syssubsystems](

[subsystem_id] [int] NOT NULL,

[subsystem] [nvarchar](40) NOT NULL,

[description_id] [int] NULL,

[subsystem_dll] [nvarchar](255) NULL,

[agent_exe] [nvarchar](255) NULL,

[start_entry_point] [nvarchar](30) NULL,

[event_entry_point] [nvarchar](30) NULL,

[stop_entry_point] [nvarchar](30) NULL,

[max_worker_threads] [int] NULL

) ON [PRIMARY]

GO

重啟後，這個資料表就會自動長出一些資料。

看了二篇文章似乎都跟msdb有做什麼變動造成的。回想一下為什麼有這個錯，我有改過msdb嗎?? 好像也沒有...

https://www.dbaservices.com.au/the-ssis-subsystem-failed-to-load/

The problem is most likely that the location of your SQL Server installation directory differs from that of the old serve

https://byronhu.wordpress.com/2011/07/04/sql-agent-job-%E6%9A%AB%E5%81%9Csuspend/

因為從原來全部安裝在 C:\ 的 SQL Server 備份 msdb 後，restore 到安裝在 D:\ 的 SQL Server，除要注意 SQL Server 的 Build No 外，若有  Job 呼叫到外部子系統(例如 SSIS、Replication…等)，也要一併注意 msdb.dbo.syssubsystems 的設定

二篇都說到執行，但我是連這個資料表都沒有，就直接重建了。

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go

SSAS Restore vs Detach & Attach 大資料庫

因為要重裝某台DB 作業系統，但其中有個較大的SSAS OLAP DB，每每備份後再還原到另外一台備援機時，都要三天三夜...三更半夜....然後還不一定成功?

先用一個小DB試著在主機1用Deatch 卸離後，複製OLAP Data目錄下的資料庫資料夾到主機2 OLAP Data目錄下，然後在主機2 Attach掛載起來，看來大大節省時間。

(後來也有試過用mklink做過UNC 目錄的Attach看來也是可以瀏覽，不過沒有做後續process 測試，故也不確定有沒有問題)

就像用資料庫的 Detach  & Attach一樣，用來刪掉重建.ldf檔，不用費力去瘦身ldf檔，快速又方便。，

最後在重安裝的主機上，因只format掉C:\ 故重安裝好SQL SERVER後再由原來的SSAS Data目錄Attach檔案，所以必需開啟其他目錄可讀取Data目錄。

在SSAS 主機按右鍵進入屬性後，於一般下勾選顯示進階，調整AllowedBrowsingFolder，用 | 破折號相隔加入可瀏覽目錄，如下紅字。

G:\OLAP\Backup\|G:\OLAP\Log\|G:\OLAP\Data\|F:\OLAP\Data\

windows中的symbol link mklink /J

 C:\Windows\system32>mklink /?

建立符號連結。

MKLINK [[/D] | [/H] | [/J]] Link Targe

        /D      建立目錄符號連結。預設是檔案符號連結。

        /H      建立永久連結而不是符號連結。

        /J      建立目錄連接。

        Link    指定新符號連結名稱。

        Target  指定新連結參照的路徑 (相對或絕對)。

二台WEB主機，WEB2 IIS建置站台時以WEB1 UNC目錄(\\192.168.0.123\WebSite\) 為來源。

在WEB1主站台上，又需要在某功能目錄可連結WEB1其他站台的報表目錄。

利用mklink /J 竟然可達成。(mklink /D (預設) 在web2讀取rpt1 目錄會有錯誤)

mklink /J web1\master\rpt1 web1\sys1\ReportFile

mklink /J web1\master\rpt2 web1\sys2\ReportFile

mklink /J web1\master\rpt3 web1\sys3\ReportFile

不太求甚解...能達成目的就好....