Category Archives: General

Database development with SSDT and Azure

One of the new things in the development tool to SQL server 2012 (SSDT) is the database project as shown in the picture below. Before 2012 it was a part of visual studio, but now you can get it for free in the data tools.

I’m guessing that that is one of the reasons for the name change from BIDS 🙂

But I’m rambling. Let me start by describing a typically way of database development.

  • You develop your database  in a development environment with management studio
  • When you want to deploy, you create a change script that will be executed on the production server.(There may be servers in between like test, preprod etc., but let’s not add those into the mix)

Well, that’s pretty much it.

The updated database might be used by a lot of other applications. Maybe the database is even the main part of a specific application.

There are a lot of problems in regards to this way of developing. Some of them are

  • There is no test of references in Management studio. If you change a table that is used by for instance a view, you won’t automatically catch it.
  • The change script might be error prone. Especially if there is no connection between develop and production servers, so you can’t compare them with a tool like Redgate or Atlantis.
  • There may be a “drift” in the production database, which mean that a DBA for instance can have added an index to the production server, because he wrongly thinks he knows more about it than you 🙂 or Maybe someone created a view to be used for reporting etc.
  • You don’t have source control.

That’s just some of the problem you will have with that kind of database development. The above problems and many others are addressed in the SQL server database project.

Let me demonstrate some of it with an example.

Let’s first create the project. If you look at the screenshot below you’ll see that there are 2 SQL server menus. The reason for that, is that I have visual studio 2010 installed along side with data tools. The project that is marked is the new one from data tools, and is of course a newer and better version. Select that one.

Figure 1 – Create the project

When you have created the project, you have an empty project. In my case I want to continue working on the Azure SQL project I started on in a previous blog post. So I just right click the project, click import, and type in the connection to my Azure database, and click start.

Figure 2 – Import the Azure database

And badabim. Now I have my database in my development tool, and I can add it to source control alongside my other projects. All my database objects are now shown in the solution explorer. There are about 100 different object types. Right now, I only have one table, but that is fine for my example.

If I double click the product table, I get the view as shown below.

Figure 3 – The designer

You can edit the table either in the designer at the top, or in SQL at the bottom.

You can build your project, and you will get errors, if you are using keywords that are not available in the target platform or have an invalid reference etc.

And regarding the target platform. You can change it in the properties window. As shown below.

Figure 4 – Change the target platform

If you change the target to Azure, you will get errors if you don’t have a clustered index on all of your tables, since that is a requirement in SQL Azure. Likewise, if you use file groups in Azure, you will get a compile error since that of course make no sense in Azure etc.

In my example, I would like to work with stocks. I delete the product table, and add two new, so I get what is shown below.

Figure 5 – The new tables

I can now from my development tool, do a schema compare and synchronize with the development/production server.

Figure 6 – Schema compare

In my example, I just have the Azure database, so I just synchronize that.

I can look at my azure database and see that it has been synchronized.

Figure 7 – The azure database have been updated. Nice 🙂

So the conclusion: I must admit I’m really exited about the update of the database project, and the fact that it is now free to use along with the other data tools.

It addresses a lot of common problems when doing database development that you would normally have.

I can’t imagine myself starting a 2012 database project, and then not use this tool. No matter if it is a DWH, BI or application project I will be working on.

SSAS translation problems with Excel

I just revisited an old customer, where they had a setup with a SSAS 2008 and are using Excel 2007 as a frontend.

They had a problem when they tried to log on to one of the cubes. They didn’t get any errors, but when they had filled out all the connection information, they just kept getting prompted with the prompt below:

They could see the cube with all their other client tools.

After some debugging, I found that the error was caused by a translation on a measure group.  Apparently Excel 2007 can’t handle measuregroup name translations. I really have to test on another cube, if it that is a general problem, or if it just is a special case

At another consultant job some months  ago, I also experienced problems when working with translations in SSAS 2008 R2 and using Excel 2010 as frontend.

I guess the take away is, that if you have some strange problems  when using the cubes from Excel, but it works normally with other client tools, then the problem properly lies in the translations.

region->kommune->postby

In my daily work I often have to link something to a location in Denmark, and typically in the form of Region->Kommune->Postby.

Every time I start on a new project, I always find myself goggling for a file or something I can use for that, and Always without success.

So I decided to create one myself, so I have one. You can download it here: RegionInfo.

Please leave a comment if you are using the file, or if you want it in a different format.

Something for my wish list 🙂

I would be cool if there was a web service(or a file you could download) where this information was available for the whole world. Not in the form of the Danish Region->Kommune->Postby, but in the form that would make sense for the different countries. Something like this maybe:

  • Country- Region->Kommune->Postby
  • Country-State-City
  • Etc etc. 

Ofcourse there had to be some metadata attached to it, which specified what the name of the level is for the different countries.

A super cool feature could also be, if all these data  came with a set of longitude and Latitude coordinates, that specified the location on a world map, hence a lot of application today creates data based on a point in a GPS or in an GIS application (maybe with Google or MS maps) .

Well, that is just something that could be extremely useful for me. If you know of any such web service, or anything related, please drop a comment or an e-mail 🙂

Try Catch in VBA

I’ve done a little VBA (Visual basic for applications) programming lately, and today I ran into a problem, where I wanted to make a simple try catch.

The error occurred because the users put letters instead of numbers into a specified field, so I just wanted to do some simple user validation with try catch.

Apparently, there is no such thing as try catch in VBA.

Instead of try, you have to use the”on error” statement. So a Try Catch will look something like this.

”…some code…
On Error GoTo ErrHandler:
”…The code, where you might get a run time error

ErrHandler:
    ” Some error handling code
Resume Next

When an error occurs, the code will jump to the ErrHandler label, and the resume next statement will continue to run the rest of the code.

I guss that is nice to know 🙂

New job

Long time no see. It’s been a long time since my last post. That is partly due to my new job. I just changed workplace from one of the biggest software companies in Scandinavia, to a little software consulting company with 18 employees.

My short-sighted plan with this blog, was actually to write about my points of view on software architecture, and what I thought tomorrows killer applications would look like.

Realizing that I don’t have time for that, with a newborn child, a new job, and some other small project on the sideline, this will blog will instead serve as a place where I post code tips, and “strange” behaviours in .NET, that I come across, and how to solve them.

As I get some projects up and running, this web server will also serve as a test bed, and a place to download sample aps, and sample code.

Well, that’s all for now folks.
Over and out!

New blog

Hello, and welcome to my blog.

You can read what it is all about, in the about section. But for now, it will be mostly sharepoint 2003, since that is what I am working with at the moment 🙂

Developing to sharepint 2003 can at times be a challenging task, since it lacks documentation, and can sometimes come with useless error messages, but I guess you can read more about that here later 🙂

Enjoy