I’ll just leave this here….

An excellent discussion on manipulating Excel Spreadsheets directly from SQL Server TSQL!


Posted in Procedures | Leave a comment

So You Want To Be An Air Traffic Controller?

Excuse me while I take a brief detour from the DBA fare. This is my new DBA blog, and prior to this, all I ran was a personal blog. So I posted all my tech stuff on that personal blog. Now I have a venue for my purely technical scribbles.

On that personal blog, I occasionally answered questions from people who find out that I used to be an Air Traffic Controller (lo!, now, many years ago) about what that job is like. And with the CombatDBA blog, I now have a venue more fitting for such questions, even if it has nothing, really, to do with Database Administration and Programming.  It’s at least about technical issues.

In my particular case, I worked for the United States Air Force, and (to be quite honest) I did not CHOOSE Air Traffic Control. I was chosen for it based upon my test scores. I had absolutely no idea when I enlisted that ATC is what I would end up doing. I ASKED for computer programming. What I GOT was Air Traffic Control.

The most common question that I get is something like “I want to be an air traffic controller. What can I do to prepare?” The answer is, really, nothing. The skills needed to do the job are something that you either have, or do not have. And they are not really skills that you can get training for. If you “have”, good. If not, there’s not a whole lot that you can do to prepare. We’re talking things like “being observant”. Having a good memory.  Being able to “see” and estimate distances and closing speeds accurately.  The ability to keep a picture of something (something that’s moving) in your head.

As an example, one skill that would be really useful to you is to count how many time the letter “e” appears in this article, so far.  No, really, that was actually one of the questions on the one of the tests the Air Force put me through.

As for what the job is like, it can be, like any other job, exciting or dull.  In my case, I’d say I was somewhere in the middle.  If you are working in a very busy, high traffic environment – say, RAPCON or Approach Control at a large, very busy International Airport, then you can expect the job to be busy and exciting… and very, very stressful.  If you are working (as I did) in an enroute center, then you can expect a day to be routine and relatively relaxed.   And if you are working a municiple approach tower, the job might even be boring.

Approach means working in a tower.  This is the “high visibility” aspect of ATC.  When most people think of ATC, they imagine an Airport Control Tower.  It is also the job that requires the highest amount of skill.  There are also Enroute Traffic Control centers who handle aircraft while enroute between airports.  These guys handle “the long stretch” of the route.  Your job is basically to accept aircraft coming into your airspace and monitor verticle and lateral separation of them until they pass out of your airspace, or “radar handoff” them to an approach control center.

 There are other jobs involved in ATC that have nothing to do with “A” at all.  For instance, there are an entire class of controllers who actually only control planes while they are on the ground.  Ground Controllers help assure safety while planes maneuver around the tarmac on their way from the terminal to the runway.  And that can be every bit as busy as “Air” control at a busy airport.

There are “Flight Service” positions where personnel offer in-flight assistance and guidance to aircrews on subjects ranging from weather conditions to emergency procedures to use during a mechanical malfunction.

It’s a very diverse field.  But it remains that all ATC positions require a certain amount of natural skill and all involve a higher than normal amount of daily stress.  This is the reason for the (relatively speaking) young mandatory retirement age, which at my time was 51.

One thing to keep in mind is that the FAA ATC Academy has changed in recent years.  The washout rate in Academy used to be more than half.   The job is “high stress” and has high safety requirements.  The job of the Academy is to weed out people who are not a good fit for the lifestyle or who are, frankly, not cut out for it.  But in recent years, with the “ATC shortage”, the Academy taken a purely (in my opinion) political tact and is passing almost everyone who makes it to the Academy and is instead letting them “washout” in the field on their first duty assignment.

It can be a rewarding job.  In my case, at the critical moment when I left the A.F., while the normal route of us military controllers was to switch over to FAA, I decided instead to switch over to computer software development and database work.  And that’s what I have been doing ever since.

Posted in General | 2 Comments

A Toast to Brent!

I owe this to Brent Ozar.

I’ve been a “dba” for a long, long time.  But not an “official” DBA.  It was always an adjunct function to my regular developer duties.  I’ve been a long time “Development DBA”.

In 2008, I started reading Brent’s blog.  It was Brent who lit the fire in me to become a REAL, production DBA.

I started as an ASP/Java/DBA, and then took Brent’s encouragement to quit a full time developer job to pursue (and land) a position as a Production DBA managing the Dept. of Housing and Urban Development’s SQL Servers.

And now, I am happy to announce, that experience has brought me to the level where I was bold enough to pursue (and land, as of today) the Production DBA position for the Tulsa Technology Center.

I’ve gone from managing much larger databases to (probably) much smaller databases; but much more of them and in a much more diversified environment.  Whereas I currently manage three large (VERY large – terabyte class SQL 2005) servers, I will now be managing 20+ production servers spanning SQL Server 2000, 2005, 2008 and 2008 R2.

Ladies, and Gentlemen, I propose a Toast to my esteemed, and yet unknown mentor:

Brent Ozar!

Thank you Brent for inspiring and motivating me!

Posted in Procedures | 5 Comments

Does “Denali” portend the end of OLAP?

Question for you guys:  Will Denali be the end of OLAP?  Or perhaps OLAP as an adjunct or “tack on” technology? 

Microsoft claims Denali will “dramatically increase query performance ~10x and reduce performance tuning through interactive experiences with data for near instant response times and streamlined setup which removes the need to build summary aggregates.

All these are the very selling points of OLAP.  So, what do you think the future holds for OLAP either as a technology at all, or at least a technology distinct from the core technology services of SQL Server?

It sounds to me like they are either making it unnecessary, or folding it into the native services.

