Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

SQL Server – Set Options – Detecting Current Settings

Background

Set options can have devastating effect on SQL Server Operations.  I am too shamed to talk about once it bit me terribly.

Other times, setting one or a couple of them helped us.

Setting XACT_ABORT on is very important when changing data within Stored Procedure.

Setting TRANSACTION ISOLATION LEVEL  to “read uncommitted” could possibly help address data contention issues.

Set NO_EXEC ON is helpful when writing DDL packages and we need to skip existing packages.

 

Code

Declare Helper Objects

Views

dbmgmt.vw_UserOptions

Guide

Based on Microsoft’s documentation, here are the user options.


use [master]
go

if schema_id('dbmgt') is null
begin

	exec('create schema [dbmgt] authorization [dbo]; ')
	
end
go

if object_id('[dbmgt].[vw_UserOptions]') is null
begin

	exec('create view [dbmgt].[vw_UserOptions] as select [shell] = 1/0 ')

end
go

alter  view [dbmgt].[vw_UserOptions]
as

select
	  DISABLE_DEF_CNST_CHK    = 1
	, IMPLICIT_TRANSACTIONS   = 2
	, CURSOR_CLOSE_ON_COMMIT  = 4
	, ANSI_WARNINGS		  = 8
	, ANSI_PADDING		  = 16
	, ANSI_NULLS		  = 32
	, ARITHABORT		  = 64
	, ARITHIGNORE		  = 128
	, QUOTED_IDENTIFIER	  = 256
	, NOCOUNT		  = 512
	, ANSI_NULL_DFLT_ON	  = 1024
	, ANSI_NULL_DFLT_OFF	  = 2048
	, CONCAT_NULL_YIELDS_NULL = 4096
	, NUMERIC_ROUNDABORT	  = 8192
	, XACT_ABORT		  = 16384

go


 

Query

List information on current user sessions

Explanation

Query sys.dm_exec_sessions to get session setting for all existing connections.

Code

declare @mycomputer sysname
declare @sessionID  int

set @mycomputer = HOST_NAME()
--set @sessionID = @@SPID

select 

		  tblES.session_id
	    , tblES.login_name
		, tblES.host_name
		, tblES.program_name
		, tblES.prev_error

		/*
			Transaction isolation level of the session.
			0 = Unspecified
			1 = ReadUncomitted
			2 = ReadCommitted
			3 = Repeatable
			4 = Serializable
			5 = Snapshot
		*/
		, [transactionIsolationLevelLiteral]
			= case tblES.transaction_isolation_level
					when 0 then 'Unspecified'
					when 1 then 'ReadUncomitted'
					when 2 then 'ReadCommitted'
					when 3 then 'Repeatable'
					when 4 then 'Serializable'
					when 5 then 'Snapshot'
					else cast(tblES.transaction_isolation_level as sysname)
			  end	

		, [sessionStatus]
			= tblES.[status]

		, [language]
			= tblES.[language]

		, [Date Format]
			= tblES.[date_format]		

		, [Ansi Defaults]
			= case tblES.ansi_defaults
					when 0 then 'No'
					else 'Yes'
			  end

		, [quoted Identifier]
			= case tblES.quoted_identifier
					when 0 then 'No'
					else 'Yes'
			  end

	

from   sys.dm_exec_connections tblSC

inner join sys.dm_exec_sessions tblES

			on tblSC.session_id = tblES.session_id

where tblES.[is_user_process] = 1

and   tblES.host_name
		 = isNull(@mycomputer, tblES.host_name)

and   tblES.session_id 
		= isNull(@sessionID, tblES.session_id )




Output

listUserSessions

 

Explanation
  1. Microsoft’s .Net application’s default Transaction Isolation level is Serializable
  2. SQL Server Agent connections have quoted identifier set to off

 

Query @@OPTIONS

Explanation

In the sample below, we query @@OPTIONS to get the current session’s settings.

We then issue a bitwise and (&) to see if we achieve the specific options value.

 

Code

declare @option int

set @option = @@options

select 
		  [@@optionsAsDecimal] = @option

		, [quotedIdentifier] 
				= case @option & vwUO.QUOTED_IDENTIFIER
						when vwUO.QUOTED_IDENTIFIER then 'Yes'
						else 'No'
				  end	

		, [nocount] 
				= case @option & vwUO.[nocount]
						when vwUO.[nocount] then 'Yes'
						else 'No'
				  end	


		, [xact_abort] 
				= case @option & vwUO.XACT_ABORT
						when vwUO.XACT_ABORT then 'Yes'
						else 'No'
				  end	

		, [arith_abort] 
				= case @option & vwUO.ARITHABORT
						when vwUO.ARITHABORT then 'Yes'
						else 'No'
				  end	

from  [master].[dbmgt].[vw_UserOptions] vwUO


Output:

@@Options

 

References

  1. Set Statements
  2. @@OPTIONS
  3. User Options Option