Connection strings used for logging should not enlist in transactions

I was just reminded of an old truth when it comes to logging errors in a database: Do not enlist the error logging operation in the current transaction, if there is one! The default is to do just that, so be sure to add Enlist=false in your connection string used for logging:


    <add name="myDb" connectionString="..."  providerName="System.Data.SqlClient"/>
    <add name="logging" connectionString="...;Enlist=false"  providerName="System.Data.SqlClient"/>

If you don’t, then the error logging will be rolled back with the transaction in case of an error. Not so good…

Also note that it’s a good idea to use separate connection strings for the normal database operations (which should be done inside transactions) and the logging operations (that shouldn’t).

Now that I have written this down I will hopefully remember this the next time I do error logging in a database…


AutoHotkey – the essential tool for all “automate-it-all” practioners

AutoHotkey logo


This post is about a tool I always feel very crippled without, namely the incredibly useful AutoHotkey. I always install this onto new machines and I rely on it heavily every day. The strange thing is that rather few people I meet know about it so I thought I’d remedy that ignorance somewhat by posting an overview here on my blog.

So what is it? AutoHotkey allows you to map keypresses to custom macros.

This key press mapping works in any application by default and the macros that are possible are very flexible.

Some examples

The most common use is probably to start applications for a given key combination. Here’s one I use:

!#n::Run Notepad++

This starts Notepad++ when I press Ctrl-Alt-N

You can also write a little more complex macro code (the macro language is a custom one for AutoHotkey, here’s some documentation)

	FormatTime, CurrentDateTime,, yyyy-MM-dd
	EnvGet, UserName, UserName
	SendInput /%CurrentDateTime% %UserName%

This inserts a timestamp with my username when I press Ctrl-Alt-Enter.
Example: /2013-11-02 emila

Note that this works in any application since AutoHotkey sends standard Windows messages simulating keypresses for the text. So there’s no problem to use this macro in Notepad, for example.

To take this one step further, the key code trigger does not have to be based on qualifier keys such as Ctrl, Alt etc, it can also be a sequence of standard characters. Here’s one example of this:

; Today
	FormatTime, CurrentDateTime,, yyyy-MM-dd
	SendInput %CurrentDateTime%

This creates a macro that inserts the current date every time I type the sequence “tdy” followed by a word delimiter (space, tab, dot, comma, etc). This simple macro is probably the one I use the most, it’s so incredibly useful to have easy access to the current date when taking notes, creating folders, etc.

I also have a few code snippets I use a lot when programming:


