Blog ala Vidar

SQL, AppFrame and other cool technologies

Windows Phone 7 – HTC 7 Trophy

On Friday I finally got my WP7 (Windows Phone 7); HTC 7 Trophy. The reason I chose this device over the others was that this was the first I was able to get my hands on. The official words from Microsoft was that HTC Trophy, Mozart and LG’s E900 Optimus 7 were the only devices going to be released in the first “wave”. I’ve always thought of LG as the Lada of the electronics world. Have no idea why, just my impression. And, before you attack me on this, I have never owned a LG device, and never owned a Lada.

The difference between these devices isn’t much. 8MP instead of 5MP, 10 grams, 0.1” display etc. Trophy has a 0.1” bigger display than Mozart, and I don’t care about taking pictures, so easy choice.

Now, over to the best part; Windows Phone 7 is awesome! Let me first quote a mac-dude at the electronics shop I bought my device; “Whow! This is more like an Apple product than Apple ever has created”. I’m not sure I agree, but if he were talking about the user-friendliness, I have to agree. It’s sexy, simple and very quick. Before I got my device, iPhone was, without a doubt, the best phone I’ve ever had. I want to wait a couple of days before I change this, but till now my impression can be summarized with one word. awesome!

When setting my phone I added a live ID with US address, just to get access to the Marketplace. To buy anything I need an American visa-card or similar, but till now there’s only one app I want to buy and that is Geocaching. It’s a bit annoying that they haven’t opened for this in Norway yet, but to be fair, I did know this before I bought the phone.

Other than the obvious (things not available yet, English keyboard etc.), there’s only two things I don’t like about this phone. First, the micro-USB connector is on the left side, and the mini-jack is on the top. I listen to podcasts when ever I’m in my car, and that’s at least 1.5 hours each day. Because I don’t want to drive with plugs in my ears I use a FM-transmitter connected to the mini-jack, and of course charging with the micro-USB. Here’s the problem. It’s impossible to place the device with these two cables in it, and still be able to unplug the cables if anyone calls, and take the call before they hang up.

The other thing that’s starting to annoy me is a bit harder to explain. There’s a great feature built into the People Hub (basically where all your contacts live) where you can link accounts. Example; I’m friend with Jan Leon (a co-worker) on Facebook. I’ve also got him as a contact in Outlook (via Exchange), on my Gmail account, and on two of my Live IDs. With the link stuff, I link these accounts together, so I’ve only got one “Jan Leon” in my contact list on my phone. This is great, but here’s the problem. When linking these, it seems to make the shortest a default. This wouldn’t be a problem if I had added FirstName LastName etc. for all my contacts in all my accounts, but since I’m lazy, janleon@omega.no is the shortest. This means I have to go into this and rename my contact, although 4 of 5 contacts in the linked list is called “Jan Leon Sandslett”. This is really annoying, since I’ve got 300 friends on Facebook, 200 contacts in Outlook and about 250 contacts on MSN which mostly is the same people.

But what about copy-paste? Think it over. When did you last use it on your phone? When you get a phone-number on SMS, you just click it and create a contact (or similar). If you want to share an URL on Facebook, you just click the share button. I think I’ve used copy-paste once while I’ve had my iPhone, and that’s about a year. But, for those that can’t live without it, for some weird reason, you only have to wait till Microsoft releases an update in January 2011. Multitasking is also coming. I can see why people want this, but I didn’t use it on iPhone, and can’t see when I’d use it on the WP7. For the record, WP7 has multitasking, but only for the internal applications. This means you can listen to music in Zune while doing other stuff.

Final words. If you’re a technology enthusiast, there’s no other choice. You have to buy a Windows Phone 7. If you’re a normal user, like my mom, you want to wait till (hopefully) Q1 2011. I hope Microsoft’s opened Zune Pass, Marketplace and the other good stuff by then.

Amazon Kindle

I got my new Amazon Kindle yesterday and I LOVE IT! Don’t know what this is? It’s a device for reading books. And, why not use good old books? Well, first of all, the ones that I’m reading (typically SQL Bibles with 1500 pages) weigh about 15 tons, are about 1×1 meter etc, so reading books like that in bed just doesn’t happen. Having a little device that weighs 250 grams is therefore much more convenient. At the moment my device stores 40 books, and it’s only using about 10% of the available storage. I’ve got the Kindle 3 3G and Wi-Fi, but don’t think I’ll ever need the 3G functionality. Anyways, it’s nice to be able to download new books if you’re sitting at a place without any Wi-Fi, and don’t have to pay for it. Yes, you heard correct. You don’t pay anything for 3G traffic. You DO pay for the books you download though.

