Thursday, April 25, 2013

Zen of Python

I just saw this on and I loved it so much I have to save it on my blog. It applies not just for Python but programming in general:

    Beautiful is better than ugly.
    Explicit is better than implicit.
    Simple is better than complex.
    Complex is better than complicated.
    Flat is better than nested.
    Sparse is better than dense.
    Readability counts.
    Special cases aren't special enough to break the rules.
    Although practicality beats purity.
    Errors should never pass silently.
    Unless explicitly silenced.
    In the face of ambiguity, refuse the temptation to guess.
    There should be one-- and preferably only one --obvious way to do it.
    Although that way may not be obvious at first unless you're Dutch.
    Now is better than never.
    Although never is often better than *right* now.
    If the implementation is hard to explain, it's a bad idea.
    If the implementation is easy to explain, it may be a good idea.
    Namespaces are one honking great idea -- let's do more of those!
                                                -- Tim Peters

Tuesday, April 23, 2013

Run VirtualBox VMs in the background - headless mode using VboxManage

I love virtualbox for my virtualization needs for its ease of use and low resource utilization. I have a bunch of VMs created to install and test SQL Server high availability features like AlwaysOn, Clustering etc. Working with these VMs I always wished if there was a way to just run these VMs in the background instead of keeping a window open for each VM. I googled around a bit and it is possible to start a VM in background mode, in virtualbox speak, its called headless mode.

Escaping wildcards while using LIKE predicate

When you look up the LIKE predicate on BOL you'll see the wildcard characters like %, _,[], [^] that will help finding a pattern in your tables. For example, the below sql will give you students whose lastname starts with D.

SELECT firstname, lastname
FROM students
WHERE lastname LIKE 'D%'

But things start getting interesting when your data has one or all of the above wildcards in your data and you would like to lookup a pattern. Lets create a table to illustrate this:

CREATE TABLE wildcards
  c1 int identity,
  c2 nvarchar(20)
--insert some data that has wildcards

INSERT INTO wildcards VALUES ('Joker')
INSERT INTO wildcards VALUES ('_Joker')
INSERT INTO wildcards VALUES ('Joker_')
INSERT INTO wildcards VALUES ('%Joker')
INSERT INTO wildcards VALUES ('%%Joker')
INSERT INTO wildcards VALUES ('[]Joker')
INSERT INTO wildcards VALUES ('[][]Joker')
INSERT INTO wildcards VALUES ('[^]Joker')
INSERT INTO wildcards VALUES ('__Joker')
--check data,

SELECT * FROM wildcards

--9 rows

Now try a regular query with Like predicate and try to find all jokers that have an underscore preceding the value.

SELECT * FROM wildcards WHERE c2 LIKE '_%'

Surprise! SQL server returns all 9 rows. It thinks that we passed two wildcards. We need to tell SQL Server not to treat '_' as wildcard. You can do so using the ESCAPE keyword.

SELECT * FROM wildcards WHERE c2 LIKE '!_%' ESCAPE '!'
SELECT * FROM wildcards WHERE c2 LIKE '%!_' ESCAPE '!'

Monday, April 22, 2013

Scheduling SQL Profiler trace as SQL Agent job

Connect to the server using management studio and go to Tools and Launch SQL Server Profiler.
Connect to the server you want to run the trace on.