Jul 242009

I’m making my debut large-scale SQL Server presentation at the Melbourne SQL Server user group next Tuesday, July 28.  Do come along!

Are your database files and backups and the data inside your tables safe from prying eyes? Or are the doors slightly ajar, or worse – wide open? It is ever more important to secure data these days, and the database is at the heart of your organisation.

Join Jim McLeod as he explores the various SQL Server features that can be used to secure your data. In this presentation, we also examine a method of indexing the encrypted data to allow fast retrieval, and to use SQL Server’s encryption functions for a variety of practical uses.

Registration is at http://www.sqlserver.org.au.

Jul 232009

When developing T-SQL code, I frequently have a number of stored procedures created and run inside one .sql file.  While I’m generally good at highlighting the single statement I want to run, there are some situations where the highlight is misplaced – usually by me getting complacent with the lack of lag on Remote Desktop.  Suddenly I’ll get a dose of lag, my highlight command (shift-uparrow) won’t be processed, but my Execute (F5) command will.

To defend against this, I determined that the best option would be to create a severe error at the top of the .sql file to ensure that execution would cease.  Unfortunately, this only stops the execution of the current batch, and as CREATE PROCEDURE must be at the start of the batch, I have many batches in my code.

Five minutes after requesting suggestions from Twitter, Rob Farley suggested using SQLCMD mode – which is a feature that I haven’t used very often.  This is activated in Management Studio via the “Query” | “SQLCMD Mode” menu item, and can be specified as the default mode for all new query windows.

Placing the following code at the top of the file results in a fatal error that completely halts execution:

:on error exit
SELECT * FROM SomeTableThatDoesntExist


The !!dir command will list the contents of the current directory.  You can run this line by itself, but this line won’t be executed if you run the entire .sql file.

The drawback to this method is that you lose Intellisense in SQL Server 2008 and the ability to debug your code, but I’m willing to live with this to prevent having to comment out my more dangerous statements!