So, why do I love this little device? I’ve thought about reading (technical) books for a looong time, but since they’re so inconvenient to hold, I haven’t got to it. Also, I’m already WAY to much from my computer-screen, and my eyes really don’t like to read on computer-screens. The Kindle has a technology called E Ink, which is much easier on your eyes, and a cool “side effect” is that it only uses power to switch page. This means that when you’re reading a page, it’s not using any battery, or at least very little.

The Kindle also makes it quicker to buy books. On Amazon.com you can buy a book, and send it to your Kindle, with ONE click. Not like “Click Once”, where you click 4-5 times, but actually ONE click. There’s a big collection to pick from, including their free ebooks. If you’re thinking about buying this, you can download and install the Kindle application for Windows and check it out.

Getting ready for Windows Phone 7

I’m SO ready to get this device! I actually don’t care which I get, as long as I get it ASAP. I’ve actually ordered two different devices, at two different shops, just to be safe. Which I’ve ordered? HTC 7 Trophy and Samsung Omnia 7. The last one is the one I want the most, but from what I’ve heard, this will not be released for a while in Norway. But what’s the difference between the devices? Well, not much to be honest. The reason I want the Samsung is because of the screen. According to people that’s tested “all” devices, Samsung has the best screen with it’s AMOLED technology. Other than that, they’re more or less identical. Some are 10 grams heavier, some are 8MP camera, instead of 5MP, but since Microsoft has set some minimum requirements, there’s not much difference.

What should you do before you buy your new phone? Paul Thurrott, the guy behind the “Windows Phone Secrets” book, has written a pretty good article about this topic. To sum up;

  • Configure your Windows Live ID by navigating to http://profile.live.com. On this site you can connect your Live ID to Facebook, LinkedIn, your blog+++.
  • If you’ve got one account for Xbox LIVE, one for MSN, one for Hotmail etc, you might want to gather this into one account.
  • Install Zune and gather all your media-stuff.

One more thing. Xbox LIVE and Zune Marketplace will not be available in Norway when the phone gets released, but no worries. From what I’ve heard, this WILL be released. Not sure when, but rumours say January.

Unused Indexes

Two weeks ago, I held a session called “Hardcore SQL” session at AppEd where I (among other things) said that you should always start with removing unused indexes, before adding missing indexes. You can find my blog post about missing indexes here. So, how do you find unused indexes?

SELECT
  O.name AS ObjectName, I.name AS IndexName,
  user_seeks + user_scans + user_lookups AS Reads,
  user_updates AS Writes, P.rows AS RowsNum
    FROM sys.dm_db_index_usage_stats AS IUS
    INNER JOIN sys.indexes AS I ON I.index_id = IUS.index_id AND IUS.object_id = I.object_id
    INNER JOIN sys.partitions AS P ON P.index_id = IUS.index_id AND IUS.object_id = P.object_id
    INNER JOIN sys.objects AS O ON IUS.object_id = O.object_id
    WHERE O.type = 'U'
      AND IUS.database_id = DB_ID()
      AND I.type_desc = 'nonclustered'
      AND I.is_primary_key = 0
      AND I.is_unique_constraint = 0
      AND P.rows > 10000
    ORDER BY Reads, RowsNum DESC

Btw, I’ve got a Live Mesh folder with SQL scripts. If anyone wants access to it (and maybe wants to add scripts) you know how to contact me.

AppEd 2010 Summary

Last weekend we at Omega had something we call AppEd, which is a conference for people working with AppFrame technology. With 3 tracks with a total of 27 sessions (+ keynote), 70 attendees, good food and lots of social things, I think we can call this one a very successful conference! Soon the communications department will publish an article on InsideOmega.com about this event, but I just wanted to thank everyone for coming, and hope to see you again in two years!

If you’ve got any ideas on how to make the next AppEd even better, please let me know.

Oh! Most importantly. The videos for some of the sessions have been published to http://appframe.com/download

Internet Explorer 9 Beta Review

IE9 Beta was released a couple of days ago, and I’ve been using it since it was released. It’s WAY better (as in faster, and more out of the way) than for example IE8. I’ve been using Google Chrome (beta) for about two(?) years now, and I’ve been very happy with it. Chrome is fast, it works perfectly on 99% of all sites I visit and it’s out of my way. It doesn’t bug me about updates, the UI is very simple and it only takes a couple of cm in the top of the window.

