MS SQL Server 讀取 AD 資料

MS SQL Server 讀取 AD 方式有二
1.OPENROWSET
2.Linked Server + OPENQUERY

[OPENROWSET]
Step1 開啟 OPENROWSET 功能(已開啟則省略)

--開啟進階選項 1:開啟 / 0:關閉
sp_configure 'show advanced options',1
reconfigure

--開啟openrowset功能 1:開啟 / 0:關閉
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Step2 T-SQL
   [Account] : domain\account, ex. ABC\John
   [Password] : account password
   [Domain] : domain name, ex. ABC.com
   ※LDAP必須大寫

select * from openrowset(
   'ADsDSOObject' --Provider
   ,'';'[Account]';'[Password]' --Data Source;DomainAccount;Password
   ,'select sn,givenname,mail from ''LDAP://[Domain]'' where sn=''*'' '
)

常用 AD 欄位

欄位名	說明	欄位名	說明
samaccountname	AD帳號	mail	Email
givenname	名字	sn	姓氏
displayname	Fullname (姓+名)	company	公司
department	部門	location	位置
title	職稱	請參考 LDAP Properties for CSVDE and VBScript

LDAP查詢語法請參考LDAP 查詢基礎

[OPENQUERY]
Step1 新增Linked Server
   提供者 : OLE DB Provider for Microsoft Directory Services
   產品名稱 : 隨便
   資料來源 : domain name, ex. ABC.com


Step2 安全性設定
   遠端登入 : domain\account, ex. ABC\John
   指定密碼 : account password


Step3 T-SQL

select * from openquery(ADSI
   ,'select sn,givenname,mail from ''LDAP://[Domain]'' where sn=''*'' '
)

附註 :
   1. SQL Server 2005 最多只能抓到 1000 筆 AD 資料
   2. SQL Server 2008 R2 最多只能抓到 901 筆 AD 資料
   3. SQL Server 2012 最多只能抓到 901 筆 AD 資料

出人意料的 PL/SQL 的 In Condition

最近有一個需求
需要用PL/SQL 查出每天最後輸入的一筆資料
在Table 內, 一天可能有多筆資料

想來想去, 大概也只能用串欄位 In (Sub-Query) 或用 Exists 的方式處理

--串欄位 In (Sub-Query)
SELECT * FROM TABLEA
WHERE (DATE_COL||TIME_COL) IN (
   SELECT DATE_COL||MAX(TIME_COL)
   FROM TABLEA
   GROUP BY DATE_COL
)

--Exists
SELECT * FROM TABLEA SRC
WHERE EXISTS (
   SELECT 1
   FROM TABLEA
   WHERE DATE_COL = SRC.DATE_COL
   GROUP BY DATE_COL
   HAVING MAX(TIME_COL) = SRC.TIME_COL
)

在前輩高手提供的解法中又學到了另一種方式
在 Oracle 10g 之後(9i 不確定有沒有)
PL/SQL 的 In Condition 有一種應用:
WHERE (expr1,expr2,...) IN (Sub-Query)
所以上例可以如此處理

--(expr1,expr2,...) In (Sub-Query)
SELECT * FROM TABLEA
WHERE (DATE_COL, TIME_COL) IN (
   SELECT DATE_COL, MAX(TIME_COL)
   FROM TABLEA
   GROUP BY DATE_COL
)

效率有沒有比較好,還不確定,但能發現自己想到的解法外的新方法,還是滿有趣的

詳細文件 :
Oracle PL/SQL In Condition

SQL Server 建立 Oracle 連結伺服器

記錄一下在SQL Server 建立 Oracle 連結伺服器要注意的地方.

安裝好 Oracle Client 後
就可以到SQL Server建立「Oracle Provider for OLE DB」的連結伺服器
設定畫面如下:

測試連接也都正常

但是一下OPENQUERY查詢,就發生錯誤

最後終於找到原因:Provider要設定InProcess

最後,記得要將連結伺服器刪除重新建立,
這樣InProcess才會生效.

可參考下列資料(雖然說明有些都看不懂):
MSDN-OPENQUERY (Transact-SQL)
MSDN-連結的伺服器屬性(提供者選項頁面)

T-SQL 計算日期的週別

這邊記錄一下用 T-SQL 計算週別的公式

如果以星期日 ~ 星期六為一週的話
直接用 datepart(wk, @TargetDate) 就可以抓出正確的週別了

但是如果是星期五 ~ 星期四為一週
或星期一 ~ 星期日為一週呢?

可以利用下面的公式算出對應的週別

@TargetDate 是要查詢的日期
@StartNo 是指星期幾開始
(ex. 星期五 ~ 星期四為一週, @StartNo = 5)

   case when (datepart(dy, @TargetDate) - @StartNo) <= 0 then 1
   else datepart(wk, @TargetDate - @StartNo) end

將 SQL Server 2005 SSIS 封裝設為範本

今天在查怎麼將設計好的 SQL Server 2005 SSIS 封裝設為範本
就順便記錄下來 :

1.將設計好的 SSIS 建置起來
2.將建置的封裝檔 (*.dtsx) copy 到
C:\Program Files\Microsoft Visual Studio 8\Common7\
IDE\PrivateAssemblies\ProjectItems\
DataTransformationProject\DataTransformationItems\ 下
3.在專案新增項目, SQL Server Integration Services 專案項目中就會出現此範本
4.參考 TechNet 的說明, 改變封裝的 Name 與 ID 值

