Voice of the DBA

Category: Blog

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
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
My 2016 by the Numbers
This is it, the last work day of the year. I wrote about my look back from the data side of the world, but I also wanted to take a look back at my personal 2016 from the work perspective. This is a chance to examine the way my career’s gone this year and make plans for next year.

Travel

I traveled a lot this year. I had sixteen trips in 2016 for work. That’s less than some years, but it was a harder year for me. With five personal trips out of town, that means I had over 80 nights in hotels or other accommodations, which is quite a bit for me. Some people like that, but the time away from home wears me down.

I also had some long trips. Three of these trips were over two weeks, which is a long time for me. I did get some holiday time on some of these trips, and a couple with me wife, which makes things a little easier.

I have already made plans to reduce this in 2017. I hope.
- 16 business trips
- 80+ hotel nights
- 5 personal trips
- 42 flights
- 4 trains between cities
- 1 car trip to an event
- 2 car rentals driving on the left side of the road
New Countries

On the plus side, I went to two new countries this year for work: Norway and Denmark. Those were great experiences, and I have to really thank the organizers of those events (SQL Nexus and SQL Saturday – Oslo). They made the trips enjoyable and memorable. I’m not sure if I’ll get back, but I would certainly like to.

The hard part of adding in the other countries is that I need to plan these trips with business trips, so they get extended. I may get to one new country in 2017, and my fingers are crossed that things will work out.
- 4 countries in which I delivered talks during 2016
- 9 US States in which I presented
- 17 cities where I stood in front of an audience.
Conferences

In the past I’ve typically spoken at a few user groups, SQL Saturdays, the PASS Summit, IT/Dev Connections, SQL in the City, and SQL Bits. This year I added a few new ones. I was lucky enough to get selected for a couple VS Live events, SQL Nexus, the London Database Professionals Meetup, and MeasureUp, a developer event in Austin.

The highlight for me was //build//, where I was on stage with Donovan Brown from Microsoft talking database devops to hundreds of people. I haven’t spoken at a large Microsoft event before, so this was quite a honor for me.

Caveat, I was selected to speak at Ignite a few years ago, but had to cancel with a knee surgery. Still, I’m quite honored to have been chosen twice.
- 7 expensive conferences
- 7 SQL Saturdays
- 4 user group presentations
Learning

This was an interesting year for me. Each year I try to make an effort to learn some new technologies and new skills. At the beginning of 2016, I was continuing on with learning Python. In 2015 I started attending (remotely) the San Diego TIG meetings with the Python track and got interested. I found this was an interesting way to force some learning on myself. I even worked through part of the Advent of Code with Python (as well as T-SQL and PoSh) to practice some programming skills.

However, I fell off throughout the year. I started brushing up C# skills since I needed some small applications for Always Encrypted, but again fell off. I started a mobile project and let that go. It seems as though life seems to get in the way of any long term learning, and I made the mistake of not setting aside specific times each week for learning. I relied on my curiosity and interest in technology to drive learning, but this wasn’t as concentrated or focused as I would have liked.

I did make time for some pre-cons at a few events and made a point of attending a number of sessions at every event, taking notes and even working through some sample applications. A list of the major talks I remember attending and learning in.
- Encryption – I’ve always dabbled, but I spent time watching a number of sessions from other presenters and building projects with SQL Server’s encrpytion capabilities.
- Azure Machine Learning – worked through a few sample and experimental projects on my own.
- Microsoft Bot Framework – build a sample bot
- Extended Events – drove me to complete a Pluralsight course
- SSIS Frameworks – need to use this
- VSTS DevOps – I’ve started using VSTS almost every week
- PowerShell – I’ve begun using PoSh more and more to handle some tasks and practice my skills. I especially like dbatools.
- C# – On and off practice in addition to Pluralsight courses and a few sessions.
- Python – I really like Python. I worked my way through part of an ML book, but I’d like to do more with this language. I started with v
In 2017 I need a better learning plan.

SQL Server

This was a fascinating year of SQL Server work for me. I began working with a large number of parts of the platform. I spent time working with all of these items:
- Extended Events
- Row Level Security
- Dynamic Data Masking
- Always Encrypted
- TDE
- SQL Audit
- T-SQL (new language constructs as well as practicing old skills)
- XML
- JSON
- Stretch DB
- Always On
- In-Memory
There are probably other areas that I tackled, but it’s hard to remember across the year. In each of these areas, I spent a number of concentrated hours experimenting, learning, and getting some piece of technology working. I’d hope to continue this in 2017, especially as I expect another new version to appear with some enhancements that I’m interested in using.

Focus

This was a year where my focus changed from DBA to more of a developer look at the world. While I certainly worked on non-SQL technologies, my focus with Redgate has been more of a developer, DevOps, CI/CD look at the world and trying to help customers and community solve those issues.

I find this to be more interesting, but also most challenging. One of the things I learned as a developer is that there are always cool new things to experiment with and it can be hard to actually focus on the technology you primarily use and continue to improve the way in which you use the tools. There is a distraction from all of the possibilities and options available to you.

As an example, I started to work with one of the dbatools cmdlets and saw a note that Visual Studio Code supports PoSh. I’ve usually worked with the ISE, but launched VS Code and looked for a PoSh extension. I found a few and spent time looking at reviews and comments.

Then I picked one, installed it, restarted, and then tried to code. Struggled to run the PoSh inside of VS Code, so Googled for this. Spent more time trying to get this working. I ended up learning a couple things, but really I wasted close to an hour playing with VS Code, trying to make it work for me, when I had a perfectly good tool (ISE) that works. As much as I like lightweight editors, I can’t spend a bunch of time trying new ones when I have some that work.

I want to try and focus more on specific items next year, make improvements and grow deeper in a few areas rather than trying to grow too widely.

Overall

I found 2016 to be a hard year. My travels were tiring and difficult with family life. I had a few personal issues that made the year hard, among them my second son graduating from high school. I stressed about trying to spend lots of time with him (and my 3rd child) before he leaves home. On the plus side, despite the stress, I was able to watch him complete his Eagle Scout rank for Boy Scouts, which made me quite proud.

Work was a mix, both with more demands in some ways, less in others. Higher engagement with some parts of Redgate, lower with others. Certainly a year of change. However, I have a great company, and I really enjoy working there. They even got me to dress up:

The crazy election(s) of 2016 in the US and UK, the number of talented celebrities I will mourn, those where downsides. The Broncos winning the Super Bowl, lots of skiing, and some long vacations with family where highlights. I also managed a 199 day run streak and over 400 miles for the year as part of my cousin’s Zuckerburg challenge to run a mile a day.

I’m glad 2016 is ending, and I look forward to 2017 and enjoying a better year.
December 30, 2016
Checking Permissions for Keys–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.

Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.
```
CREATE SYMMETRIC KEY MySalaryProtector
WITH ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Salary Protection Key',
    KEY_SOURCE = N'Keep this phrase a secr#t'
ENCRYPTION BY PASSWORD='Us#aStrongP2ssword';
GO

CREATE ASYMMETRIC KEY HRProtection
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^';

GO

CREATE CERTIFICATE MySalaryCert
ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1'
WITH SUBJECT = 'Sammamish Shipping Records',
    EXPIRY_DATE = '20161231';
GO
```
I do this, I have these objects.

Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.
```
GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA

GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA

GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA
```
Once I do this, I should see permissions, right? Let’s check.

I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.

Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).

You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.

#SQLNewBlogger

This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.

Learned something, showed it, and hopefully will remember it from now on.
December 29, 2016