I’ve always ended up with IE, for some reason. I’ve had periods where I’ve used Firefox, Chrome and even Opera (believe it or not), but after a while I’ve always ended up with IE. Now might be the time to get back to IE after two years of Chrome. Don’t get me wrong. I LOVE Chrome. It’s awesome! But now Microsoft has released the beta and it’s satisfying to use it. The key features for this release are HTML5 support, hardware acceleration, speed and stability. I know this is a beta, but I’d like Microsoft to focus more on “out of the way”. Here’s a couple of things I’d like Microsoft to fix before they release the RTM of IE9:

Closing tabs should be easier. If I’ve got three tabs open, and I want to close only one of the tab that’s not focused, I have to click on it and then click the X on the tab. In Chrome I just click the X on the tab I want to close.

Browsing favorites. In my favorites bar I’ve got 5 folders. In some cases I don’t remember where I’ve put the favorite I’m looking for, so I have to go through all maps. With the beta of IE9 I have to click every map to see it’s content. In Chrome I just click one map, and then hover the others with my mouse.

about:Tabs is confusing. In Chrome you got the same feature, but I think it’s better done in Chrome. This might be because I’m used to it, but there’s a couple of things that annoy me with IE’s implementation of this. For instance, why are there different colors on the active bars? IE uses text and favicons, instead of showing the a snapshot of the site. This makes it way slower to find the one you’re looking for.

Now over to the positive things about this release. Let’s first start with the integration with Windows 7, more specifically, the taskbar. Go to www.twitter.com and drag the favicon in front of the URL down to your taskbar. This adds twitter to your taskbar, and you can right-click the icon and you’ll see a list of common tasks for twitter; New Tweet, Direct Messages, Mentions, Favorites and Search. This is done by adding a couple of meta-tags in your site. Under you’ll see the metatags from Twitter, but other sites like Amazon, Facebook and Channel 9 have already implemented this functionallity and is ready for you to start using them.

<meta name="msapplication-task" content="name=New Tweet; action-uri=http://twitter.com/home; icon-uri=images/ie/tweet.ico" />
<meta name="msapplication-task" content="name=Direct Messages; action-uri=http://twitter.com/inbox; icon-uri=images/ie/dm.ico" />
<meta name="msapplication-task" content="name=Mentions ; action-uri=http://twitter.com/replies; icon-uri=images/ie/mentions.ico" />
<meta name="msapplication-task" content="name=Favorites; action-uri=http://twitter.com/favorites; icon-uri=images/ie/fav.ico" />
<meta name="msapplication-task" content="name=Search; action-uri=http://search.twitter.com; icon-uri=images/ie/search.ico" />

One other thing that I’ve been amazed by is the speed of the browser. It’s more or less the same speed as Chrome, at least when you’ve disabled plugins you didn’t know you had! It also uses about 1 cm less than Chrome in the top of the window, which is the way to go in my opinion. Also, the developer tools built in to the browser is easy to use and seems to support a lot of features.

Bottom line; I like the way Microsoft’s going with their browser, and I hope the RTM will be even better than this beta release!

NNUG and MTUG Haugesund User Group Meeting in English

The countdown to AppEd has started. ARE YOU READY TO RUMBLE?!

Thursday evening, September 30, we’ll start off with NNUG (.NET) and MTUG (Microsoft Technology) Haugesund user group meeting. First, Fredrik Kalseth, which is a well known speaker in Norway, will talk about ASP.NET MVC 2 and the news in MVC 3. After his talk we’ll have some pizza, and then Morgan Simonsen, which is also a very well known speaker, will talk about Windows 7 SP1 and System Center Virtual Machine Manager 2008 R2. And the best of all, all sessions are in English, so everyone’s welcome! Please sign up for this at http://www.nnug.no/Avdelinger/Haugesund/Moter/NNUG-and-MTUG-Haugesund—September-2010/

Auto Populate Value Lists

In AppFrame we’ve got something called value lists. These are meant to make filtering even easier. When creating your filter in for example a “Documents” CR3 form, and you choose the field “Responsible”, this of course have a foreign key to some kind of Persons-table. When choosing Responsible, why not produce a list of Persons so that you can click on the person instead of having to type in the PersonID or what ever is making it unique? Well, that’s the point of value lists. I just created a script to auto populate these, based on the foreign keys. This will only take the FKs that are bound on one column though! I’ll probably fix that tomorrow, but till then, here’s the script:

DECLARE
  @ForeignKeyBaseTable AS NVARCHAR(128),
  @PrimaryKeyTable AS NVARCHAR(128),
  @ForeignKeyColumn AS NVARCHAR(128),
  @PrimaryKeyColumn AS NVARCHAR(128),
  @ColumnName AS NVARCHAR(128),
  @ColumnLength AS SMALLINT,
  @IsNullable NVARCHAR(5),
  @ColumnType NVARCHAR(15),
  @SQL AS NVARCHAR(MAX)

DECLARE @SelectColumns AS TABLE (
	ColumnName NVARCHAR(128),
	ColumnLength INT,
	IsNullable NVARCHAR(5)
)

SET NOCOUNT ON

SELECT
  O.name AS ForeignKeyBaseTable,
  OBJECT_NAME(FK.referenced_object_id) AS PrimaryKeyTable,
  FKC.constraint_column_id AS ColumnNumber,
  FKColumn.name AS ForeignKeyColumn,
  PKColumn.name AS PrimaryKeyColumn,
  CASE WHEN T.name IN ('tinyint', 'smallint', 'int') THEN 'Int32'
		WHEN T.name IN ('smalldatetime', 'datetime', 'date', 'time') THEN 'DateTime'
		WHEN T.name IN ('float', 'decimal', 'numeric') THEN 'Long'
		WHEN T.name IN ('bigint') THEN 'Int64'
		ELSE NULL
  END AS ColumnType,
  FKColumn.max_length AS ColumnLength,
  CASE WHEN FKColumn.is_nullable = 0 THEN 'false' ELSE 'true' END AS IsNullable
	INTO #FKColumns
	FROM sys.foreign_keys AS FK
	INNER JOIN sys.objects AS O ON FK.parent_object_id = O.object_id
	INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id
	INNER JOIN sys.columns AS FKColumn
		ON FKColumn.object_id = FKC.parent_object_id
		  AND FKColumn.column_id = FKC.parent_column_id
	INNER JOIN sys.columns AS PKColumn
		ON PKColumn.object_id = FKC.referenced_object_id
		  AND PKColumn.column_id = FKC.referenced_column_id
	INNER JOIN sys.types AS T
		ON FKColumn.system_type_id = T.system_type_id
		  AND FKColumn.user_type_id = T.user_type_id
	WHERE T.name IN ('tinyint', 'smallint', 'int', 'smalldatetime',
					'datetime', 'date', 'time', 'float', 'decimal',
					'numeric', 'bigint', 'varchar', 'nvarchar')
	  AND O.type = 'U'
	  AND O.name LIKE 'atbl_%'
	  AND NOT EXISTS (SELECT *
						FROM sys.foreign_key_columns
						WHERE constraint_object_id = FK.object_id
						  AND constraint_column_id > 1)
	ORDER BY O.name, OBJECT_NAME(FK.referenced_object_id), FKC.constraint_column_id

