SQL templates

SSMS has a very handy feature in the Template Explorer (Ctrl-Alt-T) that contains 100's of templates for all sorts of scripting tasks you might require:

• Open the Template Explorer
• Drill-down to the category
• Right-click on the desired task and click Open.
• Press Ctrl-Shift-M to display a popup screen & enter your parameters
• Click OK

You can also create custom templates:

• Right-click on the top node, select New ~ Folder and give it a name e.g. Custom
• Right-click on the Custom folder, select New ~ Template and give it a name e.g. MyTemplate
• Right-click on MyTemplate, select Edit
• Observe the Connect dialog & connect to the your server
• Type in the template code and click Save

. . .but when you do that, the default location is something like

C:\Documents and Settings\Owner\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\Custom

so you will probably want to use Save As... and store your custom templates in a subfolder closer to where you keep your regular scripts & apply source control. NOTE: Your template file should also be set to read-only, because when you do the Ctrl-Shift-M the displayed file name does not change, so you want to protect your templates.

One basic example of using a template is to add a new column that has a default value.

Here is a sample hard-coded script:

ALTER TABLE dbo.Company
ADD Flag bit NOT NULL
CONSTRAINT DF__Company__Flag
DEFAULT (0)
GO

You could of course create a simple template where you just search / replace ttt = table name, ccc = column name, etc. but it gets very cumbersome:

ALTER TABLE dbo.ttt
ADD ccc ddd NOT NULL
CONSTRAINT DF__ttt__ccc
DEFAULT (vvv)
GO

This is a template that works with SSMS:

important note - blogger does not display less-than or greater-than symbols, so in this example you need to replace the { with the less-than symbol, and replace the } with the greater-than symbol

ALTER TABLE dbo.{table_name, sysname, table_name}
ADD {field_name, sysname, field_name} {data_type, nvarchar(128), data_type} {nullable, nvarchar, NOT NULL}
CONSTRAINT DF__{table_name, sysname, table_name}__{field_name, sysname, field_name}
DEFAULT ({default_value, nvarchar, default_value})
GO

Within each set of brackets, the 3 arguments are:

• title of the parameter that is displayed in the popup screen
• the data type of the parameter that is to be inserted
• the default value of the parameter that is to be inserted

I have posted a few templates as Word documents with the full code, screen shots & more examples, you can view them here:

column_add_with_default

column_drop_add_with_default

table_drop_create

Remote Queries in Access

MS-Access makes it very easy to link to tables in other Access databases . . .

File ~ Get External Data ~ Link Tables...

just navigate to your mdb file & click Link, select the table(s) and click OK.

But you can also connect & retrieve data from another file, without creating a linked table. This little-known technique has been around AFAIK since the Access 2.0 days. I'm not suggesting that you use this instead of linked tables, but it has certain advantages & flexibility in specific cases.

Begin a new query, don't add any tables, and switch to the SQL view. Type in your SQL statement, which might look like this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'

Note the single quotes around the file name. When you run this query, you'll see the data in that external table. If you want to apply some criteria, you could use this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'
WHERE MyField = "something"

One advantage of this technique is that it bypasses the show / hide settings for hidden & system objects ( Tools ~ Options ~ View ~ Hidden objects, and ~ System objects ).

Let's say you want to view all the user tables in an external file - you could use this:

SELECT [name]
FROM MSysObjects
IN 'C:\MyFile.mdb'
WHERE [type] = 1
AND LEFT([name],4) <> "MSys"

MSysObjects is a system table that contains all the objects in the Access mdb. The type = 1 denotes local (not linked) native Access tables.

Taking this a step farther (with a little bit of VBA code) you could view a list of the fields in that external table (watch for line breaks) . . .

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sql As String
'
Set db = CurrentDb
sql = "SELECT * FROM Table1 IN 'C:\MyFile.mdb' "
Set qdf = db.CreateQueryDef("", sql)
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld

Change Management

Managing change in SQL Server applications requires a process that protects data integrity & maximizes production uptime, while providing flexibility for new development. I would propose the following as a minimum configuration to accomplish these goals.

Create 3 database environments -- one for production, one for development, one for QA. Only the DBA has direct access to production or QA. The developer has direct access to only the development db. These 3 db's can sit on separate servers, or alternately the development and QA db's can run on named instances on the same server if size & activity is modest.

Create 3 application front-ends -- one for each database; containing some code, config file or other means to connect the front-end to the correct db; having some method to prominently display the connected server & database when it is not connected to the production db. (The front-end is really a single design, it is only the connection string that varies between the 3 copies.)

To begin, the DBA takes a backup from production & restores it to both the development & QA servers.

The developer connects the DEV front-end to the DEV db, writes T-SQL scripts to make any changes to the design, and updates the front-end for those changes. It is possible to use the GUI to generate the change script, but the GUI is never used to actually apply those changes - that is accomplished with the script. The developer also prepares documentation for the desired change in the front-end.

At this point, the developer may decide to share the DEV copy with select stakeholders, especially if the change is complex, for feedback & verification that the change meets the requirements. This step is commonly referred to as user acceptance testing.

