Voice of the DBA

Author: way0utwest

Be Careful of Your Create Stored Procedure Batch
I was rehearsing a demo with someone recently and we had some stored procedure code that looked like this:
```
CREATE PROCEDURE UpdateEmpID @empid INT
 AS
 BEGIN
 UPDATE  dbo.Employees
 SET empid = 3
 WHERE  empid = @empid
 ;
 END
```
However, this was part of a batch that had all of this code (proc code repeated).
```
CREATE PROCEDURE UpdateEmpID @empid INT
 AS
 BEGIN
 UPDATE  dbo.Employees
 SET empid = 3
 WHERE  empid = @empid
 ;
 END

-- test the procedure execution
 -- exec UpdateEmpID 2

SELECT empid
 FROM dbo.Employees
 WHERE empid = 3
```
When I execute this, I see a simple message. If I’m not paying attention, this seems to make sense.

What happens if I execute this procedure? I’ll see something like this:

At first glance, you’d think this makes sense. However, what has happened here? The procedure executed, which has an update, and I have a result set at the end. If I look at the proc code, this makes more sense. I’ll right click the procedure and select modify.

Once I do that, a new query window opens. This is the code in there.

Why is my select code in there? That was designed to be a piece of test code. Shouldn’t the BEGIN..END after the AS define my procedure?

Actually it doesn’t. the procedure doesn’t end until the CREATE PROCEDURE statement is terminated. That termination comes by ending the batch. The CREATE PROCEDURE documentation has this limitation:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

This means that anything else you have in that batch will be considered as part of the procedure, regardless of BEGIN..END.

I hadn’t noticed, or seen this before. Perhaps because I’m in the habit of including a GO between all my code, it hasn’t been an issue.

I would hope most people would catch this before any code is deployed with testing, but perhaps not Be aware that stored procedures should be compiled in their own batches, always.
January 3, 2017
The Salary Range

We published the results of our 2016 Salary Survey today, which has some interesting numbers in it. I haven’t done a lot of analysis of the data, but it seems as though the averages are higher than I expected for SQL Server professionals. That’s good news, and it’s one of the reasons I switched from being a developer to a DBA. Our DBA back in 1990 made more than every other programmer in a larger Fortune 500 company. Since I enjoyed database work, I moved in that direction and never looked back.

I also ran across this interesting post on programmer earnings on Quora, and found some of the answers interesting. There are people showing the entry level salaries being high at large tech companies like Google and Facebook. I’m sure that happens, but those salaries often skew what most of us might see when we start in this field. I think non-tech companies that are trying to fit developers in with other entry level jobs (sales, marketing, operational positions in their industry, etc.) don’t view programmers as special. I would guess that the technology pay is better, but still below US$50k/yr in most places.

While I think in the US there isn’t necessarily a cap around US$100k/yr as there used to be for most of my career, I do think that it’s still hard to make more than this for most people. The average programmer is just that, average. Without being driven to develop strong skills, make a different in their organization (and learn how to do this), and stand out substantially from everyone else, management often doesn’t want to substantially increase their labor cost. They won’t want to pay more for a developer without a good reason.

All the number are useless if you become a freelance consultant. That’s easy to do in this field, though finding work is a challenge. As is the demands on freelancers outside of the technical work. On your own, you need to manage accounting, sales, marketing, and more, which are not necessarily the work that most people enjoy. In my view, if you become a freelancer, you earn that extra money with the hassles of running your own business (it’s not uncommon for freelancers to easily make over US$100k with successful ones going to US$200-300k/yr).

Many of us know people that love programming computers, and don’t worry too much about salary. Most of us would like to earn more, for a variety of reasons, but few of us actually work to earn more. There’s a good quote in the article: ” Because some folks never ask for raises and never leave a company. They built up experience, but didn’t leverage it, so their compensation never rises.” I’ve found that to be true. Far too many people never learn how to increase their salary, whether by changing jobs, becoming more valuable, or even just asking for more money in a way that is likely to be successful. We usually just take what’s offered.

Money isn’t everything. I think that working in technology strictly for money is a bad idea. There are downsides to this work (sedentary, stressful, complex and ever changing), but I also know that many people have enough of a talent to get by and have other interests in life. If that’s the case, and you want a job you can do for most of your life and earn a decent living, this isn’t a bad field. There are plenty of answers from people that recommend you don’t pursue programming if you don’t enjoy it, but I think that it can be hard to find employment in an area you really enjoy and also make ends meet. Certainly if you are starting a career, I recommend you try to find work in an area you enjoy. Build a budget that suits your earnings, and find some satisfaction and enjoyment in your career and life outside of work. Once you have a family or other responsibilities, it can become hard to make a drastic change in your career path and you may find yourself stuck working at a job you don’t enjoy.

There is one answer that struck me. The final line of that one says: “A bottom tier developer should probably stop being a developer.” This is a place I’d disagree. Almost every industry has no shortage of people that aren’t that talented at their craft. I know poor plumbers, electricians, doctors, lawyers, retail workers, and yes, programmers. Some of them may lose employment regularly and struggle to find jobs. If that’s the case, and it is a problem in your life, then perhaps you should look for other work. However, if you have a job, then why stop? Every team I’ve worked in or managed has a need for a variety of work. Some hard, some mundane. Some easy, some complex, but in all cases, it’s good that we have a spectrum of talents on the team. There is always some work that is better suited to less talented employees, whether they are electricians or programmers. There is also work that can only be done effectively (or efficiently) by the most talented.