[參考TechNet]

話說我在查 TechNet 的資料時, 一直看錯
以為只要 copy 到
C:\Program Files\Microsoft Visual Studio 8\Common7\
IDE\PrivateAssemblies\ProjectItems\
DataTransformationProject\
就好, 害我試了半天都叫不出範本

SQL Server 2005 備份 Analysis Service DB

SQL Server Analysis Service DB
透過 XMLA 命令就可以做到排程備份
將粗體字的部份替換成實際資料即可

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <Object>
      <DatabaseID>[Analysis Service DB Name]</DatabaseID>
   </Object>
   <File>[FilePath].abf</File>
   <AllowOverwrite>true</AllowOverwrite>
</Backup>

[參考小狐狸部落] [參考TechNet]

SQL Server 2005 備份

完整備份 : 備份 DB 的 .mdf 及 .ldf

差異備份 : 必須先產生完整備份
　　　　   備份完整備份至目前為止所有異動過的 .mdf 資料
　　　　   適用於 : 資料量成長較低, 但交易頻繁的 DB, 且交易歷程較不重要的 DB

交易記錄備份 : 必須先產生完整備份
　　　　　　    備份自完整備份/差異備份/上次交易記錄備份到目前為止所有異動過的 .ldf 資料 (見附圖)
　　　　　　    適用於 : 資料量急速成長的 DB

Command :

--截斷交易記錄
backup log DBName with truncate_only

--壓縮資料庫
dbcc shrinkdatabase(DBName, truncateonly)

--壓縮檔案
dbcc shrinkfile(DBName_log, Default_Size)

--完整備份
backup database DBName to disk = 'D:\Folder\xxxxxxxxxxxxx.bak'

--差異備份
backup database DBName to disk = 'D:\Folder\xxxxxxxxxxxxx.bak' with differential

--交易記錄備份
backup log DBName to disk = 'D:\Folder\xxxxxxxxxxxxx.trn'

SQL Server 2005 自動識別欄位檢視表

今天查了一下 SQL Server 2005 的自動識別欄位
發現每個 DB 下都有個系統 View 可以查詢

SELECT * FROM sys.identity_columns

name 代表欄位名稱
seed_value 代表初始值
increment_value 代表遞增值
last_value 代表最終值

參考 TechNet

T-SQL Like 的小玩意兒

偶然發現 T-SQL Like 的一個小玩意兒
所以記錄一下

SELECT * FROM TableA
WHERE Col1 LIKE '[TB]est%' --查詢出來以 'Test' 開頭或 'Best' 開頭的資料

在一些情況下, 善用這個方式, 就不用一直 LIKE OR LIKE 了

SQL Server 的 BINARY_CHECKSUM

為了 Update data 的效能及減少 Log 檔大小
在 Update 之前都要先檢查資料是否有異動
一檢查可能就是十幾個欄位在做 Equal to 的比較

SQL Server 本身提供一個 BINARY_CHECKSUM 的 function
input 幾個欄位, 它就會算出這些欄位值的 Check Sum (整數)
所以只要比對 Check Sum 是否相等, 即可知道是否有異動

用法如下

SELECT * FROM TableA, TableB
WHERE TableA.ID = TableB.ID
AND BINARY_CHECKSUM(TableA.Col1,TableA.Col2) <>
BINARY_CHECKSUM(TableB.Col1,TableB.Col2)

參考 TechNet

取得SQL Sever 剛產生的自動編號

如果在SQL Server 中
使用自動編號欄位(int ,自動識別)當Pkey
常會遇到Insert Data 後,要再Update Data 時
不知道Pkey 是多少

剛好查到了一個方法可以使用

SELECT @@Identity

這個語法可以查出這次Connection 中最後產生的自動編號

Where條件區分大小寫(T-SQL)

最近查了一下 SQL Server 中如何區分文字(英文)資料的大小寫
所以記錄一下 :

　　重點就是要在 Where 條件的欄位後面加上
　　Collate SQL_Latin1_General_CP1_CS_AS
　　(CS 代表 Case Sensitive)

範例 :

　　SELECT * FROM (
　　　SELECT 'Test' AS Col1
　　) AS A
　　WHERE Col1 Collate SQL_Latin1_General_CP1_CS_AS = 'test'

[參考TechNet]

Database 分頁

之前試了一下怎麼用 SQL Statement 做分頁
試出了下面的方式, 所以記錄一下

[Oracle DB]
PageSize就是一頁的資料筆數
如果希望一頁有10筆資料, PageSize 就代入10

SELECT *
FROM (
   SELECT
   FLOOR((ROWNUM-1)/PageSize)+1 AS PAGE
   ,A.*
   FROM OracleTable AS A
)
WHERE PAGE = 1

[SQL Server 2005]
UpdateDate是用來排序列出資料編號的欄位
PageSize就是一頁的資料筆數
如果希望一頁有20筆資料, PageSize 就代入20

SELECT *
FROM (
   SELECT
   (ROW_NUMBER() OVER (ORDER BY UpdateDate)-1)/PageSize+1 AS Page
   ,A.*
   FROM SQLTable AS A
)
WHERE Page = 1