When the developer verifies the change in that environment, they pass the change script to the DBA who reviews the script, and then applies it to the QA db. The developer will then connect the QA front-end to the QA db and verify the change.

The QA person will then evaluate & verify the change using the documentation provided by the developer. Upon their sign-off, the app is ready for release.

The developer notifies all users that the database update will commence.

The DBA will backup all 3 db's and then apply the script to production. Following that, the developer will connect the production front-end to the production db & verify. The developer publishes the new front-end & notifies the users that the update is complete.

The DBA takes a fresh production backup & restores to both DEV and QA, and the developer reconnects DEV to DEV and QA to QA. The DBA may also pass a copy of the backup file to the developer, if the 4th environment is used as follows.

A 4th possible environment is for the developer to maintain a copy of the DEV db on their PC, using e.g. the MSSQL Developer Edition along with a 4th copy of the front-end. This provides ultimate flexibility of design; the developer restores the backup to that system & can use that as a test-bed for feasibility studies of design changes without impacting the true DEV environment.

Throughout all the above processes, source control is used to maintain matching copies of the front-end & database scripts to allow rolling back changes that have unintended consequences.

SQL scripts without sp_executesql

There is a totally useless scripting option in SSMS 2005 that you think might mimic the behavior of EM 2000 but it does quite the contrary.

Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you generate just the CREATE and then change that one word to ALTER. Simple stuff.

However, in SSMS there is an option "Include IF NOT EXISTS clause" which does several totally unnecessary things:

First of all, you will see

EXEC dbo.sp_executesql @statement = N'
all your statements are here
'
If you choose Create, it tests IF NOT EXISTS followed by CREATE, but you just scripted an object that exists, so it does nothing.

If you choose Modify, it tests IF NOT EXISTS followed by ALTER, but how can you alter an object that does not exist?

What a mess.

If you want to generate a script like 2000, then you need to turn that option OFF, and then separately generate the scripts for the DROP and the CREATE, and put them together.

SQL - list columns in a table

I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for example

sp_help 'MyTable'

or

sp_columns 'MyTable'

which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data types etc.

I created a new stored procedure wm_columns, click the link to view.

I began with sp_columns since it returns a single record set. Then, after looking in BOL I created a temp table to capture the output, and then execute the sp to populate the table:

DECLARE
@sql nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
[TYPE_NAME] sysname,
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)

INSERT INTO #temp
EXEC sp_columns @Table

Once that it done, it is simple to select only the columns that interest me:

SELECT
COLUMN_NAME,
[TYPE_NAME],
[PRECISION],
[LENGTH],
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#temp

. . . but it would be nice to be able to sort the list alphabetically by the field name, so after populating the temp table, I create a unique index based on an argument @SortAlpha to my stored procedure:

EDIT ON 04/09/2009: There was a bug in this part of the code, the alpha sort did not work. Fixed here & also in the download file . . .

SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +
CASE @SortAlpha
WHEN 0
THEN 'ORDINAL_POSITION'
ELSE 'COLUMN_NAME'
END + ')'

. . .END OF EDIT

EXEC sp_executesql @sql

. . . which causes the ouput to be sorted according to the argument.

Linking Access to SQL Server

I have an Access app that links to SQL Server, and when I tried to attach a new SQL table in code I ran into a snag - the linked table would forget the user name & password, so Access would pop up one of those login dialog boxes.

I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):

Sub SQL_AttachOne(TableName As String)
  Dim db As DAO.Database, tdf As DAO.TableDef
  Const s = "the connection string goes here"
  Set db = CurrentDb
  Set tdf = db.CreateTableDef
  With tdf
    .Name = TableName
    .SourceTableName = TableName
    .Connect = s
    .Attributes = .Attributes Or dbAttachSavePWD
  End With
  db.TableDefs.Append tdf
End Sub

The Or operator sets the appropriate flag for Access to save the info.

Loop thru SQL tables

"How can I loop through all the tables in a SQL Server database, and run T-SQL on each table where the name matches a pattern, without using cursors?"

This question was recently posted at a SQL forum. Here is the solution. . .

SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example, to list all tables that have the prefix 'tbl' you would use:

sp_tables 'tbl%'

To use these results in subsequent processing, we need to save the output to a table. After looking up the sp_tables procedure in BOL, we can create a table to hold the results, and then execute the procedure to populate the table:

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)

INSERT INTO #temp
EXEC sp_tables 'tbl%'

Now we need a strategy for looping thru these records & processing each table one-by-one - - We can add a flag to the temp table to mark when each record is processed:

EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'

Now, we select the first table name, run our T-SQL on that table, and mark the flag as being processed. The WHILE loop continues as long as there are any records that have not been processed. At the beginning of our code we need to insert the line:

DECLARE @TABLE_NAME sysname

and then we can write our loop:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
PRINT @TABLE_NAME
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which simply prints the name of each table, where the table name matched our filter.

To execute a real SQL statement against each table, we need to add another variable at the top of our code:

DECLARE @sql nvarchar(4000)

and then the loop is written like this:

WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END

...which prints the number of rows in the table, for each table where the name matched our filter.

You can view the complete code here:

SQL_Loop_thru_tables

Tracking Access Button Clicks

I am working with an older Access application that has almost 500 command buttons, and our task is to determine which features are still being used & which ones can be retired. Hopefully we'll recover screen space, plus improve the maintenance by removing obsolete queries & reports.

Of course, asking the users which features they are using is one option, but with over 100 users that is not really practical.

So we created some code to track button clicks, and using the Screen object in Access makes that quite simple. Basically we created a module to save the click info to a table, and then used Word to mass-update the code modules to add a line to invoke the tracking code. More on that later.

The ClickTracker code:

First, we test if the user clicked a command button. This is necessary because you can have a click event on a list box, combo box or even the form itself. The test looks like this:

If Screen.ActiveControl.ControlType = acCommandButton Then
' ( code goes in here )
End If

Then we can get the relevant info:

The form name is
Screen.ActiveForm.Name

The button name is
Screen.ActiveControl.Name

...and the button caption is
Screen.ActiveControl.Caption

The complete module can be viewed here. ( see * and ** below )

Part 2 - adding that line of code in 500 places...

With that many command buttons, we needed a quick way to insert that code. Easy. Select the text of the entire code module & copy / paste into Word. Then you can do a Find & Replace All, using some of the special characters available in Word:

Find this: _Click()
Replace with: _Click()^p^tClickTracker

...which will insert a new line right at the beginning of every click event to invoke the ClickTracker code. Then you just select all the text from Word & paste it back into the code module & overwrite the old version.

----
* our table uses an Autonumber for the first field, that why we skip rs.Fields(0). The other fields can be text(64) because that is the max length of a form or control name in Access.

** our version includes calls to custom functions 1: GetUser() calls the Windows API to get the ID of the Windows user, and 2: DateNoTime() strips off the time portion from Now() so we are saving only the date, not the time. You don't need these for the rest of it to work, but you do need to modify the code to skip those last two fields.

Access subform VBA

Forms behave differently when they are used as subforms, and this can be a challenge when using VBA. Normally, when you load a form, Access adds it to the Forms collection. So if you open a form MyForm then

Forms.Count = 1

and you can refer to a control on that form using:

Forms!MyForm!MyControl

However, if that form contains a subform, MySubform, then

Forms.Count = 1

and this will fail:

Forms!MySubform!MySubformControl

So if you want to refer to a control on the subform, you need to use:

Forms!MyForm!TheSubformControl.Form!MySubformControl

Note that TheSubformControl is the name of the subform control on MyForm that contains the form MySubform, not the name of the embedded form. Nowhere do you see the actual name of the form that is being used as a subform.

Confusing? You bet. If you use the Expression Builder it can allow you build the incorrect version, so you need to make sure to drill down through the main form to the subform.

Let's say that you want to invoke code on the subform when an event occurs on the main form - for example, when you navigate to a different record on the main form, you want to set the value of a combo box on the subform & then run its AfterUpdate code. Due to the fact that the subform properties & methods are not exposed, in the Form_Current event on the main form you might use this:

Forms!MyForm!TheSubformControl.Form!MySubformCombo = DCount("*","MyTable")

Forms!MyForm!TheSubformControl.Form.MySubformCombo_AfterUpdate

But wait - that doesn't work (yet). Why? The answer is quite simple: Scope.

By default, code in standard modules are declared as Public, so they can be accessed from anywhere in the application. But code-behind in forms is declared as Private. So in the subform code, all you have to do is change this

Private Sub MySubformCombo_AfterUpdate()
'
End Sub

to this:

Public Sub MySubformCombo_AfterUpdate()
'
End Sub

...and it will work as expected.

Compact and Repair Database

MS-Access files tend to grow over time, for a variety of reasons such as

• adding many new records
• large number of user edits
• running action queries (insert, update, delete)
• design changes, especially to forms & reports
• temp tables that are emptied & repopulated
• using the Name AutoCorrect options
• Access replication
• WorkGroup Security with encryption

The theoretical design limit of an Access mdb file is 2 GB, but as you approach 80-100 MB the performance & stability will begin to suffer; above 250 MB the file can become barely useable. This is especially significant when the file is located on the network & you have multiple users accessing that file. Plus, many anti-virus programs will aggressively scan mdb files.

NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...

One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use

Tools ~ Database Utilities ~ Compact & Repair Database

which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.

An alternate solution is to turn on the option for Auto-Compact, using

Tools ~ Options ~ General, and check the box Compact on Close

...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.

So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:

Function GetMyFileSize() As Long
    ' this requires a reference to
    '   Microsoft Scripting Runtime
    '   c:\windows\system32\scrrun.dll
    Dim fso As FileSystemObject
    Dim oFolder As Folder, oFile As File
    '
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(CurrentProject.Path)
    Set oFile = oFolder.Files(CurrentProject.Name)
    GetMyFileSize = oFile.Size
    Set oFile = Nothing
    Set oFolder = Nothing
    Set fso = Nothing
End Function