Developing for Google Home: Getting Started

I recently blogged about the new Google Home I received for Christmas. In this blog, I'll talk about how developers can extend Google Home and teach it new conversational skills. Please note I am only 3 days in here, so this is just an introduction.

I'm a career software developer, so naturally I'm interested in extending Home further by developing new abilities for it. Amazon Echo is known to have an extensive developer and partner program with over 5,000 skills available; so what about Home? Google Home also has a developer program, which you can find out about at Actions on Google. Note, when you are developing for Home you are really developing for the Google Assistant, something that potentially can be used in additional ways (such as on Google Pixel phones).

There are several ways to develop conversation actions for Google Home and its Conversation API. One is with the Actions SDK, which includes a NodeJS client library and a web simulator. The other is API.AI, a web-based interface. API.AI is where I'm starting, and that's what I'm writing about today.

I've just started on learning, but I've already gone through the first tutorial and added a new conversation action that I can test in the web simulator or with my actual Google Home device. I can already tell this is going to be a great deal of fun.

Getting Started Sample: Silly Name Maker

Defining an Agent in API.AI

The API.AI site has a walk-through that gives you the steps to build a simple app named Silly Name Maker - and it's really simple - all it does is ask you for a number and a color, and constructs a silly name from that information. But, it does the job of getting you on your way. If you faithfully go to the API.AI site and enter the instructions, you will soon have an agent that looks like this:

Silly Name Maker tutorial sample in API.AI

and interacts like this:

User: Hey Google, Talk to Silly Name Maker.
Silly Name Maker: Hi! Welcome to Silly Name Maker!
Let's get started. What is your lucky number?
User: 22
Silly Name Maker: What is your favorite color?
User: Blue
Silly Name Maker: Alright, your silly name is Blue 22.
I hope you like it! See you next time.

Yes, it's not terribly useful but it does show you the ropes of defining an agent and running it. 

Intents

So what goes into making this agent? It consists of a number of intents. The first intent is named start_name_maker, and it's the first thing that happens after you invoke Silly Name Maker by asking Home to "Talk to Silly Name Maker". That's because it's tagged as a Welcome intent. All this intent does is welcome you to the agent, and prompt the user to select a number (and when the user does that, they'll be triggering the second intent). It's important for Google Home agents to clearly identify themselves to the user so they understand they're now interacting with an agent (app) and not Google.

start_name_maker intent

The second intent is called make_name, and it gets triggered when the user says a number or says "My lucky number is number". The intent can recognize several patterns of input, any of which will trigger it. Note how easy it is in API.AI to define user input - you simply provide samples of what someone would say, and then you can mark parts of that as parameter values. Parameter values have types; for example, @sys.color is a color and @sys.number is a number. 

Some of those "user says" patterns may provide parameter data - in this case, both patterns define the parameter $number. The intent has another parameter, named $color. Since $color is marked as a required parameter, and none of the defined "user says" patterns provide $color, Home will automatically prompt for it when this intent is triggered. That's why the example dialog above asks "What is your favorite color?" The make_name intent incorporates the parameters in its response: "Alright, your silly name is $color $number. I hope you really like it! See you next time!". Although it's not done in this case, it's possible to list multiple text responses for an intent (if more than one is present, one will be selected at random for the response).

name_maker intent

Now let's revisit the earlier sample dialog, with an understanding of what is happening behind the scenes:

User: Hey Google, Talk to Silly Name Maker.
[Silly Name Maker agent is launched. start_make_name intent runs,
and provides its response]
Silly Name Maker: Hi! Welcome to Silly Name Maker!
Let's get started. What is your lucky number?
[start_make_name intent ends]
User: 22
[make_name intent matches user input of number, and executes]
[$number parameter is from the user input]
[$color parameter value has not been provided yet, 
so API.AI prompts for it automatically]
Silly Name Maker: What is your favorite color?
User: Blue
[make_name intent provides its response]
Silly Name Maker: Alright, your silly name is Blue 22.
I hope you like it! See you next time.
[make_name intent ends]

Testing and Deploying your Agent

You can run the agent in a couple of ways. There's a web simulator on the right-hand side of the API.AI site you can interact with: just click the microphone icon and say something. You'll see how that was interpreted and what the response is. Click the Play link to have the response spoken to you.

