Category Archives: SQL Server

輸出執行狀態

若要避免輸出過程中被 Queue 住,可以 Raiserror 代替 Print

參考:https://www.red-gate.com/simple-talk/blogs/outputting-status-t-sql-code/

DECLARE @i INT = 1, @msg NVARCHAR(100), @startTime datetime2(4) = SYSDATETIME()
WHILE @i <= 10
  BEGIN
    SET @msg = CONCAT(‘Iteration:’, @i, ‘ ‘, DATEDIFF(MILLISECOND,@startTime,SYSDATETIME())/1000.0 , ‘sec elapsed.’)
    –PRINT @msg –會 Queue 住不出來
    RAISERROR (@msg, 10,1) WITH NOWAIT; –Severity 使用 10 以下,則 raiserror 功能類似 PRINT
    WAITFOR DELAY ’00:00:01′; –wait one second
    SET @i = @i + 1;
END;

廣告

解開 SQL Server 加密的 T-SQL 物件

感謝 Neilas 提供:

https://www.devart.com/dbforge/sql/sqldecryptor/download.html

bcp XML format file

要產生 format file 時,輸出檔要設定 nul,而搭配 –f 指定 format file 檔案名稱

bcp northwind.dbo.customers format nul -t\t -T -r\n -x -c -fC:\temp\customers.fmt

bcp northwind.dbo.customers out customers.txt -t\t -T -r\n -c

SELECT * FROM OPENROWSET ( BULK ‘C:\temp\customers.txt’, FORMATFILE = ‘C:\temp\customers.fmt’ ) AS customers

找尋資料表與索引所在的 FileGroup

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = ‘U’ — User Created Tables
GO

參照

https://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/

Open Master Key

USE Northwind;
GO
–  建立資料庫 master key 來保護接下來建立的憑證
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P2$$w0rd’
— 若資料庫 Backup/Restore 到其他執行個體,需要先 open 才能使用其加密的其他憑證或鑰匙
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘P2$$w0rd’

CREATE CERTIFICATE foo
–   ENCRYPTION BY PASSWORD = ‘password’ 
–若不用密碼就需要先設定 Database Master Key
   WITH SUBJECT = N’測試加解密’,
   EXPIRY_DATE = ’10/31/2026′;

SQL Server clustered 架構下,不知如何 -f

model 損毀,在 clustered 架構下,似乎無法最小啟動

單機模式下,以下的作法可行

http://sharedderrick.blogspot.tw/2011/10/tempdb-1814could-not-create-tempdb.html

https://pastebin.com/QqNz7xYd

 

似乎在 clustered 下,平常還要備一份 master.mdf,ldf 和 model.mdf,ldf,免得 master 註記 model 壞了,就直接換 model.mdf,ldf master 也不管,但又無法透過 –f 進去改變 master 所註記的 model 狀態

感謝 John 提醒,在 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER1000\MSSQL\Binn\Templates 目錄下有可用的空白系統資料庫 master、model 和 msdb 的 mdf/ldf

IIS_IUsers 存取本機私密金鑰憑證

若 ASP.NET 存取 SQL Server Always Encrypted 錯誤

image

image

image

image

image

小心 MAX

image

SSMS 查詢文字結果過長

雖然 Grid 設定 Max 可達 64K,但實際 4 萬多字可能就被截斷了,最好是轉成 XML 呈現。

OPENROWSET 取 Tab 分隔檔

SQL 2017 以前的做法

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

https://dba.stackexchange.com/questions/29306/can-i-query-a-tab-delimited-file-from-ssms

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

 

製造來源 file 欄位定義

a b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4 5 6 7 8 9 0

100 萬筆紀錄,利用此種方式載入 table,只會用一顆 CPU,在我的 NB 耗時 10 min