SQL SERVER欄位資料類型為Timestamp的使用範例

Timestamp說明：

Timestamp為SQL SERVER資料庫內自動產生的唯一二進位數字的資料類型，Timestamp通常用來作為版本戳記資料表資料列的機制，簡單來說就是ROW的版本控管，大小為8位元組。只要資料有更新或新增Timestamp的內容就會遞增。

Timestamp的應用情境：

若有兩個人同時在應用程式編輯同一筆資料，使用Timestamp可以防止更新被覆蓋。

Timestamp使用範例：

(1)新增資料:

Timestamp是SQL SERVER會自動產生，不用特別匯入。

USE [TestDataTypeDB]
GO
--建立測試timestamp的資料表
CREATE TABLE [dbo].[TestTable](
 [nid] [int] NOT NULL,
 [timeid] [timestamp] NOT NULL,
 datetimes DATETIME2 not NULL
) ON [PRIMARY]

GO
--插入兩筆資料(timestamp是SQL SERVER會自動產生，不用特別匯入)
INSERT INTO [dbo].[TestTable](nid,datetimes)
VALUES(1,GETDATE()),(2,GETDATE())
GO
--檢視結果
SELECT * FROM [dbo].[TestTable]
GO

--更新一筆資料，資料版本會改變
UPDATE [dbo].[TestTable]
SET datetimes=GETDATE()+1
WHERE nid = 1
GO
--檢視結果
SELECT * FROM [dbo].[TestTable]
GO

--插入一筆新資料，新資料版本會是最新的版本
INSERT INTO [dbo].[TestTable](nid,datetimes)
VALUES(3,GETDATE())
GO
SELECT * FROM [dbo].[TestTable]
GO

(2)資料匯入：

在使用SSIS或是匯入匯出精靈轉入資料時，如果沒有略過資料類型為Timestamp的欄位會產生資料轉檔錯誤。

我們將資料類型為Timestamp的資料行忽略，此時就可以成功匯入資料內容到目的端了。

搞定收工

如何設定當job執行失敗時sql server自動寄發mail給管理者2

問題：之前的文章有提到SQL JOB失敗時寄發的EMAIL訊息太過簡單而無法讓管理者知道SQL JOB失敗的詳細原因，相信也有許多人也有跟我一樣的問題。

解決：為了解決使用SQL JOB搭配OPERATOR所寄發的EMAIL錯誤訊息太過簡單這個問題，我不使用OPERATOR，而是在SQL JOB新增一個STEP，而這個STEP會取得SQL JOB執行失敗的詳細資訊，所以當SQL JOB執行錯誤時，會跳到這個STEP，如此一來管理者就能在EMAIL上面取得SQL JOB執行失敗的原因了。

--JOB失敗的詳細內容

--1.使用msdb的系統資料表取得執行JOB的詳細結果

SELECT TOP 1

   J.name,

   J.[description],

   S.step_id,

   S.step_name,

   H.run_date,

   H.run_time, 

   H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND [name]='InsertError'

ORDER BY h.instance_id DESC

--2.使用FOR XML PATH把內容轉成TR與TD的XML格式

SELECT TOP 1

   td =J.name,'',

   td =J.[description],'',

   td =S.step_name,'',

   td =H.run_date,'',

   td =H.run_time,'',

   td =H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND [name]='InsertError'

ORDER BY h.instance_id DESC

FOR XML PATH('tr')