WHILE EXISTS (SELECT * FROM #FKColumns)
BEGIN
	SELECT TOP 1
	  @ForeignKeyBaseTable = ForeignKeyBaseTable,
	  @PrimaryKeyTable = PrimaryKeyTable,
	  @ForeignKeyColumn = ForeignKeyColumn,
	  @PrimaryKeyColumn = PrimaryKeyColumn,
	  @ColumnType = ColumnType,
	  @ColumnLength = ColumnLength,
	  @IsNullable = IsNullable
		FROM #FKColumns

	SET @SQL = '<?xml version="1.0" encoding="utf-16"?>
<afRecordSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <IsFilterChanging>false</IsFilterChanging>
  <UniqueTable>' + @PrimaryKeyTable + '</UniqueTable>
  <ViewName>' + REPLACE(@PrimaryKeyTable, 'atbl_', 'atbv_') + '</ViewName>
  <SelectColumns>
	<afSelectColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />'

	IF @ColumnType IS NOT NULL
	BEGIN
		SET @SQL = @SQL + '	  <DataType>' + @ColumnType + '</DataType>'
	END

	SET @SQL = @SQL + '
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'


	INSERT INTO @SelectColumns
	  (ColumnName, ColumnLength, IsNullable)
	SELECT
	  name, max_length, CASE WHEN is_nullable = 1 THEN 'true' ELSE 'false' END
		FROM sys.columns
		WHERE object_id = OBJECT_ID(@PrimaryKeyTable)
		  AND (name LIKE '%Description%'
			OR REPLACE(name, 'ID', '') LIKE REPLACE(@PrimaryKeyColumn, 'ID', ''))
		  AND name <> @PrimaryKeyColumn

	WHILE EXISTS (SELECT * FROM @SelectColumns)
	BEGIN
		SELECT TOP 1
		  @ColumnName = ColumnName,
		  @ColumnLength = ColumnLength,
		  @IsNullable = IsNullable
			FROM @SelectColumns

		SET @SQL = @SQL + '
	<afSelectColumn>
	  <Name>' + @ColumnName + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'

		DELETE
			FROM @SelectColumns
			WHERE ColumnName = @ColumnName
	END


	SET @SQL = @SQL + '
  </SelectColumns>
  <OrderByColumns>
	<afOrderByColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	</afOrderByColumn>
  </OrderByColumns>
  <GroupByColumns />
  <WhereClause />
  <FilterString />
  <DistinctRows>false</DistinctRows>
  <MaxRecords>0</MaxRecords>
  <IgnoreDesignTimeMaxRecords>false</IgnoreDesignTimeMaxRecords>
  <MasterChildCriteria />
  <DisableAutoLoad>false</DisableAutoLoad>
  <InitMode>false</InitMode>
</afRecordSource>'

	UPDATE stbl_Database_Fields
		SET
		  ValueListRecordSource = @SQL,
		  ValueListValueMember = @PrimaryKeyColumn
		WHERE DBObjectID IN (@ForeignKeyBaseTable,
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbv_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbx_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'aviw_'))
		  AND FieldName = @ForeignKeyColumn

	DELETE
		FROM #FKColumns
		WHERE ForeignKeyBaseTable = @ForeignKeyBaseTable
		  AND PrimaryKeyTable = @PrimaryKeyTable
		  AND ForeignKeyColumn = @ForeignKeyColumn
		  AND PrimaryKeyColumn = @PrimaryKeyColumn
END

DROP TABLE #FKColumns

DELETE FROM atbl_Module_%

You ever struggled with deleting everything from a module (test data for instance) before you start the data-transfer of your live data? Here’s a quick script that will loop through all tables in a namespace and delete everything in all tables in that namespace. It will stop after failing 10 times on the same table.

DECLARE @TableName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name AS TableName, 0 AS FailedToDeleteNumber
    INTO #tables
    FROM sys.tables
    WHERE name LIKE 'atbl_Module_%'

WHILE EXISTS (SELECT * FROM #tables WHERE FailedToDeleteNumber &lt; 10)
BEGIN
    SELECT TOP 1
      @TableName = TableName
        FROM #tables
        ORDER BY FailedToDeleteNumber, TableName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    BEGIN TRY
        SET @SQL = 'DELETE FROM [' + @TableName + ']'
        EXEC (@SQL)

        DELETE
            FROM #tables
            WHERE TableName = @TableName
    END TRY
    BEGIN CATCH
        UPDATE #tables
            SET FailedToDeleteNumber = FailedToDeleteNumber + 1
            WHERE TableName = @TableName
    END CATCH

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)
END

DROP TABLE #tables

Geocaching

When I was younger (a year ago or something), I play a pirate very often, waving with a sword going on treasure-hunts etc. This weekend, I found a new hobby which reminds me a lot of this; Geocaching.

Geocaching

The point is to use a GPS enabled device to find packages set out by others. These packages comes in four different sizes, from a couple of centimeters (1 inch) to 20-30 cm (about 11 inches). You locate a package you want to find on the internet (www.geocaching.com), plot the GPS coordinates into your device (in my case, iPhone) and then start walking. Some of these packages might be very close to a main road, while others are out on an island where you have to use a boat to go out and find it. When you’ve found the location, you start looking for the package. You can get some hints from the application if you need it, or you can ignore it.

Me and a friend had to check this out, so we located the package closest to my apartment. After we found it we were so excited that we just had to find more of them. We ended up finding 4 packages, all in the area where I live.

When you find a package, you will probably find a notepad in there, to write your name and date, and a couple of other things. If you take anything, remember to put in something with the same or greater value. The things we’ve found is marbles, pens, reflexes and similar. I really recommend this for people that want to do something while walking around.

Here’s a couple of links which shows the packages around the cities where most of my readers lives: