Zen of Python

I just saw this on Python.org 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

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')
GO
--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 '!'

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. 

Enable email to operator for all jobs using TSQL

There were a bunch of jobs that I needed to enable email notifications in case of a failure. I came up with this quick and dirty way:

DECLARE @job_id1 nvarchar(200)
DECLARE updateOperator CURSOR FOR 
select job_id from msdb..sysjobs WHERE enabled = 1

OPEN updateOperator
FETCH NEXT FROM updateOperator INTO @job_id1

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id= @job_id1, 
  @notify_level_email=2, 
  @notify_level_netsend=2, 
  @notify_level_page=2, 
  @notify_email_operator_name=N'Master-DBA'

    FETCH NEXT FROM updateOperator INTO @job_id1
    END

CLOSE updateOperator
DEALLOCATE updateOperator

Stored procedure for exporting query results to text file

First you need to enable xp_cmdshell( I realize it can be a security issue so I always enable it on demand, do what I need to do and disable it immediately)

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'xp_cmdshell', 1
reconfigure
go

And now the procedure -

CREATE PROCEDURE dbo.ExportText
AS
BEGIN
DECLARE @bcp AS VARCHAR (8000)
DECLARE @status AS INT

PRINT ''
PRINT 'Lets generate the text file'

SELECT @bcp = 'bcp "SELECT * FROM master..sysdatabases" QUERYOUT "D:\output.txt" -T -c'

EXEC @Status = master.dbo.xp_cmdshell @bcp, no_output
IF @Status <> 0
BEGIN
 PRINT 'Something went wrong. No text file for you!'
 RETURN
END ELSE
 PRINT 'Successful! Go check your text'
END

SSRS Report Access Error: User 'DOMAIN\LOGIN' does not have required permissions. UAC issue

I didn't configure reporting services on my local machine during the initial installation. I recently had to test something on my machine and configured everything from 'Reporting Services Configuration Manager' and when I tried to access reports on IE, I initially had to enter my windows login and password (which is silly, how hard is it to get the current login and security token without having to enter manually?)  I was greeted with the below message:

User 'DOMAIN\LOGIN' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed

Really? I had to launch IE using "Run As Admin" to get this to work. But that doesn't solve the problem forever. You either set your IE to run as an admin or disable UAC. But what if you access this server from different machines?

There is a workaround though: Once logged into report server, go to

Site Settings => Security => New Role Assignment => and Grant your admin login a System Admin role.

TIL: What makes a query relational?

1.The relational result set does not any order by default. If your query has an ORDER BY clause, your resultset ceases to be relational.
2. For any query to be relational, it must satisfy a number of requirements - No ORDER BY clause, All attributes must have names, all attributes must be unique, duplicates must not appear in the result.

Grant execute on all stored procedures

Ever wanted to grant execute permissions on all the user stored procedures for a user?
I had to do this and I ended up writing this cursor. Instead of executing directly, I opted to print the execute commands so that I can review before executing.

DECLARE @spname nvarchar(100)
DECLARE spgrant CURSOR FOR 
select name from sys.objects where type = 'P' AND is_ms_shipped = 0
OPEN spgrant
FETCH NEXT FROM spgrant INTO @spname
WHILE @@FETCH_STATUS = 0
BEGIN 
 Print 'GRANT EXECUTE ON OBJECT::'+@spname+ ' TO Cherry'
FETCH NEXT FROM spgrant INTO @spname
END
CLOSE spgrant

DEALLOCATE spgrant

Create a database user if it doesn't already exist

Similar to OBJECT_ID function which can be used to check whether it already exists before creating/dropping, we can use SUSER_ID function check whether the database user exists before creating. This can be handy when you have a user rebuild script and it often errors out when the script tries to add a user that is already in the database

IF SUSER_ID('Cherry') IS NULL
 CREATE USER [Cherry] FOR LOGIN [Cherry]
ELSE Print 'Already exists'