Voice of the DBA

Category: Blog

Back Across the Pond

This coming weekend is SQL in the City – London 2012. Friday, July 13, and Saturday, July 14, I’ll be at One Wimpole Street in London. This is the Royal Society of Medicine, a fantastic venue for events near Oxford Circus. I’m looking forward to the event and the venue.

This is the first stop in 2012 for our SQL in the City tour. These are the only two days in Europe and then we come back to the US with a 5 city, 11 day tour of the US. I’m looking forward to the events, but not the travel. It’s a lot of flights in a short time, and quite a few hotels. Fortunately I’ve consolidated down to one carry on bag and a backpack for travel, so I should be in and out of airports quickly.

This week I fly overnight on Wednesday to arrive in London and recover on Thursday before the Friday and Saturday events. Then I’m back home on Sunday, just in time to start another week of work. A quick trip, and likely not much sightseeing for me, but if you’re up early, you might find me running down Oxford Street in the mornings.

I’ll be delivering two sessions in London. They are both new and while I’ll link them to a few of the Red Gate tools, these aren’t sessions built around our products. These are sessions that talk about the issues of Disaster Recovery and Contained Databases.

If you’re coming to one of the London events, please feel free to grab me and shake my hand. I’ll be the guy in the Hawaiian shirt and cowboy hat

July 9, 2012
The Standing Desk – In the Light

It’s been almost a month since I started the Standing Desk experiment. My first setup was down in the basement, since that was the desk that fit my monitor stand. In the middle I had a week of vacation, where I didn’t do much standing at the desk, though I did have my laptop on the old desk.

This was the setup upstairs

That has now been replaced with this:

I spent a portion of the Fourth of July holiday moving my old desk downstairs, and then bringing the smaller one up stairs, setting up all the monitors again. A cleaned things up and minimized the setup, and learned a few things.

First, put the bigger, nicer monitors on top. I had them reversed in the basement, which wasn’t great. The smaller ones have less stuff on them, which isn’t great when I primarily use the top two for work (browsing, editing, email).

As you can see, the setup isn’t a lot different, but I did move a few of the podcast items off and I have a touch more space.

It’s also better to have the start bar on the top screen for me. I can more easily see the programs running without leaning over. Plus it’s cool to see the programs shoot out from the bottom and maximize.

In the last few weeks I’ve also learned the shoes matter. At first I thought it was because of the basement floor, but I found that the padding helps. I’ll look around for floor mats to help up here and see if that makes a difference.

The books and boxes work, but they aren’t great, and I’d like more of a surface at keyboard level for my coffee, water, etc. Whether I build or buy something is up for debate. I’d like to build something, but it’s time. I’m pressed a bit and if I can find something cheap, I might grab it.

Plus this gives me an excuse to hit the new IKEA store in Denver

July 5, 2012
Fourth of July Bloopers

Here you go. The raw ones.

It’s a holiday, and usually I’m spending time with the family and enjoying the day.

In all seriousness though, this is an important day for US citizens. The birthday of our country. Take a moment and read the Declaration of Independence or the Constitution and remember how our country was founded and the principles we should hold dear. Interpret things today, but remember that this country was founded on the idea of freedoms.

Despite any differences with other citizens, we should celebrate our country.

July 4, 2012
Decryption and CASTing
In my last encryption post I showed how to encrypt and decrypt data with a symmetric key. However there was a piece of the explanation I left out. If you look at that post, suppose that you ran this query after you’d encrypted the data:
```
-- decrypt the data
select 
  id
,firstname
,lastname
,title
,Salary = DecryptByKey(EnryptedSalary)
,EnryptedSalary
 from Employees
go
```
The results wouldn’t be what you’d expect:

The binary data is returned, which isn’t rendered correctly. The salary column is the decryption, and the EncryptedSalary is the encrypted data. Note they are different.

This stumped me for awhile when I was playing with encryption and I checked the dercryptbykey page thoroughly before I realized that the return type was varbinary and needed to be CAST.

If I cast this back to nvarchar, I get the data:
```
select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as nvarchar) 
, EnryptedSalary
 from Employees
```
In my example, I CAST to nvarchar, and then to numeric, mostly for clean coding. This is numeric data. Can I cast directly?
```
select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as numeric(10,4))
, EnryptedSalary
 from Employees
go
```
No. I get an error.

Msg 8115, Level 16, State 6, Line 1

Arithmetic overflow error converting varbinary to data type numeric.

This isn’t a valid CAST, so I need to double up the CASTs as shown in the original post.
July 3, 2012