Test Console built into API.API

Even better, you can test this on your own Google Home device. If you're logged into API.AI with the same Google account that your Google Home device uses, you can deploy it and go over and talk to the Home. Click Integrations on the left-side menu. On the list of One-click Integrations, click Actions on Google.

Integrations

On the Actions on Google dialog that appears, review the options (you can for example change the voice Home will use), then click Authorize at bottom right.

Actions on Google dialog with Authorize option

Now there is a Preview option available at bottom right. Click it. Preview is a temporary deployment (to do a permanent deployment, you'll need to first formally register your agent).

Actions on Google dialog with Preview option

Once you click Preview, you'll soon see confirmation that your agent has been published. Now you can walk over to your Home device and try it out. Begin with "Hey Google, Talk to Silly Name Maker."

Preview published confirmation

In my next post, I'll develop my first solo agent for Google Home.

At Home with Google Home

I just became the proud owner of a Google Home. This review will cover the initial experience of setting up and using Google Home.

Why Google Home?

First off, how did I end up with a Google Home, and not an Amazon Echo? The simple answer is, I received it for Christmas as a gift from my family. This year we celebrated Christmas a couple of days early so that our college-aged daughter Susan could join us. I'd overheard a few whispers here and there that "we got Dad something cool," so I knew there was some kind of special gift awaiting me - could it possibly be one of these home assistants? It turns out my family had purchased a Google Home for me. I've now had the Google Home for 36 hours, and in this post, I'll share what the initial experience has been like.

In November 2014, Amazon released Amazon Echo, their home smart speaker. It has been enormously successful for Amazon. Several former colleagues of mine who now work for Amazon have been regularly tweeting about the ever-expanding skills available for Alexa, the Amazon Echo assistant. Much more recently, in November 2016 Google released their competitor, Google Home. If I'd made the purchase myself, I admit I would have been torn: you can tell from reading reviews that both products are compelling with plenty of potential to do even more.

Reviews seem to suggest that both products are very good additions to the home and of course each maker has some advantages over the other. Both can play music or send things to your TV. You can order a zillion products from Amazon using Echo, and there's a vast network of partners providing support for just about every kind of smart home device there is. From Home you get amazing search and voice recognition plus the ability to ask general knowledge questions. The Home also lets you direct video from Netflix and other sources to your TV via Chromecast and control some home devices, including the Nest thermostat.

I've been aware of Amazon Echo and Google Home for the last year but hadn't had the opportunity to play with either before now. To be honest, I wasn't too keen to jump into anything voice-driven because I haven't had very good experiences with voice recognition in the past--this is my own fault, as I tend to talk fast or speak too low. But I know the technology has been getting better and better. From reading reviews, both Amazon Echo and Google Home are good useful products. Well, I was now the owner of Google Home so it was time to find out what all the excitement was about.

Unpacking

Home comes nicely packaged in an attractive white box which is very reminiscent of the experience you get with Apple products. Inside is the Google Home unit, a wall adapter, and some minimalist start-up instructions.

Home resembles an air freshener. It's white with an angled round top. The bottom colored band (gray on mine) covers the speaker. You can buy bands in other colors if you want to match your home decor. 

Google Home on my fireplace mantle

The Get Started card has just 3 steps: plug the power cable into the home, plug the other end in to a wall socket, and download the Google Home app on your phone.

The phone app detects your Home device and performs a quick test which causes your Home to make a sound--confirming you are connected to the right device.

You also configure access to your wireless network for the Home, choose a default music service, and tell it what address it's located at.

  

There are more things you can configure to get the most out of the device--see other sections below--but at this point you can already start using Home for music and search queries. From here on, you use the phone app to do further setup on Google Home such adding home devices and service accounts (see later below).

Basic Usage and Interaction

To get Google Home's attention you say a wake-up phrase of "OK Google" or "Hey Google," followed by your command or question.  I find "Hey Google" to be easiest to say.

As I mentioned earlier, I wasn't sure that *I* would get much out of a voice-driven product as I tend to speak too rapidly and softly. And how well would it do with a whole family of people talking at it? This turns out not to be a worry with Google Home: from the moment we plugged it in, everyone in our family has had no problems at all being understood. The voice recognition is really good, and it can pick up voice from quite far away.

The round top appears to be featureless white, but as you interact with Home you'll find it can do a variety of fun things with lights. As soon as Home recognizes the wake-up phrase, four lights appear to show it is active.

Google Home when active

The top of Home also responds to touch. You can tap the top to pause (or resume) playing music or video, or to interrupt a response. You can dial your finger around the top in a clockwise motion to increase volume or in the other direction to decrease volume. Of course, you can also do all these things by speaking to it.

From the reference card included in the box, here are some examples of commands you can give Home (in each case, preceded by the wake-up phrase):

"What does circumlocution mean?"
"What's the latest news from NPR?"
"Who won the Giants game?"
"How is the S&P 500 doing?"

Google Home also has great contextual memory. Once you are discussing something, it knows what "it", "he", or "she" means. For example:

"Who is president of the united states?"
"When was he born?"
"What is his favorite food?"

Since the phone app configuration includes telling the Home the address where it is located, you can ask it lots of locale-based questions:

"What's the temperature outside?"
"Where's the nearest supermarket?"
"What time is 'Passengers' playing?"

And there are also some things it can't do--yet:

"Give me directions to Panda Express" responds with an apology that Home can't give directions yet. This is kind of odd, since Google has fantastic direction-giving ability when you use it from a computer or phone. This is clearly coming, though. I have to remind myself that this product was only released last month.

Controlling the Thermostat
Home is clearly behind Amazon when it comes to partnerships that allow you to control a vast array of home devices, but they do have support right now for Nest thermostats, Philips Hue lights, Samsung Smart Things, plus the IFTTT online service.

No doubt this list will expand over time. In the meantime, I do have a Nest thermostat in my home so I configured that. In the Google Home app, I added the Nest device and provided my account information, and that was that.

Now I can give commands and queries like these:

"What's the inside temperature?"
"What's the thermostat set to?"
"Set the heat to 72 degrees"
"Make it warmer"
"Make it cooler"

I can see the day when I have more smart home devices and control them via Google Home, such as lighting and alarms.

Music

One of the most useful aspects of Home is playing music: that's where having this device in the home with a good speaker (as opposed to using your phone) really shines. The Home's speaker is pretty good and music sounds great coming from it.

In configuring Home with the phone app, I selected Google Play Music as my default music service, which automatically makes my Google Music library available to Home (alternatively, I could have selected YouTube Red, Spotify or Pandora). Now I can play songs from my library like this:

"Play my Christmas playlist"
"Skip this song"

Oddly, though, it doesn't seem I can play an individual song or album by name: "Play song" doesn't work. After some research, it appears you currently can only play playlists, not individual songs or albums from your Google Music Library. That's a bit of a downer, and hopefully will be addressed soon. I believe this may just be an issue with Google Play Music and not the other music services.

On the other hand, we've had great success just asking Home to play things that we don't even own in our music library. My wife has been getting hours of Christmas atmosphere with requests like these:

"Play some Christmas music"
"Play some instrumental Christmas music"

It's not really clear to me yet where this music is coming from--since I haven't paid for it. It makes me wonder if there's some kind of premium music trial going on that will eventually need to be paid for.

But wait, there's more. Radio is also available through Google Home, via the TuneIn service:

"Play KOST FM"

Video

I have a Chromecast device connected to my TV, and that is also something Google Home understands. After using the phone app to tell Home our Netflix account, I can now give commands like the following:

"Play Zootopia from Netflix on TV"
"Fast forward 10 minutes"
"Play 'What Does the Fox Say' from YouTube on TV"

I only have one TV and one Chromecast, in the same room as Google Home--but if I had a more elaborate setup I could tell Home where to play things:

"Play Zootopia from Netflix in Living Room"

Although video worked right away on my Chromecast, audio did not. It took me about a day of trial and error to get audio working (I had go to my LG TV Settings and turn speakers on instead of letting it default to integration with my home theater). This is an extra step I'd prefer to avoid but its not a bad reflection on the Google Home itself. When I next upgrade my TV and home theater, I'll be paying attention to Consumer Electronics Control (CEC). As I understand it, with the right set up playing video from Home can automatically turn on the TV and switch input to Chromecast.

Photos

Home can also play photos (from my Google Photos collection) on my TV via Chromecast. You can reference photos by date, by special occasion, or even by subject. I'm not sure how Google manages to find photos by subject, but it does. If (for example) I say "Play car photos on TV", it finds all my photos that contain an automobile. There's some kind of impressive analytics going on with Google Photos.

"Play photos from Christmas on TV"
"Play photos from December 22 on TV"
"Play car photos on TV"
"Play dog photos on TV"

Fun

The Home also has plenty of fun built into it, including some personality in the Google Assistant that powers Home. My son has been building up his comedic material by constantly asking Home "Tell me a joke" and getting a different joke each time.

Home can also play games. For example,

"Play mad libs"
"Play lucky trivia"
"Play crystal ball"
"Entertain me"

Google Home has the concept of applications, known as conversation actions. When you're interacting with an action such as Lucky Trivia (which is multi-player by the way), you're executing a conversation action. You don't have to say "OK Google" before each response, which is nice.

How Useful Is it?

I admit it, I expected Google Home to be an interesting geek toy but didn't really expect it to become an integral part of my home--but I was wrong. After just a few days, it's regularly used by my entire family.

Everything the Home does can also be done with my phone, so how useful is it, really? Well, very! Think about the number of times you pick up your phone or pull it out of a pocket--it's a lot. Just being able to call out voice commands is extremely convenient. When I first installed the unit, I wondered if it would be necessary to be right in front of it in order for it to recognize speech, but it turns out it does really well even when we call out commands from another room.

I also wondered how strict the Home would be about the format of commands and queries: would we have to memorize exact patterns of words in order for it to understand us? Many voice-driven devices in the past have ended up "training the user" to speak the way the device can understand. Fortunately, that isn't necessary here. The Home has excellent natural language speech recognition and is very tolerant of variations in phrasing. All of my family members have been able to interact with it right from the start.

One limitation of the Home is that it is linked to a single account--in our case, my music library, my Netflix account, my calendar, etc. There's no support as yet for switching profiles for different users. I presume this will come in time.

One other limitation that I don't expect to get resolved is there's no support for competing services like Apple iTunes. That's a shame, because the rest of my family is on Apple devices. I think that's a shortcoming of any home assistant, whether from Google, Amazon, or (coming soon) Apple: you won't necessarily be able to access every service you want to.

In my next blog post on Google Home, I'll talk about how developers can teach Google Home new actions.

The Geek's Guide to Dieting, Part 3: Diet-friendly Fast-food Options

This is part 3 in a series on dieting for geeks. Today I'll discuss diet-friendly lunch options when eating out.

Buffalo Wild Wings

Buffalo Wild Wings most popular items are the boneless wings with one of the many sauces. You're essentially looking at 100 calories per wing here, so choose a smaller-size portion such as the snack size (5 wings, 1 sauce) and you'll be okay.

Buffalo Wild Wings Snack-size Boneless Wings (500)

Chili's

A lot of the delicious food at Chili's is out of reach: we're talking calorie counts in the thousands. However, they do have some low-calorie options that are quite good. Although they don't generally list calories on the menu, they do have a Lighter Choices section which does. Options here are Spicy Chile Chicken (420), Ancho Salmon (590), Classic Sirloin with Grilled Avocado (420), Mango-Chile Chicken (510), Mango-Chile Talapia (520), Grilled Chicken Salad (440), and Margarita Grilled Chicken (590). I'm personally fond of the Mango-Chile Chicken.

Chili's Mango-Chile Chicken (510)

In-n-Out Burger

In-n-Out Burger can be done, but you'll probably be getting a single item rather than a full meal. A cheeseburger is 480, but protein-style (no bun) is 330. A Double-Double is 670, or protein style is 520. French fries are 395.

In-n-Out Protein Cheeseburger (330)

Jersey Mike's

Jersey Mike's is hands-down my favorite sandwich chain, but like most sub places the majority of the food is way up there in calories. However, there are some lower-calorie options. Start by getting a mini-size sandwich on whole wheat. A Turkey (#7) mini on whole wheat, prepared Mike's Way, is 410.

Jersey Mike's #7 Turkey Mini on Wheat, Mike's Way (410)

Kentucky Fried Chicken

KFC might seem like something to avoid on a diet, but you can make it work. Original-recipe chicken is lower than you might think, at Breast (320), Thigh (270), Drumstick (160), Wing (120). Individual sides really vary, so choose carefully. On the higher side we have Cole Slaw (170), Mac & Cheese (170), and Mashed Potatoes & Gravy (120). On the low side is Corn on the Cob (70) and Green Beans (25) which are quite doable.

KFC Original Chicken Breast (320)

Panda Express

Panda Express nicely lists the calories on their menu. In particular, take note that the popular sides (white rice, fried rice, chow mein) are 380-520 calories alone! That means you're in trouble before you even select an entree. Fortunately, there is the mixed veggies side option which is only 80 calories. If you go with mixed veggies, you can enjoy an entree or two without killing your calorie count. The entries with lowest calorie counts are broccoli beef (150), string bean chicken (190) and mushroom chicken (170).

Panda Express Broccoli Beef (150)

Panera

Panera is one of my favorite places to eat, but some of their yummy food is off-the-charts high in calories (mac and cheese is 450-900). Be careful of sides: a baguette or soft dinner role on the side will add 180/190 calories to your meal. But you can make Panera work if you focus on the lower-calorie choices, and they helpfully have the calories listed on the menu. The Green Goddess Cobb Salad with Chicken makes a filling lunch at 520 calories. If you get a Pick 2, good choices for soup are Turkey Chili (170) or Broccoli Cheddar soup (230). For Pick 2 sandwiches, best bets are Steak and Arugula (250) or Turkey Breast on Whole Grain (280).

Panera Pick 2 Steak & Arugula Sandwich (250)+Turkey Chili (170)

Rubio's Coastal Grill

Rubio's is a popular Mexican food chain headquartered in Southern California. Famous for their fish tacos, Rubio's offers delicious fresh food and prominently displays calories on their menu; unfortunately, much of this yummy food is off-the-charts high in calories. Good options for the calorie-conscious dieter are individual tacos such as the Wild Mahi Mahi Taco (240), Classic Steak Taco (200), Classic Chicken Taco (250), Grilled Gourmet Taco with Chicken (330). The Grilled Street Tacos entree (three snack-size tacos) is also diet-friendly, at Chicken (100) or Steak (120). The dish that I always get, however, is the Balsamic & Roasted Veggie Salad with Chicken (310): it's a large bowl of food and is really good.

Rubio's Balsamic and Roasted Veggie Salad (310)

Sonic

Sonic is another tasty place where most of the menu is out of reach (don't go near a milkshake), but there are options for you. For burgers, your best bet is the Sonic Jr. Deluxe Cheeseburger (420) or Sonic Jr. Double Cheeseburger (520). For chicken, the Classic Grilled Chicken Sandwich (450), Chicken Strip Sandwich (440), or Crispy Chicken Sandwich (580).

Sonic Classic Grilled Chicken Sandwich (450)

Subway

Subway isn't my favorite sandwich place, but given its size and popularity I thought I should list it here. Start with 6" sandwiches on wheat, and beware sauces that can add hundreds of additional calories. Sandwiches on Subway's "Fresh Fit Choices" menu include Turkey Breast (280), Black Forest Ham (290), Subway Club (310), Oven Roasted Chicken (320), Roast Beef (320), Rotisserie-style Chicken (350), and Sweet Onion Chicken Teriyaki (370).

Subway 6" Turkey Breast on Whole Wheat (280)

Well, there you have it--fast food on a diet can be done, if you look for the low-calorie options. I'll be expanding this list over time.

In Part 4, we'll look at surviving the holidays.

The Geek's Guide to Dieting, Part 2: Diet-friendly Foods

In Part 1, I shared my diet plan. Here in Part 2, I'll share some of the foods I've found that make my reduced-calorie diet work.

Breakfast: Dannon Light & Fit Yogurt (80 cals), Coffee with Creamer (40 cals)

If I wasn't dieting, I'd be enjoying a Jimmy Dean sausage, egg and crossant sandwich most mornings for breakfast. But I am dieting, so that's out. Breakfast is the most challenging meal of the day for me: I'm only allowing 120 calories for it, and there's a long stretch between it and lunchtime.

What works best is yogurt, but which one? The craze right now is Greek yogurt--which I really don't like. Fortunately, Dannon's Light & Fit Greek yogurt (80 calories) tastes great. In fact, they've done something remarkable with the huge variety of delicious flavors they offer. It comes in Banana Cream, Blackberry, Blueberry, Caramel Macchiato, Cherry, Cherry Chocolate, Citrus Blend, Key Lime, Peach, Pineapple, Pomegranate Berry, Raspberry, Raspberry Chocolate, Salted Caramel, Strawberry, Strawberry Banana, Strawberry Cheesecake, Toasted Coconut Vanilla, and Vanilla. They're always experimenting with new flavors too, so it doesn't stop there. I've recently seen limited edition flavors as well such as Pumpkin Pie. I've tried about half of these so far, and nearly all of them are really delicious.

But it doesn't stop there. There's also Dannon Light & Fit Yogurt Mousse (also 80 calories), which tastes more like a dessert. These come in Cherry Cheesecake, Coconut Cream, French Vanilla, Lemon Meringue, Blueberry Tart, Raspberry & White Chocolate, and Salted Caramel flavors.

You can buy Dannon Light & Fit Yogurt in packs of 6 or individually.

With my yogurt I'll have a cup of Breakfast Blend coffee (2 calories) from my Keurig and some creamer (30-40 calories).

Snack: Pickles (0 calories) or Tangerine (40)

Pickles have a special place in the world of dieting because most varieties have zero calories. Don't believe me? Check out the nutrition facts label on a jar of pickles and see for yourself. How can this be? Well, it turns out the energy it takes to digest pickles offsets the calories, coming out to a net of 0 calories. For me, pickles are a welcome snack in that long morning stretch between breakfast and lunch.

Pickles: 0 calories

Another good snack are cuties (tangerines), only about 40 calories each.

Cuties: 40 calories

Lunch: Lean Cuisine Microwave Meals

I work from home most days, so microwave meals are typically what I have for lunch. If dieting were not a concern, I'd favor meals from Stouffers and Marie Callenders. But I am dieting, so I need leaner choices. Lean Cuisine to the rescue! Lean Cuisine has a large offering of meals with low calories and many of them are quite good. I'm especially fond of their under-200 calorie meals such as Herb Roasted Chicken and Steak Portabella, because these leave me enough calories for a mid-afternoon follow-up meal. Also good from Lean Cuisine are their Chicken Parmesan (310 calories), Ranchero Braised Beef (250) and Fiesta Grilled Chicken (250). Another note in Lean Cuisine's favor is honest labeling. I can't tell you how many times I've bought a microwave meal from some other brand only to find out the calories on the front were misleading due to how they figure portion size. With Lean Cuisine, they're very clear with their calorie labeling.

Good meals under 200 cals from Lean Cuisine

Very good from Lean Cuisine in the 250-310 cal range

or:

Boston Market Turkey Medallions--a good portion of food (Turkey, Mashed Potatoes, Gravy, Vegetables), only 230 calories.

With one of these meals for lunch, I'm satisfied until my mid-afternoon meal.

Lunch Part 2: Mid-Afternoon Meal: Ready-Pac Bistro Bowl Salad

By 2:30 or 3:00pm I'm getting hungry again, but I have room for another meal because I kept my lunch to 200-300 calories. Ready-Pac Bistro Bowl Salads make a great follow up. They have a large variety in the 210-330 calorie range and most of them are quite good. There's a good variety and they are easy to prepare: just mix the toppings and dressing and stir.

Bistro Bowl Salads

My favorite is the Chef Salad (240 calories). If I need to stay on the lower side of calories, I'll go with the Caprese Salad with Balsamic Dressing or the Walnut Cranberry salad with Raspberry dressing (each 210 calories). The Santa Fe, Bacon Blue Wedge and Turkey & Bacon Cobb are also good, but I'll have those less often because they are 290 calories.

With my mid-afternoon salad, it's smooth coasting till dinner time.

Dinner: Home Cooking

My wife usually cooks for dinner, so it could be something different each time.  Fortunately, she's good at helping me figure out the calories. On my diet plan, I've kept 600 calories (half my daily allowance) reserved so I can have a satisfying dinner.

Dessert: Sugar-free Jello and Diet Snapple

In the evening, I may have a sugar-free Jello (10 calories) or a Diet Peach Snapple (10 calories).

Well, there's my default meal plan. Of course, I sometimes eat out or have to drive in to the office, which changes things. In Part 3, we'll explore survivor tips for eating lunch at fast food places.

Azure SQL Database Cross Database Joins with Local Databases

Organizations that make use of the cloud sometimes have to integrate that information with local databases. If you have a need to do cross-database joins including an Azure SQL Database, you might be wondering how that can be accomplished. One solution that works is to go to a local database and add a linked server to your Azure database.

Defining a Linked Server with sp_addlinkedserver

To add a linked server, open your local database in SQL Server Management Studio and execute a sp_addlinkedserver statement. You'll need to specify a number of parameters:

• @server : the name you want to use to refer to the database in queries. Use a simple, memorable name. In our example, we'll use abc.
• @datasrc : the data source (Azure database server), such as clients.database.windows.net
• @catalog : the database to connect to. We'll use ABC_PROD in our example.
• @provider : set to SQLNCLI.
• @srvproduct : set to empty string.

sp_addlinkedserver @server='abc', @datasrc=N'clients.database.windows.net', @catalog=N'ABC_PROD', @provider=N'SQLNCLI', @srvproduct=N''

Defining a Linked Server Login

To go with your Azure linked server you'll need to define a login for it. Use a sp_addlinkedsrvlogin statement with these parameters:

• @rmtsrvname : name of the linked server (same value you used for @server in the previous section). Example: abc
• @useself : set to 'false'
• @rmtuser : username. Example: dbrpt
• @rmtpassword : password. Example: s4ssafr4ss!

sp_addlinkedsrvlogin @rmtsrvname='abc', @useself='false', @rmtuser='dbrpt', @rmtpassword='s4ssafr4ss!'

Viewing Linked Server in SSMS Object Explorer

After performing the above steps, refresh your SSMS Object Explorer and you should see your linked server. If you defined everything correctly, you'll also see the database objects available, such as tables and views.

For troubleshooting, there's also a handy Test Connection option if you right-click the linked server name.

Querying the Azure Linked Server

To query your linked database, use the prefix [server][database] before specifying schema/table, where server is the value your used for @server and database is the value you used for @catalog in the first step above. For example,

SELECT * FROM [abc].[ABC_PROD].[dbo].person

Including Azure Linked Server in Cross-Database Queries

Now that you have a working linked server, you can include it in database queries that join to the local database or other linked databases. In the query below, the Azure linked database is joined to a local database table.

There you have it: queries that combine cloud and local data through the use of a Linked Server.

The Geek's Guide to Dieting, Part 1

I started dieting 3 4 months ago, and as of today I've lost over 30 40 lbs. I thought I'd share some tips on how I've been able to reduce weight and stick to my diet plan. In Part 2, I'll also discuss some of the diet-friendly (yet tasty) foods I've latched on to.

I've lost more than 2 bowling balls of weight

Let me begin with some words of encouragement: you can do this! If I can do it, anyone can. As you'll read below, I'm about the worst candidate for weight loss you can imagine, yet I'm doing it. You can too.

The Geek's Dilemma: A Sedentary Lifestyle

I've certainly attempted diets before, usually ending in failure. One thing I learned from those experiences is that temporary diets are destined to fail, because when they're over you revert back to life as usual. Instead, you've got to make permanent lifestyle changes that you can stick with. This time around, I've been able to do that and that's why it's been working. 

As anyone who's ever made a New Year's resolution knows, lifestyle changes are hard. I think it's even harder for geeks who can't tear themselves away from their work. I'm a software developer with project responsibilities and looming deadlines, and that means a sedentary lifestyle. I spend 12 hours+ a day sitting. There's not a whole lot of exercise going on, other than conjugal visits (heh heh). And realistically, that's not going to change much. I may take an occasional walk but it's been hard to get into a daily exercise routine. That leaves food as the logical area of focus for me, and that's where I've had some success.

Another challenge for me is that I really love food! My wife is an excellent cook. Gourmet cooking is her hobby, so there's lots of good food at home. Half the time Food Network is what we're watching on TV. Plus, we like to dine out. We usually dine out as a family every Sunday.

Lastly, I have Type 2 Diabetes which really complicates things. With diabetes you're needing to eat often, but with a diet you've got to eat less. What prompted me to start dieting again was the realization that I've been putting on a great deal of weight year after year since the onset of diabetes about 5 years ago. 

Limiting Your Daily Calories

But what kind of diet to go on? There are endless diets to choose from, but I had some specific criteria to narrow things down. I needed something that would be:

1. Simple

2. Not expensive

3. Not unpleasant

4. Not disruptive to family dinners

This criteria quickly eliminated many diet approaches. Special diet foods were out: they cost a lot of money, taste foul, and complicate family dinners if I have to eat something different from everybody else. 

I ultimately settled on something very, very simple: limiting the amount of calories I eat each day. Simply eating less has a lot going for it. It doesn't restrict what you eat. When eating with others, you don't have to be eating something different.

I started out at 1800 calories a day, but wasn't seeing much weight loss so I reduced that until I started getting results. Since getting down to 1200 calories a day, weight loss has been around 10 lbs a month. (Note: if you're considering limiting your daily calories, you should set a limit that makes sense for your height and weight. There are online calculators for that, or even better discuss with your doctor.)

Tracking Your Calories

With a firm calorie/day goal in mind, the next question is how to keep track of everything you put into your mouth. These days, that's fairly easy. Many restaurants now display calories on their menu and/or have nutrition information on their web sites. Food products you purchase list calories in their Nutrition Facts labels. For dishes prepared at home, there are apps that let you enter ingredients and calculate calories for the dish.

Speaking of apps, an invaluable tool for tracking calories is My Fitness Pal, a free phone app. It lets you set and track your daily calorie goals and track what you eat (with an enormous database of foods and calories). It also keeps track of your weight goal and lets you enter your weight as it changes. My Fitness Pal is a key element to my diet plan.

  

Tracking meals in My Fitness Pal

Weight reduction progress in My Fitness Pal

Sticking to a Daily Routine

I settled into a daily eating routine that has become ingrained in me, which is very helpful to sticking to the diet plan. Here it is:

Breakfast (120 calories)

Yogurt

Coffee with creamer

Mid-morning snack (0-40 calories)

Pickles (0) or Tangerine (40)

Lunch, Part 1 - Noon (250 calories)

Microwave meal

Lunch, Part 2 - Mid-afternoon (210 calories)

Salad

Dinner (600 calories)

Whatever's being served, but not more than 600 calories

Evening (20 calories)

Diet Snapple

Sugar-free Jello

A big challenge for me is the stretches between eating, hard on me due to my diabetes and fluctuating glucose levels. I decided I would tough out the morning with a small breakfast and a mid-morning snack of pickles (which are 0 calories) or a tangerine (40). The rest of the day is easier as I eat progressively more. For lunch I have two 200-250 calorie items, one at noon and another mid-afternoon. Dinner is best of all, where I still have half of my daily calories available.

Each morning I weigh myself. Often it's good news, and I enter my new reduced weight in My Fitness Pal. Some days my weight stays the same, and sometimes it even jumps up a bit--it's a finicky thing, that scale. But I don't let that bother me: I stay on my routine, and each month I've lost another 10 pounds.

Are there ever exceptions to the daily plan? Sure. Occasionally, I will have dinner out with my wife. On Sundays, I have dinner out with the entire family. On those occasions it's tempting to forego the daily routine, but I've been careful to keep the calories in check. If I eat more calories than usual for lunch, I'll eat fewer for dinner. Sometimes I'll only eat half of what I'm served at a restaurant and take the rest home, where my 13-year old son is happy to take it off my hands.

Well, there you have it. A diet that works for a geek that doesn't cost extra money. Now that I've been doing this for three months, it's become easier. My body has become used to eating less each day.

In Part 2, I'll cover some of my favorite diet-helpful foods.