T-SQL 民國日期欄位(varchar) 轉 西元日期

b.edit_date格式為yy/mm/dd (民國)

convert(varchar,convert(int,substring(b.edit_date,1,charindex('/',b.edit_date)-1))+1911)+
'/'+
substring(b.edit_date,charindex('/',b.edit_date)+1,datalength(b.edit_date))

sp_who2 and dbcc inputbuffer

use [xxx]
go
sp_who2

------------------------------------------------------------------------
declare @sql_handle varbinary(64)
select @sql_handle = sql_handle from master..sysprocesses where spid=66

select dbid, objectid, text from ::fn_get_sql(@sql_handle)


------------------------------------------------------------------------
dbcc inputbuffer(66)

找出MS SQL Server的內含文字 (同Oracle v$user_source)

http://www.select-sql.com/mssql/search-text-in-triggers-and-store-prodedures.html

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'Employee', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
end as [Object Type],syscomments.text
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))

取得 SQL Server 資料庫正在執行的 T-SQL 指令與詳細資訊

http://blog.miniasp.com/post/2010/10/13/How-to-get-current-executing-statements-in-SQL-Server.aspx

今天有個客戶的資料庫發生了異常的鎖定狀況，但並不是鎖死 (Deadlocked) 的情況，所以必須要進一步分析才能知道問題的主因，不過在分析的過程中卻遇到了一個問題，那就是雖然知道是哪一條連線(SPID)出問題，但卻不知道應該 如何快速查出這條有問題的連線實際執行的 T-SQL 為何，唯有查出這條 T-SQL 實際執行的內容我才能找出應用程式中的哪一段程式出錯，這個技巧算是非常實用喔！
我們若用 sp_who2 這個系統預儲程序可以查出所有連線的狀況，也可以看到該連線被卡住 (Blocked) 的狀況，不過 Command 這個欄位卻只有查詢的摘要，看不出完整的查詢命令為何：

這 時我們可以利用 SQL Server 2005 以上都有支援的幾個 DMVs (Dynamic Management Views) 來查詢 SQL Server 資料庫中即時的運作資訊，當然也包括正在執行的完整 T-SQL 命令句，技術細節我就留在本文最後的相關連結讓各位自行研究，以下是我的研究成果：

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
    ltrim(q.text),
    r.statement_start_offset/2+1,
    (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
   LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

相信是非常實用的一句 T-SQL，全中文的喔！ ^__^     ( 點下圖可放大 )

相關連結

• Execution Related Dynamic Management Views and Functions (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.dm_exec_sql_text (Transact-SQL)
• Use Undocumented SP_WHO2 Procedure Instead of SP_WHO for SQL Server 7.0
• Microsoft SQL Server - Wikipedia, the free encyclopedia
• SQL SERVER – 2005 – List all the database
• TSQL to get current executing statements - SQL Server 2005

SQL Server 時間函數

http://www.dugukeji.com/mb/biancheng/Mssql/mssql_24228.html

Sql Server中的日期與時間函數：

1. 當前系統日期、時間
select getdate()
2. dateadd 在向指定日期加上一段時間的基礎上，返回新的 datetime 值：
例如：向日期加上2天
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000
3. datediff 返回跨兩個指定日期的日期和時間邊界數：
select datediff(day,'2004-09-01','2004-09-18') --返回：17
4. datepart 返回代表指定日期的指定日期部分的整數：
SELECT DATEPART(month, '2004-10-15') --返回 10
5. datename 返回代表指定日期的指定日期部分的字符串：
SELECT datename(weekday, '2004-10-15') --返回：星期五
6. day(), month(),year() --可以與datepart對照一下：

select 當前日期=convert(varchar(10),getdate(),120) ,當前時間=convert(varchar(8),getdate(),114) select datename(dw,'2004-10-15') select 本年第多少周=datename(week,'2004-10-15') ,今天是周幾=datename(weekday,'2004-10-15')

函數 參數/功能

GetDate( ) 返回系統目前的日期與時間 DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 與date1兩個日期之間的差值 date2-date1 DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期 DatePart (interval,date) 返回日期date中，interval指定部分所對應的整數值 DateName (interval,date) 返回日期date中，interval指定部分所對應的字符串名稱

參數 interval的設定值如下：

值 縮 寫（Sql Server） (Access 和 ASP) 說明 Year Yy yyyy 年 1753 ~ 9999 Quarter Qq q 季 1 ~ 4 Month Mm m 月1 ~ 12 Day of year Dy y 一年的日數,一年中的第幾日 1-366 Day Dd d 日，1-31 Weekday Dw w 一周的日數，一周中的第幾日 1-7 Week Wk ww 周，一年中的第幾周 0 ~ 51 Hour Hh h 時0 ~ 23 Minute Mi n 分鐘0 ~ 59 Second Ss s 秒 0 ~ 59 Millisecond Ms - 毫秒 0 ~ 999

　　access 和 asp 中用date()和now()取得系統日期時間；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，這些函數的用法也類似。 舉例：

1.GetDate() 用于sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值為 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值為 5 天
3.DatePart('w','2005-7-25 22:56:32')返回值為 2 即星期一(周日為1，周六為7)
DatePart('d','2005-7-25 22:56:32')返回值為 25即25號
DatePart('y','2005-7-25 22:56:32')返回值為 206即這一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值為 2005即2005年



--系統時間45分鐘前
select dateadd("MINUTE",-45,getdate())

查詢sql server tables names/columns names

select *
  from bigsun_human.sys.tables;

select *
  from bigsun_human.sys.columns;


甚麼column在甚麼table
select b.name table_name,a.name column_name
  from  bigsun_human.sys.columns a,bigsun_human.sys.tables b
 where a.object_id = b.object_id

SQL Server 2008 建立 db link 到 2000，建立時OK，但下SQL出現錯誤 的解決方式

連結伺服器 "172.16.1.20" 的 OLE DB 提供者 "SQLNCLI10" 傳回訊息 "無法指出的錯誤"。
連結伺服器 "172.16.1.20" 的 OLE DB 提供者 "SQLNCLI10" 傳回訊息 "無法在伺服器中找到完成此操作所須的預存程序。請連絡您的系統管理員。"。
訊息 7311，層級 16，狀態 2，行 1
無法為連結伺服器 "172.16.1.20" 的 OLE DB 提供者 "SQLNCLI10" 取得結構描述資料列集 "DBSCHEMA_TABLES_INFO"。提供者支援介面，但在使用時傳回失敗碼。


經過測試，如果在sql server 2008 建立ODBC，之後在sql server利用ODBC建立db link，下SQL就可以避免上述error