Using IF EXISTS

When I work with stored procedures, I will always test if the procedure exists, drop if it does exist, and then create the stored procedure. This produces nice clean code which can be saved to source control & run at any time as needed. Note that the create code includes the grant permissions.

I had been using "the long version" of the IF EXISTS statement, for example:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME' AND [type] = N'P'
)

This does work just fine, but MSSQL does not allow more than one schema-scoped object (table, view, sp, udf) with the same name. So, testing for the type is not necessary. So we could use just this:

IF EXISTS (
SELECT * FROM sys.sysobjects
WHERE [name] = 'TESTME'
)

However, there is a unique ID for every object & a built-in function OBJECT_ID() to get that ID, so this is all we need to test if an object exists:

IF OBJECT_ID('TESTME') IS NOT NULL

External Change Tracking

This article relates to my previous post that describes a change-tracking system for MS-Access. You can view the complete code with comments here: Change Tracking in Access

In a very active database, the tracking table can become quite large, so here are two methods to move the tracking table to a separate file.

Remember - always make a backup copy before making significant changes to your database.

Method 1

Create a new blank database.
Import the tracking table from the original file.
Delete the tracking table from the original file.
In the original file, create a link to the table in the new file.

...all of the above can be done using the Access GUI.

Method 2

This module will create a new file in the same folder as the original file, move the tracking table and link to it. You would run this only one time...

Sub External_Change_Tracker()
Const MyTable = "tbl__ChangeTracker"
'
Dim dst As DAO.Database
Dim MyNewFile As String
Dim src As DAO.Database
Dim tdf As DAO.TableDef
'
On Error GoTo err_sub
'
' create new db
MyNewFile = CurrentProject.Path & "\Change_Tracker.mdb"
Set dst = CreateDatabase(MyNewFile, dbLangGeneral)
' copy the tracking table
DoCmd.CopyObject dst.Name, MyTable, acTable, MyTable
' drop local tracking table
DoCmd.DeleteObject acTable, MyTable
' link to table in external file
Set src = CurrentDb
Set tdf = src.CreateTableDef(MyTable)
With tdf
.Connect = ";DATABASE=" & dst.Name
.SourceTableName = MyTable
End With
src.TableDefs.Append tdf
exit_sub:
Exit Sub
'
err_sub:
MsgBox Err.Description, vbCritical, "Error # " & Err.Number
Resume exit_sub
'
End Sub

You can view the complete code here: External_Change_Tracker

Whether you use method 1 or 2, you will still need to perform a Compact & Repair to reclaim the space used by the deleted table.

Abort SQL batch

I am working in an environment that has three databases:

database ABC on server ABC - the production system

database ABC_QA on server ABC2 - for QA testing

database ABC_DEV on server ABC3 - for development

The developer writes scripts to create / alter objects & manipulate data during development on ABC_DEV, and then sends them to the DBA to run against ABC_QA for QA testing. When QA is complete, the developer prepares a final script for the DBA to run against ABC. In both cases, the "script" file is a compilation of multiple individual scripts, each of which ends with a GO statement.

A problem arose when the DBA ran an intermediate QA script against production - the script does not include a USE statement; but even if it did, and that failed, the script would continue to run, and any create object statements would execute against whatever the current database connection was (usually master). Not good.

I did some research on RAISERROR and found that a severity level of 20 of higher will actually close the database connection, and thus abort the entire script, preventing any extra objects from being created. Adding the following to the beginning of the script takes care of this:

IF NOT EXISTS
(SELECT 1
FROM sys.sysdatabases
WHERE [name] = 'ABC')
RAISERROR ('INCORRECT DATABASE', 21, 1) WITH LOG

which yields the following message to the DBA:

Msg 2745, Level 16, State 2, Line 5
Process ID 57 has raised user error 50000, severity 21. SQL Server is terminating this process.
Msg 50000, Level 21, State 1, Line 5
INCORRECT DATABASE
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

...and the rest of the script is aborted without any further processing.