Suppressing/Bypassing Triggers in SQL Server 2005 onwards

Sometimes being DBA you need to perform certain activities where we do not want that triggers should fire. for example in most of the cases where we are doing some kind of datafix we do not want unnecessary audit data generated. When we are not working in 24/7 environment its very easy we can drop/disable the trigger in off hours and then do the operation, but in 24/7 environment you do not have this luxury. So we need a mechanism to bypass the trigger action. In SQL server this action can be performed by utilizing CONTEXT_INFO session level setting. Let's take a UPDATE trigger example from one of my previous post.


We can modify the update trigger as shown below.

         ALTER TRIGGER update_trigger
         ON trigger_test1
         FOR UPDATE AS
         BEGIN
          IF CONTEXT_INFO() = 0x545
          RETURN
         
          UPDATE a
          SET value2= i.value1*2
          FROM trigger_test1 a
          JOIN INSERTED i
          ON a.id = i.id
         END
         GO

Now If I want to perform UPDATE on the trigger_test1 table and I do not want trigger to fire, the only thing I need to do is to put SET CONTEXT_INFO statement in the beginning.


          SET CONTEXT_INFO 0x545

          UPDATE trigger_test1 SET value1 = 30 WHERE ID = 5

If You want to know how to perform testing on triggers then go through this article.

Performing Unit Tests on Triggers

Testing triggers from DB side or how to do unit testing of triggers without even going on the front-end.

People often ask me that without having real world data, how can we test these. The answer is that for performing any kind of unit testing we do not require to actually commit the data that we are playing with.

So in this article I will talk about how to perform a unit test for INSERT, DELETE and UPDATE triggers without even actually creating extra data/modifying existing data. For achieving the end result I am going to use TRANSACTION CONTROL (BEGINTRAN /ROLLBACK) and NOLOCK dirty read concept. Also I here I am just talking about method so my test cases very straight forward; but how much complicated the triggers are these approaches would work.

To explain how to perform unit test case for each action, I am going to use below two tables and trigger scripts. I have also included the script to populate the data.

-- main table

     CREATE TABLE trigger_test1

(

id int primary key,

value1 int,

value2 int

)

-- audit table

   CREATE TABLE trigger_test2

(

id int,

value1 int,

value2 int

)

-- INSERT trigger

CREATE TRIGGER insert_trigger

ON trigger_test1

FOR INSERT AS

BEGIN

UPDATE a

SET value2= i.value1*2

FROM trigger_test1 a

JOIN INSERTED i

ON a.id = i.id

END

GO

-- UPDATE trigger

CREATE TRIGGER update_trigger

ON trigger_test1

FOR UPDATE AS

BEGIN

UPDATE a

SET value2= i.value1*2

FROM trigger_test1 a

JOIN INSERTED i

ON a.id = i.id

END

GO

-- DELETE trigger

CREATE TRIGGER delete_trigger

ON trigger_test1

FOR DELETE AS

BEGIN

INSERT INTO trigger_test2

SELECT * FROM DELETED

END

GO

--inserting test values

INSERT INTO trigger_test1 (id, value1) SELECT 1,10

INSERT INTO trigger_test1 (id, value1) SELECT 2,10

INSERT INTO trigger_test1 (id, value1) SELECT 3,10

INSERT INTO trigger_test1 (id, value1) SELECT 4,10

INSERT INTO trigger_test1 (id, value1) SELECT 5,10

GO

INSERT trigger test method:

Following script can be used for performing INSERT test case.



BEGIN TRAN

/* to check initial state of the data
(you can have where condition as per your requirement)
HERE having "nolock" is important to make
a dirty read within transaction
*/
SELECT * FROM trigger_test1 with (nolock) WHERE ID = 6

INSERT INTO trigger_test1 (id, value1) SELECT 6,60

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 6

ROLLBACK

DELETE trigger test method:

Following script can be used for performing DELETE test case.

BEGIN TRAN

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

DELETE FROM trigger_test1  WHERE ID = 5

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

ROLLBACK

UPDATE trigger test method:

Following script can be used for performing UPDATE test case.

BEGIN TRAN

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 5

UPDATE trigger_test1 SET value1 = 30 WHERE ID = 5

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 5

ROLLBACK

Finally suppose if we happen to have both INSERT and DELETE triggers implemented then these both can be tested through one SQL as depicted below. This approach is also has two more benefits that you can use existing data sitting within same table for INSERT trigger testing. The trick is to USE temporary table.

BEGIN TRAN

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

SELECT * INTO #tmp FROM trigger_test1  WHERE ID = 5

DELETE FROM trigger_test1  WHERE ID = 5

INSERT INTO trigger_test1  SELECT * FROM #tmp

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

DROP TABLE #tmp

ROLLBACK

RESTORE Arguments - SQL Server Backup

RESTORE HEADERONLY. This statement provides details regarding the backup database and returns all the backup's header information. 


RESTORE LABELONLY. This statement returns a result set containing information about the backup media on which the backup is stored. The output of this statement includes only partial header information.


RESTORE VERIFYONLY. This statement verifies whether the backup data is complete. 


RESTORE FILELISTONLY. This statement returns a list of data files and log files in the backup. 

sp_help_jobactivity - Check Run Time Job Activity

