建置
想使用 SQL Server的全文檢索功能，先去[控制台][系統管理工具][服務]看以下兩項服務是否有啟動：
1. SQL Full-Text Filter Daemon Launcher(MSSQLSERVER)：建立 fdhost.exe處理序的服務，這是主控處理全文檢索索引之文字資料的斷詞工具與篩選的必要項目。
2. SQL Server Agent(MSSQLSERVER)：透過 SQL Server Agent作業的排程，啟動全文檢索－索引母體的擴展，累加資料表母體擴展。
如果沒有啟動第一項服務(SQL Full-Text...)，直接進行 Select全文查詢，會出現錯誤訊息如下：
SQL Server在與全文篩選器的後台程序宿主(FDHOST)的進程通信時遇到錯誤0x80070218。
請確保 FDHOST進程正在運行。若要重新啟動FDHOST進程，請運行 sp_fulltext_service 'restart_all_fdhosts' 的命令或重新啟動 SQL Server實例。
參考網址：
http://blog.xuite.net/sugopili/computerblog/76684376-SQL+Server%E5%85%A8%E6%96%87%E6%AA%A2%E7%B4%A2%E5%B0%8F%E6%8A%80%E5%B7%A7
使用 & 設定
設定
看網址
使用
設定全文檢索並以建立索引後，就可以透過條件 WHERE CONTAINS(欄位,搜尋的字)的方式，
來做全文檢索的查詢方式，下面範例程式碼， 依序介紹平常使用Like及全文檢索下CONTAINS用法。
參考網址：
http://www.dotblogs.com.tw/dotjum/archive/2009/08/01/9796.aspx
'更新
If UW.WU.IsNonEmptyFromQueryStringOrForm("Refresh") Then
System.Web.HttpContext.Current.Cache.Remove(CacheName)
End If
'快取部分
If obj Is Nothing Then
Dim SQL As String = ""
SQL = "select * from Product with (nolock)"
obj = UW.SQL.DTFromSQL(SQL)
Dim AC As New AggregateCacheDependency
Dim DepArray() As CacheDependency = { _
New SqlCacheDependency(UW.SQL.sqlCacheDependencyName, "Product"), _
New SqlCacheDependency(UW.SQL.sqlCacheDependencyName, "Product_Category") _
}
AC.Add(DepArray)
Dim FD As New CacheDependency(UW.Template.TemplateRoot & "2013UI\Product\DirectBuyAllProduct.html")
AC.Add(FD)
System.Web.HttpContext.Current.Cache.Insert(CacheName, obj, AC)
End If
-- To allow advanced options to be changed.開啟進階選項
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.執行動作
RECONFIGURE
GO
-- To enable the feature.開啟xp_cmdshell功能
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.執行動作
RECONFIGURE
GO
DECLARE @DBPath nvarchar(120)
--指定磁碟機Z的路徑為\\192.168.8.201\SQLBackupLeon
exec master..xp_cmdshell 'net use z: \\192.168.8.201\SQLBackupLeon 密碼 /user:帳號'
--指定備份路徑檔名
SET @DBPath = 'Z:\' + 'ReikoTEST' + '_' + Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','') + '.bak'
--DATENAME(Weekday,GETDATE())=>會顯示為"星期N"
--SET @DBPath = 'Z:\' + 'Leon' + '_' + DATENAME(Weekday,GETDATE()) + '.bak'
--備份資料庫ReikoTEST到路徑檔名
BACKUP DATABASE ReikoTEST TO DISK = @DBPath
--刪除磁碟機Z
exec master..xp_cmdshell 'net use Z: /delete'
GO
-- To enable the feature.關閉xp_cmdshell功能
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.執行動作
RECONFIGURE
GO
-- To allow advanced options to be changed.關閉進階選項
EXEC sp_configure 'show advanced options', 0
GO
-- To update the currently configured value for advanced options.執行動作
RECONFIGURE
GO
/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('DBName', 'Collation')
GO
/* Find Collation of SQL Server Database Table Column */
USE sample
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (
SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TableName')
AND name = 'ColumnName'
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/26/2008 09:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who3]
(
@filter tinyint = 1,
@filterspid int = NULL
)
AS
SET NOCOUNT ON;
DECLARE @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime,
current_statement_parent xml,
current_statement_sub xml)
INSERT INTO @processes
SELECT sub.*
FROM
(
SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch,
(
SELECT
LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE
) AS parent_text,
(
SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE
) AS child_text
FROM sys.sysprocesses sp WITH (NOLOCK) LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY
sub.spid
-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid
-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51
OR spid = @@SPID
-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0
SELECT spid,
blocked,
databasename,
hostname,
loginame,
status,
current_statement_parent,
current_statement_sub,
cmd,
cpu,
physical_io,
program_name,
login_time,
last_batch
FROM @processes
ORDER BY spid
RETURN 0;
DECLARE @Table TABLE(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
loginame varchar(256),
status varchar(60),
current_statement_parent xml,
current_statement_sub xml,
cmd varchar(128),
cpu int,
physical_io int,
program_name varchar(256),
login_time datetime,
last_batch datetime
)
INSERT INTO @Table EXEC sp_who3
SELECT *
FROM @Table
WHERE databasename= 'Leon' and cmd = 'AWAITING COMMAND'
and CAST(current_statement_parent AS nvarchar(max)) not LIKE '%AspNet_SqlCachePollingStoredProcedure%'
order by CAST(current_statement_parent AS nvarchar(max))
必備元件
Microsoft .NET Framework 3.5 Service pack 1(不裝連安裝畫面都無法開啟)
http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=zh-tw
Windows Installer 4.5 Redistributable - 繁體中文(不安裝就無法安裝SQL Server 2008)
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-tw&FamilyID=5a58b56f-60b6-4412-95b9-54d056d6f9f4
PowerShell 1.0(不裝在檢查元件步驟就無法通過)
926139：Windows PowerShell 1.0 英文語言安裝封裝適用於 Windows Server 2003 及 Windows XP
926140：Windows Server 2003 Service Pack 1 與 Windows XP Service Pack 2 的 Windows PowerShell 1.0 當地語系化安裝套件
926141：Windows 1.0 PowerShell 多語系使用者語言介面 (MUI) Pack 適用於 Windows Server 2003 或適用於 Windows XP
928439：適用於 Windows Vista 的 Windows PowerShell 1.0 安裝封裝