If someone is willing to pay you, and you’re willing to do the work, then that’s the definition of a job you can do. Why stop, especially in this business, if you have employment that suits your situation? There’s nothing wrong with going to a place of employment every day, and doing a day’s work for a day’s pay, even if you don’t do it as well as others.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 7.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

January 3, 2017
Starting with Chocolatey

I ran across Chocolatey years ago when Scott Hanselman wrote about it in his power tool list. At the time, I wasn’t in need of the tool, but I thought it was cool and tried it. I was hooked.

In fact, the next year I got two different laptops, and using chocolatey, I was up and running in an hour, being productive. As I found software I needed, I just grabbed a package from Chocolatey and continued working while it installed. This was way, way easier than anything I’ve done outside of a Linux environment.

Getting Started

The first thing to do in order to use Chocolatey is go to the install page and run the PowerShell command in an administrative command prompt. That’s easy to do.

NOTE: This is a security change, so I’d do this on dev and client machines, not production boxes.

Once you’ve installed and enabled Chocolatey, the next step is to use it. When I have a new machine, the first thing I do is get Dropbox. The reason is that Dropbox has my passwords and my Chocolatey script. This is as simple as opening your command window and typing

choco install dropbox

This installs and I log in and start downloading my Dropbox to the local machine. While this is working, I may open a second command window and do this

choco install googlechrome

Since this is a staple for me on any machine. I do usually add FireFox as well, but Chrome is the first browser for me.

There are lots of packages available, and you can search for them. Once you get used to the tool, you’ll find yourself guessing at names to install things.

That’s it.

Just in Time Installs

I have a batch file with a lot of “choco install xx” statements, but I don’t worry about making it too complete. In fact, I try to keep this to a minimal number of software applications because I do change my habits over time.

I get the software I need, as I need it. When I built my last machine, I was still using cmd.exe. However, I decided to move to ConEmu on one machine and used “choco install conemu” to get the software on one machine. I liked the tool and a few months later I needed it on another machine, so I used chocolatey to just get it. While working, ConEmu installed in the background, and I could use it a few minutes later.

The same thing with most other software. Greenshot, Visual Studio Code, many things I need are just easier to install now. If I need something, I call a package and it installs.

Exceptions

There are some exceptions. Notably for me, Redgate products aren’t up there, so I have to download those separately. We also use O365 for Office, and that’s a separate install. In fact, if you use media, you’ll have to uninstall and reinstall from the o365 site. SQL Server also requires a separate download and install.

Convenience

The main reason I use chocolatey is that is saves time. I don’t hunt through Google and websites for download links, I don’t next…next…next for installs. I have ConEmu running, so a CTRL+~, pick my ConEmu window and choco install what I need, letting this run in the background while I task switch to something else.

This might not be for everyone, but I’ve found it very convenient and easy to use. Moving to chocolatey allows me to start treating laptops and desktops like cattle (with Evernote, Dropbox, OneDrive, etc.) and not be too concerned about the effort to rebuild or restore a system.

My next thought is to get some of the tools I need, like test SQL instances, into containers, and perhaps being able to back these up and restore them even quicker than I can today.

January 2, 2017
Technology Guilt

This editorial was originally published on June 15, 2013. It is being re-run for the New Year holiday.

I was reading a piece recently about the problems many new college graduates will face as they enter the workforce. There are less and less jobs available in many industries and a large reason for this is the advances in technology that have eliminated many jobs. Technology has made people more efficient, allowing companies to hire less staff. Or at least management thinks less staff is needed. New applications and devices have allowed self service operations for many types of tasks that might have required an employee in the past. This is a trend that is likely to continue.

Do we just need less people working in the world? I don’t think so. For every job we eliminate, there are new opportunities that come about. Unfortunately the new openings often require different skills from the position eliminated, which means that often the person who lost the job can’t take advantage of the new opening without some re-tooling of their skills.

Those of us in technology find ourselves moving from place to place, learning new skills and technologies easily because that’s the nature of the business. However in many other fields, it’s not as easy for individuals to gain new skills. This Friday I wanted to ask those in technology if they think about the impact their work has on others.

Do you feel guilty about the jobs that technology eliminates?

I used to feel that it was up to everyone to ensure they could find a new job if they lost their current one. It seemed to be a capitalist market idea that change will occur, new industries will arise as old ones die, and everyone ought to be prepared to transition to a new career. However technology seems to hasten the process, and so many people do not seem to be able to make the change quickly to a new field or a new industry.

Or perhaps they are too slow to embrace the idea of change.

I feel that technology has made the world better overall, and the tremendous amount of data that we capture, analyze, and use to make decisions allows the world to function better. We have room for improvement, and we certainly have security and privacy concerns, but we do need to find solutions to those problems and prepare for change in the future. We also need to make sure the next generation also understands that they will need to be more flexible and better trained than in the past.

Steve Jones

January 2, 2017