sp_help_jobactivity system stored procedure provides information about the runtime state of SQL Server Agent jobs. This SP can be executed to check state of particular Job by passing value to either @job_id or @job_name parameter.


This SP has following parameters

Parameter_name	Type
@job_id	uniqueidentifier
@job_name	sysname
@session_id	int

This SP can be used along with sp_help_jobhistory to implement generic notification process for all Jobs.

sp_help_jobhistory - Check failing Jobs

sp_help_jobhistory system stored procedure is used to view the history of jobs. For checking the history of all Jobs we can execute below script.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
GO

We can also execute this SP to check for only failing Jobs by providing @run_status parameter. Below SP call would provide the list of Jobs with steps details which failed to get execute. Specifying a value of 0 for the @run_status parameter returns history for all failed jobs.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
@run_status = 0;
GO

Specifying a value of 1 for the @run_status parameter returns history for all jobs that completed successfully.


Specifying a value of 2 for the @run_status parameter returns history for all jobs steps that have a retry status.


Specifying a value of 3 for the @run_status parameter returns history for all jobs that were canceled.


This SP can also be executed with @Job_id /@Job_name parameters too to check for particular Job's history. If we do not pass any value to these parameters this SP runs for all Jobs.


There are more parameters to this SP, which I don't think are that much handy then the above 3 described though I have provided the list of all parameters below.

Parameter_name	Type
@job_id	uniqueidentifier
@job_name	sysname
@step_id	int
@sql_message_id	int
@sql_severity	int
@start_run_date	int
@end_run_date	int
@start_run_time	int
@end_run_time	int
@minimum_run_duration	int
@run_status	int
@minimum_retries	int
@oldest_first	int
@server	sysname
@mode	varchar

Exception from HRESULT: 0xC0010014(Microsoft.SqlServer.DTSRuntimeWrap)

I was working on setting up Maintenance Plan for backup on newly installed SQL Server 2005. On this server we installed only database engine services originally but not Integration Services (SSIS), and then later decided to add this service too. While editing backup task on this new Server, I was facing issue with Exception from HRESULT: 0xC0010014(Microsoft.SqlServer.DTSRuntimeWrap) as shown below.

To resolve this issue for 32-bit installation, you can perform below steps (basically register couple dll's which got messed up due to sequence of installation)

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll"

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll



and for 64 bit installation

%windir%\system32\regsvr32 "%ProgramFiles%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\system32\regsvr32 "%ProgramFiles%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

Aggregates... Some Times they become brain teasers...

One Day ... I was sitting with one of my team mate.. He just asked me casually what could be the value of SUM aggregate on a table containing no row...
to confuse me more he ran ....

SELECT COUNT(1) FROM @tmp

The result was zero (0)...


Then he changed the SQL to...

SELECT SUM(1) FROM @tmp

I was little confused; Can any body be able to solve my confusion... make sure you guess the answer without executing the above query...

USING sp_spaceused FOR Multiple tables

Using sp_spaceused we can only find statistics for particular table within database in SQL Server. But there is way by which we can USE sp_spaceused for multiple tables too. Actually the fact is that it can be used for all tables and for multiple/all databases. Following script can help you to get space used by all tables in particular database. I will update this script for all/multiple databases soon (hint: use sp_foreachdb along with this scipt to achieve the result) .

-- Declare variables

DECLARE @Table_Size TABLE

(

[Table_Name] NVARCHAR(200)

, [Number_of_Rows] NVARCHAR(100)

, [Reserved_Space] NVARCHAR(100)

, [Data_Space] NVARCHAR(100)

, [Index_Size] NVARCHAR(100)

, [Unused_Space] NVARCHAR(100)

)

DECLARE @Table_Name NVARCHAR(200)

-- Select first table

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

-- loop thorugh rest of the tables

WHILE ( @@ROWCOUNT != 0 )

BEGIN

INSERT INTO @Table_Size

EXEC sp_spaceused @objname = @Table_Name

PRINT @Table_Name

SELECT TOP 1 @Table_Name = (TABLE_SCHEMA + '.' + TABLE_NAME )

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND @Table_Name < (TABLE_SCHEMA + '.' + TABLE_NAME)

ORDER BY (TABLE_SCHEMA + '.' + TABLE_NAME)

END

-- Display end results

SELECT * FROM @Table_Size ORDER BY Table_Name

OPENROWSET Example

A simple example for using OPENROWSET in SQL Server 2005 to access another SQL Server is goes as .....


SELECT * FROM OPENROWSET('SQLOLEDB',
'';;'',
'SELECT *
FROM .dbo.')

The SELECT within OPENROWSET can have where clause also. There is benefit associated also, sometimes you can access same data using Linked server strategy also; but in that case WHERE gets applied only when data is tranferred to destination. WHEREAS in case of OPENROWSET where gets applied on SOURCE itself.

Sharing My SQL Experience - The way to learn more

I would be sharing my knowledge about SQL Server in this place. The Knowledge that i have acquired over the span of last 4-5 years. I would be sharing solutions to the problems I faced over these years. Would be writing about topics that I like.
Would try to server SQl commnuity as much as I can. Over the time period I would add new authors who can write on SQL Server topics.
I am going to start this because I know when you share your thoughts it increases your knowledge, changes your though process. Lets hope serving the community brings out the best from my side.