T-SQL Tuesday: My Automation Epiphany

February 8, 2011

This post is part of the monthly SQL Server blogging event known as T-SQL Tuesday.  Each month, a different SQL Server blogger “hosts” the event by suggesting a topic or theme and accepting trackbacks from participating bloggers from their posts on the topic.  Pat Wright (blog | Twitter) is hosting this month and has selected automation as the theme.

 

In late 2010, Jeremiah Peschka (blog | Twitter) reached out of my monitor and slapped me upside my head when he wrote…

“99% of what you do could be replicated by a fairly stupid shell script.”

– Jeremiah Peschka, T-SQL Tuesday – Why Are DBA Skills Necessary?

That’s a Bitter Pill

He was right.  Well, maybe not 99% but I had to be honest with myself and admit that I was doing too much automatable work manually.

It wasn’t that I hadn’t automated anything.  The first thing I had done as a fulltime DBA was automate our morning checklist, displaying the results in a series of SQL Server Reporting Services (SSRS) reports.  Since then, I had automated process after process using technologies such as SQL Server Integration Services (SSIS), C#, SSRS, SQL Server Agent, and even a bit of Windows Shell scripting.  That is pretty much what I do.  But I had failed in two major ways.

I had failed to recognize the opportunities to automate the little things.  The pesky little things that constantly pop up and take up your time.  Little things such as running a query against 60 of the 80 databases on two particular servers and consolidating the results in a single comma-separated value (CSV) file.  Little things such making a sp_configure change on 15 servers to enable the setting to Optimize for Ad-hoc Workloads.  Little things such as configuring a new instance of SQL Server.

I had also failed to equip myself with a best-of-breed scripting language for automation.  I had known for years that Powershell was the future, yet for me it still remained just that.  Why stop what I was doing and learn yet another way to get things done?  I already had T-SQL, C#, SSIS, etc.

I’m Better than That

Well, if a fairly stupid shell script could do 99% of what I do, well that was great news because I didn’t really like much of that 99% anyway.   I would rather learn a new technology or design a new solution to a problem than repeat mindless busy work that could be automated.  The best days for me are when I get lost in something and lookup at the clock and the day has just vanished.  That does not happen while copying query results and pasting them into Excel!

I decided in December 2010 that I owed it to myself to bite the bullet and take this new fangled Powershell thing for a spin.  I decided to do what I always do when I want to learn a new language or technology:  I gave myself a requirement.  I set out to automate our manual checklist for configuring a new instance of SQL Server and (true to Jeremiah’s observation) I was able to automate 99% of it!

By the time I finished with that first project, two things had happened.  I had gotten to know Powershell fairly well and I had fallen in love with it!  Powershell is so full of ah-ha and ooh-yea moments that it is just a joy to work with.  I’ve since continued to seek out that joy by taking an automate-first approach to everything that comes my way now.  If I can automate it, I do automate it.  With each passing week, the percentage of what I do that could be automated is going down while the percentage of what I do that is automated goes up.

Advertisements

T-SQL Tuesday #002: Checking for SET FMTONLY ON

January 12, 2010

This post is my submission for the second T-SQL Tuesday, the theme of which is A Puzzling Situation.

A number of SQL Server tools execute queries with SET FMTONLY ON in order to obtain information about the result sets returned by those queries.  SQL Server Reporting Services (SSRS) does this to get the list of fields returned by the query so that you can lay them out on your report as desired.  SQL Server Integration Services (SSIS) does this in several places, including within the Data Flow task when you create your source in order to gather the meta data of the fields that will make up your data flow.

You can run into problems when complex queries are run with SET FMTONLY ON.  The most common problem might be with temp tables.  If your query is a stored procedure that creates a temp table and then subsequently references that temp table, you will get an error.  When running with SET FMTONLY ON, the statement that creates the temp table is not actually executed.  Therefore, when the statement that references the temp table is compiled, it generates an error due to referencing an object that does not exist.  See the following example.

CREATE PROCEDURE dbo.p_FmtOnly

AS

SELECT GETDATE() AS theDate

INTO #tDate

SELECT theDate AS [fulldate]

, DATEPART(MONTH, theDate) AS [month]

, DATEPART(YEAR, theDate) AS [year]

FROM #tDate

GO

If you execute this stored procedure with SET FMTONLY ON, you will receive the following error:

Msg 208, Level 16, State 0, Procedure FmtOnly, Line 7
Invalid object name ‘#tDate’.

The most common workaround for this problem is to essentially inject “SET FMTONLY OFF” into the query.  For example, if you had an SSRS report that called the procedure above, the actual query would be “SET FMTONLY OFF; EXEC dbo.p_FmtOnly”.   However, sometimes you might be dealing with a stored procedure that takes a long time to run or for some other reason you do not want it to run for real at design time.  In this case, the SET FMTONLY OFF workaround does not work for you.

My puzzle for this post is a challenge to come up with a way inside the stored procedure to capture whether SET FMTONLY is ON.  If it is on, the procedure should just return a single row of hardcoded values of the same data type as the real final result set.  If SET FMTONLY is off, then the real stored procedure should be run.  It would look something like the following (note, this code does not work and only serves to explain the challenge):

CREATE PROCEDURE dbo.p_FmtOnly
AS
DECLARE
@bFmtOnlyOn AS BIT

SET @bFmtOnlyOn = ???

IF (@bFmtOnlyOn = 0)

SELECT GETDATE() AS [fulldate]
, DATEPART(MONTH, GETDATE()) AS [month]
, DATEPART(YEAR, GETDATE()) AS [year]

ELSE

BEGIN

SELECT GETDATE() AS theDate
INTO #tDate

SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate

END
GO

A colleague of mine was able to come up with a clever way to solve this problem.  I thought it would make an interesting puzzle for the T-SQL Tuesday event to see how many other techniques folks might come up with.

CREATE PROCEDURE dbo.FmtOnly
AS
SELECT
GETDATE() AS theDate
INTO #tDate SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate
GO