第25個冬天

  DECLARE @d_sql NVARCHAR(MAX) SET @d_sql = '' SELECT @d_sql = @d_sql + 'DROP TABLE ' + QUOTENAME(name) + '; ' FROM tempdb..sysobjects WHERE name like '#[^#]%' AND OBJECT_ID('tempdb..'+QUOTENAME(name)) IS NOT NULL IF @d_sql <> '' BEGIN     PRINT @d_sql     EXEC( @d_sql ) END Ref: https://stackoverflow.com/questions/5497450/drop-all-temporary-tables-for-an-instance

 在 SQL Server 中，Index Location（索引的位置） 通常是指「索引資料實際儲存的檔案位置」或「索引是放在哪個資料表分割區（partition）、資料檔案（filegroup）或頁面（page）上」。 CREATE INDEX ... ON <filegroup|partition_scheme> 若將非叢集索引放到與資料表不同的 filegroup 且位於不同磁碟，可能提升查詢效能，因為可以平衡 I/O 載荷；亦可利用 partition scheme 將 Clustered / Nonclustered index 分散到多個 filegroup 之上 https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17 官方 T‑SQL 語法說明 CREATE INDEX 對比 ON filegroup_name、ON partition_scheme(column) 與 ON [default] 若不指定，非分割資料表的 index 將放在與 base table 相同檔案群組。 [default] 並非資料庫預設組，而是指基表使用的 filegroup 或 partition scheme https://learn.microsoft.com/zh-tw/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17 建立索引時指定特定檔案 or filegroup https://stackoverflow.com/questions/7823882/how-to-create-indexs-on-a-different-file-of-filegroup?utm_source=chatgpt.com 將現有的索引移至不同的檔案群組 https://learn.microsoft.com/zh-tw/sql/relational-databases/indexes/move-an-existing-index-to-a-different-filegroup?view=sql-server...

  使用 PowerShell 腳本來執行 T-SQL  觸發查詢並將結果使用 bcp (Bulk Copy Program) 命令導出到 .log 文件 # 定義 SQL Server 的連線資訊 $serverName = "YOUR_SERVER_NAME" $databaseName = "YOUR_DATABASE_NAME" $username = "sa" $password = "YOUR_PASSWORD" $query = "SELECT * FROM YOUR_TABLE WHERE CONDITION = 'VALUE'" # 替換為您的 T-SQL 查詢 # 設定 bcp 命令所需的查詢和導出文件路徑 $queryFile = "C:\path\to\yourQuery.sql" $outputFile = "C:\path\to\output.log" # 將 SQL 查詢寫入一個文件中 Set-Content -Path $queryFile -Value $query # 建立 bcp 命令 $bcpCommand = "bcp `"$query`" queryout $outputFile -c -t, -S $serverName -d $databaseName -U $username -P $password" # 執行 bcp 命令 Invoke-Expression $bcpCommand # 檢查輸出文件 if ( Test-Path $outputFile ) { Write-Host "文件已成功導出到: $outputFile" } else { Write-Host "導出失敗" } 定義連線參數，包括伺服器名稱、資料庫名稱、用戶名和密碼。 設定要執行的查詢和結果輸出文件的路徑。 使用 bcp 命令從 SQL Server 導出數據到指定的 .log 文件。 $query 如果很複...

  ROW_NUMBER()函數 ROW_NUMBER()要配合 OVER()一起使用，生成的序號可以是升序的，也可以是降序的。 首先使用 SQL 語句創建一個表，然後添加一些數據，SQL 語句如下 /* CREATE TABLE Student ( Id int primary key not null, SName nvarchar(5), SAge int ) INSERT INTO Student(Id,SName,SAge) VALUES(1,'Ted',23),(2,'Sam',18),(3,'Jack',29),(4,'Mike',21) GO */ select * from Student --用ROW_NUMBER來針對年齡由小到大編列序號 SELECT (ROW_NUMBER() OVER( ORDER BY S.SAge ASC )) AS 序號 ,Id,SName AS 姓名 ,SAge AS 年齡 FROM Student AS S 分組排序 在 ROW_NUMBER()函數的 OVER()中，可以使用 PARTITION BY 關鍵字進行分組排序。 首先是根據第一個欄位分組，分組後再在組內根據第二個欄位再排序並加上序號。 PARTITION BY 關鍵字的基本語法如下： ROW_NUMBER() OVER(PARTITION BY 欄位1 ORDER BY 欄位2) 將上面的 Student 表中再添加一個城市的字段 City，新建一個Student2的表。 ROW_NUMBER,RANK,DENSE_RANK各自比較 /* drop table Student2 CREATE TABLE Student2 ( Id int primary key not null, SName nvarchar(50), SAge int, City nvarchar(10) ); INSERT INTO Student2(Id, SName, SAge, City) VALUES (1, 'Ted', 23, '台北'), (2, 'Sam...

 在使用 SQL Server 的 BCP (Bulk Copy Program) 工具匯出數據時，如果遇到中文別名變成亂碼的問題，通常是由於編碼設置不正確所導致。這裡有幾個步驟可以嘗試解決這個問題： 指定正確的字符集：在 BCP 命令中使用 -C 參數來指定正確的字符集。例如，如果你的數據庫使用的是中文字符集，可能需要設置 -C ACP 來使用活動代碼頁，或者使用 -C 936 指定簡體中文的代碼頁。使用 -C ACP 可以幫助確保 BCP 輸出或輸入的數據正確處理中文字符，避免出現亂碼，特別是當資料庫存儲的數據使用的是與 Windows 系統相同的字符編碼時。 bcp "SELECT * FROM your_table" queryout output_file.txt -c -C ACP -T -S your_server 在 SQL Server 的 BCP 工具中使用 -C 參數是來指定字符集的。-C ACP 這個參數指的是讓 BCP 使用活動代碼頁（Active Code Page）進行資料的匯出或匯入。代碼頁（Code Page）是一種用於提供特定語言字符集支援的編碼系統。 活動代碼頁 ACP 通常指的是在 Windows 系統上設定為預設的系統本地字符集，它會根據操作系統的地區設置自動調整。例如，在中文版 Windows 上，活動代碼頁通常設定為 936（即 CP936，對應於簡體中文 GBK 編碼）。 -T 參數用於指定使用 Windows 驗證。 -c 參數用於指定以字符模式進行數據輸出，適合文本數據。 使用 Unicode 格式：如果 -C 參數無法解決問題，嘗試將輸出格式設為 Unicode 格式。使用 -w 參數來將輸出設為 Unicode。 bcp "SELECT * FROM your_table" queryout output_file.txt -w -T -S your_server 啟用xp_cmdshell後就可以用它來間接執行bcp EXEC sp_configure 'show advanced options' , 1 ;  RECONFIGURE;  EXEC sp_configure 'xp_cmdshell' , 1 ;  RECONFIG...

  在朝狀交易特別是層層包覆的SP十分容易遇到.... 建議先不要特別為transaction 取名 一律都統一用 begin tran commit tran rollback tran https://stackoverflow.com/questions/19260055/cannot-roll-back-subtransaction-no-transaction-or-savepoint-of-that-name-was-fo https://www.sqlservercentral.com/forums/topic/transaction-no-transaction-or-savepoint-of-that-name-was-found https://blog.sqlauthority.com/2015/03/11/sql-server-error-msg-6401-level-16-cannot-roll-back-transaction/

  SELECT request_session_id AS spid,     resource_type AS rt,     resource_databASe_id AS rdb,     (CASE resource_type      WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id)      WHEN 'DATABASE' then 'sombas'      ELSE      (SELECT object_name(object_id) FROM sys.partitions          WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,     resource_description AS rd,     request_mode AS rm,     request_status AS rs FROM sys.dm_tran_locks where request_mode IN ('X','IX') --X 表示 dead lock --IX 表示 wait lock ，這種是因為前一個Request已經dead lock，而被pending的狀態。 kill {上一個查詢結果集的 spid} SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID()  AND resource_associated_entity_id = OBJECT_ID(N'dbo.Table名'); select cmd,* from sys.sysprocesses where blocked > 0         SELECT r.session_id,   ...

列出一或多個郵件設定檔的相關資訊。 EXECUTE msdb.dbo.sysmail_help_profile_sp 列出一或多個Database Mail 設定檔的相關帳戶。 EXECUTE msdb.dbo.sysmail_help_profileaccount_sp 列出Database Mail 帳戶的相關資訊(密碼除外)。 EXECUTE msdb.dbo.sysmail_help_account_sp 列出Database Mail設定檔 SELECT * FROM msdb.dbo.sysmail_profile 列出Database Mail帳戶 SELECT * FROM msdb.dbo.sysmail_account 列出設定檔對應的帳戶 select * from msdb.dbo.sysmail_profileaccount

條列角色與對應成員清單 WITH RoleMembers (member_principal_id, role_principal_id) AS ( SELECT rm1.member_principal_id, rm1.role_principal_id FROM sys.database_role_members rm1 (NOLOCK) UNION ALL SELECT d.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm (NOLOCK) INNER JOIN RoleMembers AS d ON rm.member_principal_id = d.role_principal_id ) select distinct rp.name as database_role, mp.name as database_userl from RoleMembers drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) order by rp.name 檢查某個角色下授權的存取清單(以USER_TABLE來篩) SELECT DB_NAME() AS 'DBName' ,p.[name] AS 'PrincipalName' ,p.[type_desc] AS 'PrincipalType' ,p2.[name] AS 'GrantedBy' ,dbp.[permission_name] ,dbp.[state_desc] ,so.name AS 'ObjectName' ,so.[type_de...

  接續上一篇 T-SQL筆記57_MERGE...USING語句使用_part1.When Matched/When Not Matched 對於有些資料記錄，在來源表中不存在，而在目標表中存在，則需要執行 刪除操作，將不存在的記錄刪除掉。 還是使用原來的來源表和目標表，並將數據恢覆原狀： TargetTable中 Id=11 和22 的記錄在SourceTable中不存在，需要刪除掉。 T-SQL程式碼 merge into TargetTable As T Using SourceTable as S on T.Id = S.Id WHEN Not Matched By Source THEN DELETE ; 在這邊SQL 語句中使用到“NOT MATCHED BY Source”這個語句 指定在來源表中不匹配的記錄，需要於目標表中刪除。

最近又回鍋到Porting母公司系統的職務了。 近期在閱讀公司上千行的預存程序既有的程式碼邏輯時候，又發現一個有點陌生的語句。 Merge .... Using... When Matched/When Not Matched 真的有點複雜 https://csharp-video-tutorials.blogspot.com/2014/09/part-69-merge-in-sql-server.html 微軟在 SQL Server 2008 及以後的版本中添加了 Merge 語句，使用 Merge 語句可以多個 SQL 的 DML 語句 組合成一個整體操作，從而 解決實現同樣的功能簡化 SQL 語句和事務。 DML 操作語句指的是對表執行 插入（INSERT）、更新（UPDATE）和刪除（DELETE）數據的操作。 與 Merge 語句搭配使用到的 USING 語句，可根據條件執行 DML 操作。 如此一來，就可以根據相關的條件對目標表執行插入、更新和刪除操作。 Merge 語句執行的操作中，會涉及到兩張表，一張表稱為來源表，另一張表稱為目標表。 語法如下： MERGE [TOP (表達式)] INTO <目標表> AS 別名 USING <來源表或一個來源查詢結果> [AS 別名] ON <條件表達式> [WHEN MATCHED THEN DML 操作] [WHEN NOT MATCHED THEN DML 操作] [WHEN NOT MATCHED BY SOURCE] (1). 使用 MERGE 指定目標表，目標表名放在 INTO 關鍵字的後面。 (2). AS 關鍵字後面是給目標表起一個別名。 (3). TOP 表示只對指定的前幾條執行 DML 操作。 (4). USING 指定源表，並使用 AS 指定別名。 (5). 使用 ON 將源表和目標表建立連接。 (6). 使用 WHEN…THEN 指定匹配條件。 (7). MATCHED 表示如果 ON 條件匹配上了，則執行的 DML 操作。 (8). NOT MATCHED 表示如果 ON 條件未匹配上，則執行的 DML 操作。 SQL測試資料案例建立 CREATE TABLE SourceTable ( Id int primary key not nu...

  最近在debug一隻差不多有4000多行的SP 此外這隻行之有年的SP，大約活了30年有。 還層層去call不同SP，當中包了一堆的階段性transaction。 我知道農曆七月還沒到，但這是真實的鬼故事。 需求就是要求要在此隻已經有歷史包袱的SP再去頂樓違建加蓋 最近在整合測試過程中也陸續有相關BUG Issue回饋要工程師修正 想將階段性跑出來前置單據的table資料給單獨存出來 好讓我額外包出來的SP比較能夠關注點分離去單元測試。 此時就發現了無論我是透過select * into 寫到全域性暫存表(##) 又或者insert into 一個table schema完全相同的copy_table皆無效 第一時間其實就有猜疑可能是transaction rollback導致的靈異現象。 再參考了多篇網文後，只要於測試區資料庫暫時性的透過@@TRANCOUNT比對一下交易數量 如果跑到你想要抽絲剝繭關注點分離的區塊就先暫時COMMIT;讓後續跑的區段不會受到交易包覆異常，等修完BUG在改回去，當然前提是在測試區資料庫。 Ref: https://www.cnblogs.com/JiaX/p/5667638.html http://vito-note.blogspot.com/2013/05/transactions.html https://note.robinks.net/2015/06/using-transaction-procedure-template.html https://littlehorseboy.github.io/2020/07/05/202007-t-sql-save-tran/#%E5%BB%BA%E7%AB%8B%E4%B8%80%E5%BC%B5-Table-%E4%BE%86-INSERT

  https://blog.sqlauthority.com/2008/07/22/sql-server-clear-sql-server-memory-caches/comment-page-1/

  每一天的累積都是未來的一大步... 在歷史故事或小說中，特別是在傳統的武俠小說中，常常有類似於到特定地點修行或學習以獲得特定武功技能的情節，就好比在不同公司工作可以學到不同的技能和經驗。 比方金庸的武俠小說《射雕英雄傳》中的主角郭靖先後歷經了 江南七怪傳授的基礎武功，跟洪七公學到降龍十八掌。 從黃蓉那裡間接學到了九陰真經。 在絕情谷中從老頑童周伯通那裡學到的空明拳。 隨後還有陸續跟丐幫接觸到打狗棒法等武術，每一位師傅教他不同的技巧。 基本上每一天工作不管學到捨麼程式語法技能， 每次在實際工作中施展出來就很像使用了某一招武功秘笈。 也可以讓工作過程保有一點儀式感跟熱忱，就很像打電動發出特殊絕招。 接續之前篇章-原先的前七篇篇章(水晶報表七日成蝶) 基本上因為在之前公司接觸到的水晶報表設計開發模式 都是針對visual studio 針對C#/vb.net搭配的開發設計情境 講白話一點就是你可能要在runtime時候都出一個類似DataTable等datasource元件之類透過程式碼方式回填到報表中。 Crystal Report報表開發(一)_專案配置 Crystal Report報表開發(二)_基礎操作排版對齊_基本組成部分介紹 Crystal Report報表開發(三)_綁定資料庫資料源_動態參數傳入 Crystal Report報表開發(四)_報表欄位的自動換行與自動編號 Crystal Report報表開發(五)_每張報表表頭表尾顯示差別_調整區段自動縮放技巧 Crystal Report報表開發(六)_每頁限制細目顯示資料列數 Crystal Report報表開發(七)_缺列補空白_Runtime參數設置_公式設置_避免多浪費空白頁的後端程式修正 看起來七日好像還不太能成蝶XDD Crystal Report其實已經有段時間了也是滿多公司在用的 當然也有些公司若用.net (C#,vb.net)會藉由類似像 pdfsharp 或是 iTextSharp 匯出pdf報表方式 或透過 RDLC  ，若是node.js開發者則可能藉由 pdfkit 。 上述都是在之前篇章有介紹過的常見pdf報表匯出功能solution 不過不得不承認 水晶報表功能真的是滿強大 在後續的篇章中(由於近期公司用到的報表開發模式比較不一樣) 要下載獨立一套c...

  使用如下方法 ALTER AUTHORIZATION ON DATABASE::[輸入出問題的資料庫名稱] TO [資料庫管理員帳號] 或用以下方法 USE [出問題的資料庫名稱] GO EXEC sp_changedbowner '資料庫管理員帳號'  GO

  bak正常還原了針對DB右鍵屬性>選項>狀態的限制存取裡 「Multiple」、「Single」與「Restricted」改成Multiple就可以了

Chinese_Taiwan_Stroke_BIN 與 Chinese_Taiwan_Stroke_CI_AS 之間的定序衝突 http://mmncsmm.blogspot.com/2010/04/chinesetaiwanstrokebin.html Cannot resolve the collation conflict between "Chinese_Taiwan_Stroke_CI_AS" http://eric-fire.blogspot.com/2014/04/cannot-resolve-collation-conflict.html

使用系統視圖sysjobs和sysjobsteps來查詢作業和作業步驟的定義，並找到 包含特定SP的作業。 1 2 3 4 SELECT j.name AS [Job Name] FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id WHERE s.command LIKE '%YourStoredProcedureName%' -- 替換為要搜索的存儲過程名稱 使用系統視圖sysjobschedules和sysschedules來查詢作業的排程時間。 1 2 3 4 5 6 7 8 9 10 SELECT j.name AS [Job Name], s.name AS [Schedule Name], js.next_run_date AS [ Next Run Date ], --js.next_run_time AS [Next Run Time] LEFT (Replicate( '0' , 6 - Len(js.next_run_time)) + Cast (js.next_run_time AS VARCHAR ( 6 )), 2 ) + ':' + Substring (Replicate( '0' , 6 - Len(js.next_run_time)) + Cast (js.next_run_time AS VARCHAR ( 6 )), 3 , 2 ) + ':' + RIGHT (Replicate( '0' , 6 - Len(js.next_run_time)) + Cast (js.next_run_time AS VARCHAR ( 6 )), 2 ) AS [ Next Run Time] FROM msdb.dbo.sysjobschedules AS js INNER JOIN msdb.dbo.sysschedules AS s ON js.schedule_id ...