--2.把執行JOB的結果轉成HTML格式

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    CAST ( ( SELECT TOP 1

               td =J.name,'',

               td =J.[description],'',

               td =S.step_name,'',

               td =H.run_date,'',

               td =H.run_time,'',

               td =H.[message]

            FROM msdb..sysjobhistory H

            INNER JOIN msdb..sysjobsteps S

            ON H.step_id = S.step_id AND H.job_id = S.job_id

            INNER JOIN msdb..sysjobs J

            ON J.job_id = H.job_id

            WHERE  h.run_status = 0 AND [name]='InsertError'

            ORDER BY h.instance_id DESC

            FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'' ;

--將SQL JOB錯誤訊息寄給指定的管理員EMAIL帳號

EXEC msdb.dbo.sp_send_dbmail @recipients='ryotest@gmail.com',

    @subject = 'SQL Job Error Report',

    @body = @tableHTML,

    @profile_name = 'dba',

    @body_format = 'HTML' ;

--3.在JOB上設定當JOB執行失敗會自動寄發JOB執行失敗的詳細結果。

--圖.1 設定一個新的STEP:寄發SQL JOB執行錯誤的詳細訊息給管理者，其功能為當SQL JOB執行失敗時寄送錯誤訊息給特定的EMAIL帳號

--圖.2 在寄發SQL JOB執行錯誤的詳細訊息給管理者這個STEP，成功時的動作設定結束回報失敗的作業

--圖.3 新增一個StepInsertError，引發提供的數值數量與資料表定義不相符這個錯誤

--圖.4 將StepInsertError的失敗時的動作設定為寄發SQL JOB執行錯誤的詳細訊息給管理者

--圖.5 設定完成的SQL JOB的STEP清單

--圖.6 停用OPERATOR

--4.測試結果

如何查看執行失敗的JOB清單以及其詳細訊息

問題：有甚麼方法可以快速查看執行失敗的JOB清單以及其詳細訊息呢？

解答：可以使用msdb的sysjobhistory、sysjobsteps與sysjobs，下表為簡略的說明。

SQL Server Agent Tables
sysjobhistory	Contains information about the execution of scheduled jobs by SQL Server Agent
sysjobsteps	Contains the information for each step in a job to be executed by SQL Server Agent
sysjobs	Stores the information for each scheduled job to be executed by SQL Server Agent

--如何取得失敗JOB的詳細錯誤訊息

DECLARE @NumDays INT

DECLARE @FinalDate VARCHAR(8)

SET @NumDays=-1

set @FinalDate=CONVERT(VARCHAR(8), (SELECT DATEADD (DAY,(@NumDays), GETDATE())), 112) 

SELECT

   J.name,

   J.[description],

   S.step_id,

   S.step_name,

   H.run_date,

   H.run_time,  

   H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND run_date >=  @FinalDate

ORDER BY h.instance_id DESC

--結果

參考網址:
Failed SQL Server Agent Jobs
SQL Server Agent Tables

如何移動TEMPDB

--1.查看tempdb DB的位置

USE tempdb

GO

SP_HELPFILE;

--2.執行下列語法

USE MASTER

GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, FILENAME = 'D:\TempDB\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (name = templog, FILENAME = 'D:\TempDB\templog.ldf')

GO

--3.重新啟動SQL SERVER

--4.查看結果

USE tempdb

GO

SP_HELPFILE;

如何把SELECT的結果利用EMAIL傳送出去

--0.Table內容

SELECT UserID,UserNames FROM dbo.TBAccounts

--1.使用FOR XML PATH將SELECT的內容轉成TR與TD

SELECT td = UserID,'',

       td = UserNames, ''

FROM TBAccounts

FOR XML PATH('tr')

--2.編輯HTML組成TABLE的格式

--3.將結果寄出

EXEC msdb.dbo.sp_send_dbmail @recipients='ryotest@gmail.com',

    @subject = 'Work User List Report',

    @body = @tableHTML,

    @profile_name = 'dba',

    @body_format = 'HTML' ;

--4.確認結果

訊息2760，層級16，狀態1，程序OOXX，行3 指定的結構描述名稱"dbo" 不存在，或您沒有使用它的權限。

--建立測試的帳號與權限

USE [master]

GO

--1.建立LOGIN

CREATE LOGIN ryo2011 WITH PASSWORD=N'ryo@2011', DEFAULT_DATABASE=DemoPermission

, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE DemoPermission

GO

--2.建立USER

CREATE USER ryo2011 FOR LOGIN ryo2011

--3.設定USER預設的SCHEMA為dbo

ALTER USER ryo2011 WITH DEFAULT_SCHEMA=[dbo]

--4.授權讀的權限，可以讀取所有的Table。

EXEC sp_addrolemember N'db_datareader', N'ryo2011'

--5.授權建立VIEW、PROC、FUNCTION與執行PROC的權限

GRANT CREATE VIEW TO ryo2011;

GRANT CREATE PROC TO ryo2011;

GRANT CREATE FUNCTION TO ryo2011;

GRANT EXEC TO ryo2011;

--使用測試帳號登入並執行CREATE VIEW的指令後會有錯誤訊息如下；

訊息2760，層級16，狀態1，程序testview，行3

指定的結構描述名稱"dbo" 不存在，或您沒有使用它的權限。

--切回到SA，給予ryo2011擁有GRANT ALTER ON SCHEMA的權限

GRANT ALTER ON SCHEMA::dbo TO ryo2011;

--再次使用測試帳號登入並執行CREATE VIEW的指令後執行指令成功

--使用測試帳號登入並測試SELECT的指令

--搞定收工