Posted in General | Leave a comment

Anti-Oopsie Tip of the Day

We’ve all done this. You know you have. Here’s the problem:

When scripting a Stored Procedure, you don’t save the script. You execute the script to create the stored procedure. Then you throw the script away, and execute the stored procedure.  However, quite the opposite is true when you are scripting a query or task in a query window. In this case, to run the thing you do in fact have to execute the script to run it.

This causes me no end of “oopsie opportunities”. I work a lot in stored procedures, so I’m very accustomed to thinking “Execute to Save”. But when I start writing a script to do a lengthy ad-hoc update, or a mass delete, or to kick off a lengthy backup procedure, hitting “Execute to Save” will give me a nasty suprise: the script starts doing it’s nefarious deed – immediately!

Here’s a tip:

At the top of your ad-hoc, one-off scripts, make the very first line “RETURN”. And be sure to code the rest of it using NO “GO” Statements (explained in a moment).

That way, if you inadvertently hit the “Execute” button, all it does is run and then immediately end. Then, to actually run the script, you highlight everything EXCEPT the first line, and execute that. And off it goes, doing what you want, but most importantly, doing it as planned.

The reason you need to NOT insert any GO statements is that each “GO” creates a seperate “batch” in your script. If there is a GO statement, anywhere in the script, then the “RETURN” on the top line will only make it jump to the first line after the very first GO statement and start executing there. That could be even worse than what you are trying to avoid, so use this tip with caution.

Posted in S.W.A.T. | Leave a comment

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.


When attempting to drop a database user, you get the following message:


One place to begin is your stored procedures.  The database user has likley been used in the “WITH EXECUTE AS” option in a stored procedure:

Hey, didja know that RedGate SQL-Search is FREE! Hint! Hint! Go get it. It will be a BIG help in this case.

Posted in Procedures | 2 Comments

That DBA Look…

Did you ever notice that there’s just something different about the DBA, if you have one on your development team?  Way back when, when I was a developer, I always used to kinda admire the DBAs. They had a uniquely different look about them.  Or, it was more like a uniquely different attitude. 

No matter what we were doing, or what emergency we were embroiled in, these guys always just oozed calmness and cool-headedness.

And, lo, these many years later, since I am a DBA now, it has finally dawned on me what this difference is.

It’s not smugness.

It’s not arrogance.

It’s not even confidence. 

“That DBA Look” is more like “Since there is not a single thing that you could possibly do to make my life any more stressful right now, why don’t you just go ahead and tell me what’s on your mind…


Posted in Procedures | Leave a comment

TSQL Cursor Options


Since cursors (being a workhorse of industry since ancient days) are never, really, going to go completely away, there’s still a need for helpful information on their use.

Fortunately, this row has already been hoed:


Posted in Procedures | Leave a comment

2008 != 2008R2

It’s old news by now that SQL Server 2008 R2 is NOT even closely related to SQL Server 2008. It’s a complete version upgrade. More annoying, though, is that because it is a complete upgrade, the backups (and MDF/LDF files that you attempt to detach/attach) are not compatible. If you are just taking a casual glance, it seems that SQL Server is not compatible with itself.

If it’s upgrade enough to break the backward compatibility, then it ought to have a version number and name that reflects that fact. Or at least make it clear:

“Microsoft SQL Server 2008 R2

But what really winds me up is the message you get when you attempt it:

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Like, maybe they could preface that whole thing with the words “Hey, moron: ”

It’s not really a nice idea to ridicule the customer. Especially when you’ve given them a false sense of security by naming the two completely different products so close in name that you’d have to excercise more than due dilligence to catch it. I don’t expect version 2008-dot-anything to be completely incompatible with 2008-dot-anything-else.

And I must say that a whole 0.5 version upgrade is hardly excuse enough to break ties with the previous version.

Posted in General | Leave a comment

SSMS Tab Bar is Nearly Useless

I can’t be the only one to notice that the Tabbed Window Management in MS SQL Server Management Studio (SSMS) is just plain awful.

As my day, or task, or project progresses, I end up with more and more windows open.  The order of those tabs is useful information to me, as it keeps track of the progression of the work I am doing.  But, depending upon the size of your screen, as more tabs are opened, tabs start to get bumped off the tab bar into some kind of limbo where they are only available on the dropdown list at the right. 

You cannot just scroll the tabs left and right to find your tab.  Once they drop off into the pulldown, they are also no longer displayed in the order that they were on the tab bar.  So when I’m looking for a window, I have to actually KNOW what meaningless tab name I’m looking for.  Otherwise, I end up pulling the dropdown down numerous times and poking around until I find the one that actually is my “ALTER TABLE”, or “CREATE PROCECURE” or whatever. 

The problem is one or more of them are Stored Procedures that I am modifying, others are ad hoc queries I am working on, one of them might be a TABLE I am altering.  But ALL of them say the same thing: SQLQueryN.sql.

Complicating this is the fact that the text of the tabs is virtually meaningless.  In my example above, all I see is SQLQuery1….prod.sql, SQLQuery2…prod.SQL……. SQLQuery6…prod.sql.  etc etc etc.  And things don’t get any better once they drop off into the dropdown where, presumably, you can take as much width as necessary to display the full tab name.  But they don’t.  The dropdown window name displays the same abbreviated, chopped, and useless information as it did when it was on the tab bar.

All in all, I’d REALLY be happy if MS came up with a different way of handling this. 

In the mean time, you can open Tools –> Options –> “Editor Tab and Status bar”, and choose which useless information you want on your tabs. 

But it’s still a kludge.

Posted in Procedures | 2 Comments