Derived column IF expression

I can never remember the SSIS if syntax in the derived column control, so Just so I can remember it myself 🙂

The syntax is like this:

(compare) ? true : false 

So if x equals  5 and y equals 1, then this will return x

(x>y)?x:y

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 🙂

Setup the Membership and Roles features in ASP.NET

There are a lot of tutorials about how to use the standard membership and role features, and the custom controls that comes with them in ASP.NET.

There is however, a lack of information about how you setup and config your database and your web app, so you can use it with your standard MSSQL server.

First you need to run the ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe), and point it to the database you are using. It is located here: C:\\%windir%\\Microsoft.NET\\Framework\\<versionNumber>\\aspnet_regsql.exe

Aspnet_regsql creates the tables and stored procedures, that your web app needs for you to use the standard controls.

Next you have to tell your web app where it can find the database with your membership information. You do that by adding these lines to the webconfig:

< connectionStrings>
<
remove name=LocalSqlServer/>
<
add name=LocalSqlServer connectionString=Server=servername;UID=your userid;PWD=your password;initial catalog=your catalog/>
…(add your other connection strings here)…
</connectionStrings>

You are now ready to start using the standard ASP.NET membership and role custom controls.
You can start here: http://www.asp.net/learn/videos/video-45.aspx

Maximum request length exceeded

When I was trying to deploy a report to the report server today, I got the following error message:

There was an exception running the extensions specified in the config file. —> Maximum request length exceeded. 

First I was a bit surprised, because a report project doesn’t come with a config file.

I turned out, that it wasn’t a specific Report server error, but a common IIS error.

The IIS has a default configuration, which specifies, that it can only accept a specific amount of data pr. Request.

I guess I should have known that, since I did a lot of web programming to the IIS at my last job.

Of course there is an easy solution. Find the web.config to the report server. It is somewhere around here.  

C:\\Program Files\\Microsoft SQL Server\\MSSQL.2\\Reporting Services\\ReportServer

Open it, and change the line that says something like this: 

<httpRuntime executionTimeout = “9000” /> 

And change it to:

<httpRuntime executionTimeout = “9000” maxRequestLength=”500000″ />

This changes the request length to 5MB. Change the value to your needs.

Changing the web.config, should by default restart your webapp, but run an iisreset from your command line to be sure.

Problem solved!