This way I never feel tempted to write string comparisons such as if (s == null) { ... }, it’s just as easy to write if (String.IsNullOrEmpty(s) { ... } using my snippet. And this kind of snippet works even in Notepad πŸ™‚

This ability to replace character sequences is also very useful for correcting my common spelling errors:


I try to detect common operations that I often perform that can be automated and try to write an AutoHotkey macro for them. An example of this is that I have noted I often have to write a valid Swedish social security number (personnummer) when testing applications I write. This can be a pain since the number has to end with a correct checksum digit, so I wrote a simple web service that creates a random SSN and returns it. This service can be called from AutoHotkey like this:

; Anropa web service fΓΆr att skapa personnummer
	EnvGet tmpDir,TEMP
	FileRead, pnrRaw, %tmpDir%\random.pnr.txt
	StringReplace, pnrClean, pnrRaw, ", , All
	SendInput %pnrClean%

This is really convenient, I just type “pnr” and it’s replaced with a valid SSN. This really lowers the mental barrier when testing applications where this data is required, resulting in better testing. (Mental barriers when testing applications are very interesting and is perhaps worth a separate blog post some time…)

Summing it up

The above examples absolutely just scratch the surface of what you can do with AutoHotkey, so why not give it a try? It’s free, so it’s just a matter of downloading it and start experimenting. Download it from its home page.

Final tip

I like to have all my computers share the same AuoHotkey setup so I have created my main macro file (general.ahk) in a DropBox folder. I also create a shortcut in my startup folder (find it by “running” shell:startup using Win + R) with this target string:


Since AutoHotkey associates itself to the “.ahk” file extension, this is enough start start the script on startup. Any change I make to the macro is automatically propagated to all my computers.

Good luck with your automations!


Tablet platform overview – iPad, Android and Windows 8

This post will be an opiniated discussion about the advantages and disadvantages of the different tablet platforms out there. This is not the kind of post I normally write but for once I feel the need to express my personal opinion about a subject, rather than solving a particular programming problem which is my normal kind of blog post. The reason for this is the continuing bashing of Windows 8 tablets (especially the RT variant) that occurs online and in printed media. As it happens I have personal experience of Android tablets, iPad and both Windows RT and Windows 8 tablets so I’m in a good position to compare these systems to each other.

Much of this post is obviously subjective opinions but hopefully one or two of you will learn something new about these platforms.


Acer Iconia Tab A500
My first tablet was an Acer Iconia A500 that I bought a few years ago. I was really happy with this tablet from the beginning and I choose it over an iPad for several reasons:

  • I could configure it and tweak it so that it worked exactly the way I wanted
  • Widgets were very useful
  • There were plenty of apps, even though not as many as for the iPad. There are several kinds of Android apps that simply do not exist for iPad, such as custom keyboards (the one I like best is the amazing SwiftKey) or utilities such as the really cool Tasker app that triggers actions on events such as geographical location, currently running app, etc.
  • It had a full size USB port enabling it to read files directly from a USB memory or an external hard drive
  • It had a micro sd card slot making it easy to extend the builtin memory
  • It was cheaper than the iPad

My Android home screen

My Android home screen, configured they way I want it with folders and a calendar widget.

All these are valid reasons and the tablet did indeed live up to my expectations in these areas. It’s pretty cool to be able to connect a PlayStation 3 hand controller and play games on it, for example.

There are downsides to this platform of course, as there always are. The main reason that I don’t use this tablet very much these days is mainly one thing – it has a really laggy user interface. The GUI wasn’t very smooth even when it was new, there was always some “stuttering” in animated transitions. It was disturbing but I could live with it. The problem is that it got worse over time. I’m not sure about the reason for this but I have experienced the exact same thing on all my Android deviced I have owned (the HTC Desire and even the Samsung Galaxy S3 have the same problem). The reason might be that the accumulated amount of installed apps requires more and more resource but it’s not very evident what’s happening when the GUI locks up for a second or two sometimes and there is no apparent activity. CPU monitors don’t indicate extensive CPU usage either so I’m not sure about what the problem is.

Also, the standard web browser is not so good either. It displays most pages correctly but is rather slow. Chrome is the new standard browser and that’s even worse in that respect, it takes ages to download enough of a page to start display it and when you scroll down a page it’s incredibly laggy when downloading new content coming into view. Other browsers such as Dolphin does a much better job, making web browsing bearable.

For these reasons I was tempted at buying an iPad and when I found an iPad Mini on sale I took the plunge.


iPad Mini

What immediately strikes you when you buy an “iDevice” is that it feels really classy, from the box design and packing to the actual design of the device. Apple products have a reputation for “just working” without hassle and I think that the philosophy of good looking devices that you simply turn on and start using is a really neat one.

You can’t configure an iPad in particularly many ways, but that was fine by me. This time I bought a tablet, I was tired of having to troubleshoot performance problems and stuff like that. I bought the iPad to use it on the commuter train and I wanted it to be easy to handle, quick to start up and have stable apps. I have mostly used it for watching video courses using the PluralSight app, watch movies or play the odd game. The iPad do these things really well. And there is never any unexplainable delays in the user interface! The reason for this is likely the iPad’s simpler architecture and APIs, it doesn’t for example allow background processes as freely as Android, but for me the user experience easily outweighs the limitations.

This is where you see the magazines you subscribe to.

This is where you see the magazines you subscribe to.

There are many magazine available...

There are many magazines available…

The other thing that Apple has really been successful at is integrating a lot of apps and content in the App Store. I was aware of the large amount of very polished and stable apps that was available (and I wasn’t disappointed) but I did not know of the excellent integration of magazines and podcasts. Essentially all you need to do to have easy access to this content is to select the ones you’re interested in, pay, and you’re done. No other platform makes it this easy, you often have to install a special app, Google for feed Urls, etc, but for iOS devices all this is already prepared for you.

The worst problem with the iPad in my opinion is it’s reliance on iTunes for importing media to the device. This is really incredibly limiting as it supports very few video formats and if you try to import non-supported files you don’t even get an error message, it’s just silent. Took me a while to understand what the problem was. The builtin video player is good for supported files and it supports sub titles in m4v files, for example. But if you need to play files on your home network you need a more powerful media player

The best media player I have found is nPlayer. It supports many formats and features for network files (in file shares and DLNA servers for example). Recently I also learned that that it actually supports copying non-iTunes-compatible video files directly to the iPad by using the App tab of iTunes when my iPad is connected to the computer. That removes the need to convert all files to specific formats as nPlayer supports most file types. The files are only available to nPlayer, not any other app, though. It’s a little clumsy, but it works.

Windows 8

Surface Pro

Surface Pro

Surface RT

Surface RT

In spite of the video annoyances I was rather happy with the iPad, but when I attended a Microsoft conference (TechEd Europe to be precise) there was an offer to purchase the Microsoft Surface RT tablet and the Surface Pro tablet rather cheaply. Being a technology buff, I couldn’t resist the temptation so I got both of them, mostly out of curiosity. I have been using Windows 8 at my home computer for some time and haven’t really seen much point in this Windows version for a desktop computer but I though it would be nice to try it on devices that have been developed from the start to work well with touch and the “Modern” UI interface.

The Surface Pro is a real PC with an Intel Core I5 CPU and Windows 8 Pro. You can install all Modern UI apps and any old Windows program on it and it just works. I have even tried to install Oblivion, a 3D role-playing game a few years old, and it played really well. The unit is a little heavier and thicker than a normal tablet but that can be forgiven when you consider that it’s actually a full PC.

The device comes with a pressure sensitive pen that feels very natural and is more useful than I expected it to be, both for taking notes and drawing in OneNote and for operating the desktop UI, which sports rather tiny UI items because of the full HD screen.

The Surface RT is in many ways the opposite of the Surface Pro. It has a really neat design, and it’s thin and light. It uses much weaker ARM-based hardware than the Surface Pro and it’s a wide-screen device which makes it much better suited for watching movies than the iPad. The battery life is much better than the Surface Pro.

The main objections people seem to have about the Surface RT are

  1. There are too few good apps in the Windows Store
  2. You can’t run desktop apps on it

The first point is absolutely valid, the really good and useful apps in the Windows Store are really few and the ones that are there don’t seem to get much attention from their developers. Wordfeud, for eample, is several versions behind its iOS and Android siblings.

The second point is not so much of a problem since running desktop apps do indeed require more powerful hardware and worse battery life. If you really need that, go with the Surface Pro, otherwise get an Surface RT. The criticism is also unfair when comparing the Surface RT to Android tablets and iPads – they can’t run Windows desktop applications either! And besides, Microsoft Office is included with the Surface RT and it works really well. I haven’t had any problems with opening documents or spreadsheets.

Actually, apart from the meagre app supply I can’t really see many problems with the Surface RT. What surprised me was that the deskop mode is actually available and it looks almost identical to a “real” desktop computer. There is the normal File Explorer and it supports network shares just as well as a normal laptop/desktop. This is a really powerful feature no other tablet OS can match. It just works! Also, if you have a Modurn UI app, they also will support file shares etc automatically as that support is built into the OS. On other platforms, each app has to rely on a third party library to cope with this and it seldom works robustly.

Movie file format is limited in the standard player, so I use PressPlay Video instead, which is great. Moving files from other computers, memory sticks or the Internet is done in the same way as for a “normal” computer using the File Explorer or saving directly from the web browser.

Being a Windows computer, my Surface RT tablet automatically discovered my Canon network printer and installed the correct printer driver. I can now print web pages from Internet Explorer or documents from Office and it didn’t take any configuration at all. “It just works.” πŸ™‚ No other platform can do this as far as I know.

Windows 8.1: Clicking a link in a mail now displays the web browser side by side with the mail app

Windows 8.1: Clicking a link in a mail now displays the web browser side by side with the mail app

So I’m actually rather happy with my Surface tablets, and when I installed the Windows 8.1 Preview on my Surface RT, it’s even better. There are numerous small improvements, such as much better side-by-side running of apps (see image on the right). Again, no other tablet can do this (Samsung has something similar for their tablets but it’s a more manual process).

I have used both my Surface tablets more than I expected. On the Surface Pro, Netflix in the Chrome browser with MediaHint browser add-on has been awesome for watching US NetFlix content. I have also started to appreciate Microsoft Office OneNote (I think the pen made it feel very natural to use that application). The Surface RT has also been used a lot, mainly for web browsing and watching movies (from my NAS or the standard NetFlix Windows 8 app). Windows 8.1 makes the Windows 8 experience much more streamlined and I think it’s a shame that Windows 8 wasn’t like that from the first version.


Of the three kinds of tablets I own, the Android is used the least because of UI lagging. Google promises that Android 4.3 adresses this problem, but I’ll believe that when I see it. It’s also not likely to be an update that Acer will support on an older tablet like mine.

The iPad is actively used in exactly the ways I expected, mainly when commuting and also by my kids as it has a bunch of great games that they like.

My Windows 8 tablets are also actively used and I like them both more than I thought I would. They have more features that are usable to me than both the Android tablet and my iPad.

The main problem with Windows 8 is, as everybody already knows, the lack of useful and robust apps. There are apps like that, but they’re too few. I don’t think that’s going to change either, iOS and Android have gained so much momentum that it’s too late for Microsoft to catch up with them. Microsoft have one unique absolute killer feature however, in that Surface Pro and it’s brethren can run Windows desktops applications. When that kind of tablet becomes smaller and with better battery life I think that much will happen in the tablet market. My Chrome + NetFlix + MediaHint use case is an example of the power that this platform brings to the tablet format.

To sum it up, I think the Windows RT and Windows 8 Pro tablets have taken too much and exaggerated criticism so far. They’re not perfect but then the competetion is not fault-free either. The problem is not that Microsoft and it’s allies deliver bad products, it’s that they’re too late so that the other app markets have had time to grow too large.

Just my two cents…


TechEd Europe 2013 – Friday

The last day of my TechEd event was cut in half because of an early flight home (I had a wedding to attend to on Saturday) so this post will be rather short on the sessions of the day and then I’ll conclude with a summary of my conference experience.

I attended three sessions, the first was about 3D DirectX development with Visual Studio 2012. That’s a kind of unusual subject for TechEd but was interesting for me as a general developer. I’d like to see more of this kind in future conferences. The second was a session about news in Entity Framework 6 and that was also good. This was actually a repeated session from earlier in the week since the first one was full.

Mark Russinovich

The last session I attended in this year’s TechEd was this annual edition of Mark Russinovich’s Case of the Unexplained session series: Windows Troubleshooting. These sessions are truly amazing and if you ever get a chance to attend one of them I can warmly recommend them. Mark is one of the best speakers I’ve seen in our industry (together with Scott Hanselman), entertaining and incredibly knowledgeable. A short description of this session that he describes real-world use of the SysInternal utilities (of which he is the creator) to find and solve Windows problems such as crashes, performance problems, hangs, etc. As Windows users we all experience these kind of problems so these sessions are both useful for getting insights to problems that can occur and also to get tips of how to use the SysInternals tools. I’ve experienced a few of these sessions before and they’re always very useful. This was the best session in this edition of TechEd so it was a great way to end this year’s edition for me.

With that it’s time to wrap up TechEd Europe 2013 with some thoughts about the experience. This year, there was about 5 000 delegates according to officials, which can be compared to the 10 000 count of TechEd North America. It’s a much smaller conference than TechEd NA but there were still an abundance of sessions to attend, generally about 15-20 in parallel for each time slot. That’s lot indeed, but if you look in more detail about the contents of these sessions you find that most were targeted at the IT pro community. There were also one or two Business Intelligence sessions for each time slot leaving perhaps room for 2-4 developer sessions. Most of these remaining developer sessions fall into two categories:

  • Windows Azure technologies
  • Microsoft SaaS offerings such as Team Foundation Services, BizTalk Services, and the like

This makes for a very heavy cloud focus on the developer parts of the conference. There wasn’t much about Windows 8 development, Asp.NET web development, Javascript (which is otherwise mentioned everywhere in the industry these days), etc. Also, there wasn’t much on software architecture or best practises (except a little about Azure, of course). To me, being a an architect and developer, this makes TechEd a less relevant conference than I’d hoped it to be. There was some consensus (at least among some of us delegates) that the IT pro side of things have been given more priority at TechEd than before. I don’t know if this is deliberate from Microsoft or if it just turned out this way, but in either case I will probably choose another conference the next time I get the chance to attend one.

This year the Build conference took place at the same time as TechEd Europe. Build is very focused on the developer and it’s starting to feel like a better match for my work role than TechEd. The planning seems very strange to schedule two Microsoft conferences of this large scale at the same time but I suspect there are explanations we aren’t informed of. Earlier editions of Build have taken place in the autumn but I think that Microsoft wanted to announce Windows 8.1 before the summer in order to not loose more ground than they already have. Therefore they needed to squeeze in Build before the summer. Build was also announced much, much later than TechEd, indicating some urgency from Microsoft.

The problem for me as a TechEd delegate, is that it definitely made TechEd feel a bit like a second class conference for developers, as I mentioned in one of my earlier posts. That’s not a feeling you want for your delegates if you’re organizing world-class conferences…

However, I attended some quite useful sessions and it was interesting to see what’s happening with Windows Azure and other online services. TechEd is still a useful conference for developers, especially if you have some interest in the IT pro side of things and for me, having some architectural responsibilities and also an interest in systems integration, the conference scope would be a quite good match if only there was a little more general developer content.

That about sums it up, I guess. That’s the final post on this mini series of Tech Europe 2013 reports. Thank you for reading.


TechEd Europe 2013 – Thursday

Yet another day at TechEd Europe has passed. This day started with a second keynote, the reason being that Windows 8.1 was announced at the Build conference the night before. Microsoft had to do these announcements before they could talk about Windows 8.1 at TechEd I suppose. Kind of makes TechEd look like a second class conference, Build seems to receive considerably more focus from Microsoft. Steve Ballmer himself did the keynote at Build and all delegates receive generous giveaways. At TechEd we have gotten a rather good Surface tablet offer this year, being able to order a Surface RT and a Surface Pro with quite good discounts. At Build however, the delegates gets a Surface Pro and an 8 inch Acer tablet for free…

Now, this blog post was not going to be about Build (I’m reporting from TechEd after all) but it’s worth pointing out that it seems that there is less developer focus at TechEd and that Microsoft is expecting developers to go to Build instead. I heard that approximately 25% of the TechEd delegates are categorized as developers, the rest being IT pros, so that’s also an indication.

However, being interested in enterprise development, I have attended several interesting sessions today. There was one session about the news in Windows 8.1 and it seems to be not very revolutionary. Apps can now be snapped more freely than before, for example. Another example is that there is a new speech synthesis API. That was actually not very impressive, it sounded a bit like my old Amiga 500 speech synthesis from the early nineties. Not sure who needs that, actually.

There was also lots of other smaller changes making the Windows 8 experience a little more polished, but nothing major so I guess that the version number 8.1 is pretty accurate. The most peculiar new feature is that Windows 8.1 will support 3D printing inside the OS in the same manor as 2D printing, with a print dialog with preview, etc. Obviously a gimmick, but certainly something that will get some attention (I’m blogging about it right now, for example…).

My reward for testing Azure Web Sites and TFS Services

My reward for testing Azure Web Sites and TFS Services

Other sessions I’ve been to have been about Team Foundation Server / Services and Azure Service Bus, both of them quite interesting. I can’t help noticing that most of the sessions that have been relevant to me have been about Azure or TFS so it seems that Microsoft is really pushing those technologies.

Microsoft also had a challenge at the conference that if you did certain experiments with Azure and Team Foundation Services (mainly creating a web site and deploy into that from your own project in TF Services) you’d get some prizes. Of course I couldn’t help accepting the challenge and was up last night doing 4 of the 5 challenges and in return I collected a water bottle, ear plugs with the Azure logo and an R/C car to play with. πŸ™‚

It was actually rather fun trying out these technologies and the challenge was a way for Microsoft to make us take a little time to actually do some testing. And it worked, I’ll probably be using this stuff in the future…


TechEd Europe 2013 – Wednesday

So I find myself at the keyboard again after another long day at TechEd Europe in Madrid. My sessions of today were about Team Foundation Server, Windows Azure BizTalk Services, Windows Azure Mobile Services, continuous integration with TFS Service and Azure web sites and finally an interesting session about F#. There is much happening in Azure, I already knew that but after attending these sessions I realize that the pace of progress is faster than I thought…

Editing the Insert action of a service.

Editing the Insert action of a service.

As I mentioned yesterday, I’m starting to get really impressed with Mobile Services, the demos I’ve seen in Madrid has really been an eye-opener. Steven Sanderson had another great session on that subject today, entitled Develop Native iOS and Android Apps with Windows Azure Mobile Service. First time I’ve seen Android programming and iOS programming on a Microsoft event (and boy, is Objective C ugly!). Really great stuff, check it out if you’re interested in mobile services!

Screen shot of Dustin Campbell's text adventure game compiler

Screen shot of Dustin Campbell’s text adventure game compiler

Dustin Campbell held an interesting session about Build Data-Rich Solutions Faster with Microsoft Visual F#. The talk was not so much about data-rich application as a set of cool F# demos. Without a doubt he gets the geek award of the day for his demo of his text adventure game compiler written in F#. He was very proud to demo playing Zork, written in a grammar that was runtime compiled into MSIL by F#. Rather cool, and utterly unnecessary πŸ™‚

I’m also more more interested in TFS again, the agile planning tools keep getting better and better. TFS 2012 Update 3 is coming out any day and now includes tag support. More interesting stuff is happening in TFS 2013 but we have to wait little more for that. When I asked about the time frame, MS refused to answer, other than that they’re going to respect the year part of the name…

Inspired by today’s sessions I signed up for a TFS Services account (i.e. TFS in the cloud). This way I can try out some features not yet released in the on-premise version. It’s dead simple and the feature auto-deploy to Azure web sites is very neat. The integration between TFS Services and Azure is obviously something that Microsoft has put some effort into.

A new Azure service that I haven’t seen before (I just heard about it a few weeks ago) is BizTalk in the cloud, Windows Azure BizTalk Sevices (currently in preview). I’s a little unclear however how much is actually the good old BizTalk we love and hate. They have new designers and this time you get to configure the entire path from source to destination from inside Visual Studio in an artifact called a Bridge. If you have worked with BizTalk you know that today you define schemas and transforms in Visual Studio but you have to associate them with receive and send ports using the BizTalk administration tool, causing complex deployments. Just this single feature make the new version very interesting but the main aspect is of course that integration is now available as a service in the cloud, with scalabitity, quick provisioning, and so on. BizTalk Server 2013 is also in the pipeline but for the moment these two offerings seem to be rather separate.

That’s all for today. I’ll be back tomorrow…


TechEd Europe 2013 – Tuesday

The IFEMA - Feria de Madrid convention center

The IFEMA – Feria de Madrid convention center

I have now spent the first day of TechEd listening to keynotes and sessions, and walking between the different rooms where the presentations take place. Actually it’s a lot of walking, then venue is pretty large…

The keynote discussed four themes:

  1. Empower people-centric IT
  2. Enable modern business applications
  3. Unlock insights from any data
  4. Transform the datacenter
GeoFlow demo at TechEd Europe 2013 keynote.

GeoFlow demo at TechEd Europe 2013 keynote.

A lot of sales talk of course, and not a whole lot of news. A little unexpected for me, it was actually point 3 above that was the most interesting with a some very cool demos of Excel Data Explorer and GeoFlow, new tools for data discovery and BI in Excel. These worked with data sets coming from HDInsight (Hadoop) in Azure.

If we skip the sales talk it seems the main trends are the cloud (of course), big data, BYOD (bring your own device, i.e. how IT departments handle people bringing their smart devices to work)

Also interesting was the announcement of Windows Azure Pack for Windows Server, enabling automatic provisioning of VMs in on-premise servers letting user’s configure their environments using the well-known Azure Portal locally. Pretty neat.

I went to several different sessions, most of them relatively good. Best was probably Steve Sanderson’s talk Cloud-Powered Web Apps in Minutes with Windows Azure Mobile Services where he developed a SPA (Single Page web Application) using Azure Mobile Services as a backend. Really cool presentation with almost no PowerPoint. Interesting to see that Mobile Services is not just for mobile apps, it can be used as a backend regardless of the type of application, something I had not realized can be quite useful.

This evening there was the customary welcome reception with free food and drink and a lot of exhibitors throwing swag at you if you let them scan your badge. Being a TechEd alumni, I have learned that it’s not worth all the spam you’re going to get in the coming year so I got away with just foldable frisbees for my kids and a “tech trap”, apparently used for stowing away mice and cords in the computer bag…

To sum this up I think it was an okay first day. Comparing to TechEd North America it’s a quite similar experience but in a smaller scale (perhaps half, or a third, of the number of participants). The sessions are of similar standard to those that I have attended in the US.


TechEd Europe 2013

TechEd 2013 bling bling
My trip to sunny Madrid is now just a few days off. I’ll probably not be working much on my tan though, since I’ll be at TechEd Europe this year. Really looking forward to it, as I’ve been to two great TechEd North America events before. This time it’s Europe so it’s probably a slightly smaller event but I’ve found plenty of interesting sessions in the agenda.

I’ll be writing reports about my TechEd experience next week so stay tuned if you’re interested πŸ™‚


Excel macro for generating sequential id numbers

This post describes how to write a simple Excel macro to create sequential id numbers. This can be useful whenever you create lists with rows that should have a unique id, for example in product backlogs, requirement lists, etc. Often you type these ids by hand, the first starting with 1 and then increasing the id for each consecutive row. This works okay as long as the rows are in the original order but when sorting and filtering is used it can be a little difficult to know what the next row id should be. You can always select all existing ids and look at Excel’s status row which is the highest but it would be nice to automate this, yes? Yes! πŸ™‚

This is the result we’re aiming for:

Generated id rows

To meet this goal, I have created a simple Excel macro that calculates the next id and this post describes how to use it.

First of all, make sure that the Developer Tab is enabled in the Ribbon:

Excel Ribbon Developer Tab

If it’s not, then enable it in the Customize Ribbon section of Excel’s Options dialog:

Enable Excel Developer Tab In Ribbon

Once the Developer bar is enabled, start to create a new macro by clicking on the Record Macro button and fill in the fields:

Create macro

Close the dialog and immediately stop recording the macro by clicking the same button again (it now says Stop Recording).

Then click the Macros button and edit the newly created macro by clicking the Edit button:

Edit macro

The empty source code for the new macro is displayed. Paste the following code into the macro:

ActiveCell.Formula = "=MAX(A4:A" + CStr(ActiveCell.Row - 1) + ")+1"
ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value

Adjust the area so it suits the table. In the example the id column is A and the first row with data is row 4, so the area is given as A4:A. Close the macro dialog and you’re done!

Edit macro text

Try the macro by placing the cursor in the id column of the first empty row and press the keyboard shortcut you selected when you created the macro (Ctrl-N in my case) and the new id should turn up. No more counting of rows or selecting all rows to see what the highest number is, we have now automated this process, and again there is some rest to be had for our lazy soul… πŸ˜‰

Note that the Excel workbook must be saved with the .xlsm extension (Excel Macro-Enabled Workbook) for security reasons as macros are considered riskful by Microsoft:

Save Macro Enabled Workbook


Extracting stored procedure source text from SQL Server

It’s been a really long time since the last post so here’s a small SQL Server tip. I recently had to extract the source code for more than two hundred stored procedures from a SQL Server database. Obviously I didn’t want to do that by hand so I wrote a simple script using sp_helptext:

exec sp_helptext proc1
exec sp_helptext proc2
exec sp_helptext proc3

This did indeed extract all the source code for the procs but for some obscure reason I had a newline character after every 255:th character in the result. Close but no cigar…

I did some googling without finding anyone with the identical problem (I had already increased the “Maximum number of characters displayed in each column” setting of SQL Server Management Studio). However, it turns out there’s an alternative to sp_helptext, namely the sys.sql_modules table which can be queried like this:

select definition from sys.sql_modules where object_id = object_id('proc1')
select definition from sys.sql_modules where object_id = object_id('proc2')
select definition from sys.sql_modules where object_id = object_id('proc3')

This time there were no irritating newlines in the output. Problem solved!