Geo Tracks – 12 Jun 2010

What is Geo Tracks?  My first post like this was a couple years ago, and it has been more than a year since my last Geo Tracks post… mainly because of a much shorter commute, also because I don’t have a Geo anymore!

After a year of local radio and NPR, I’m taking back my 40 minutes a day.  I purchased a cassette adapter so I’m back in business… I’ll log the good parts of my commuter listening here.

Hanselminutes 216 - Geek Relationship Tips with Scott's Wife.  Some practical ways Scott and Mo make their marriage work.  Good tips here about finances, setting expectations, assuming the best… I enjoyed it!

Hanselminutes 210 – John Lam and the Science of Fitness.  I am a couch potato right now… so hearing John Lam’s enthusiasm for using sensors to calculate his power output in watts while cycling was a bit over the top for me.  It is cool to hear about the types of sensors that are becoming available to every day Joes, and how it is becoming more acceptable, even expected, to share your data/progress in social apps like Twitter or Facebook.  Scott even jumped on the bandwagon recently by posting his LoseIt updates to Twitter.  I responded by reading about the HundredPushups and Couch to 5K programs while eating a bag of chips.

Cornerstone Simi Podcast 05/30/2010 – The End.  Francis Chan’s last message to his church in Simi Valley.  God has blessed me through this podcast, and I’m sad that Francis won’t be using this platform to teach anymore.  I’m excited to see how God will use him next though… and the good news is I have a lot of Francis messages to catch up on.

Cornerstone Simi Podcast 05/02/2010 – Salvation.  Francis compares salvation to harmonies in music, those weird 3D posters, and other things that you either “just suddenly get” or not.  He who has ears, let him hear.

Tracking SQL Server procedure and function changes

This Stack Overflow question inspired me to create a couple batch files today:

1. gen_all.cmd (creates one text file for each stored procedure and function in a database)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION')" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

2. gen_since.cmd (same as gen_all.cmd, but takes a date/time parameter and only generates files for procs/funcs that were created or altered since the specified date/time)

@echo off sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME NOT LIKE 'dt_%%' AND (ROUTINE_TYPE = 'PROCEDURE' OR ROUTINE_TYPE = 'FUNCTION') AND (CREATED >= convert(datetime,'%1') OR LAST_ALTERED >= convert(datetime,'%1'))" -o "sp_list.txt" for /f %%a in (sp_list.txt) do sqlcmd -E -S .\SQLEXPRESS -d DATABASE -h-1 -Q "SET NOCOUNT ON SELECT N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+ROUTINE_NAME+N']'')) DROP '+ROUTINE_TYPE+N' [dbo].['+ROUTINE_NAME+']' FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a' UNION ALL SELECT N'GO' UNION ALL select [text] from sysobjects join syscomments on sysobjects.id = syscomments.id where sysobjects.[name] = '%%a' UNION ALL SELECT N'GO'" -o "ProcsAndFuncs\%%a.sql" del sp_list.txt

Usage (to generate any files altered on or after Jun 5th):

gen_since 06/05/2010

I know there are some slick database change management tools out there, both OSS (Tarantino) and commercial (Redgate SQL Compare)… but I’m going to try using this simple batch file for a while as a nice, mindless way to save my DB objects as files that can be tracked in source control.