OT: Books: Bill Bryson "Down Under"

I was on a flight from Houston to Washington via Dallas today. We seemed to sit forever on the tarmac in Dallas, in fact just over 1.5 hours from leaving gate to taking off. The captain was warning that if he had to stop and restart the engines one more time, we'd have had to return to the terminal for more fuel.

However, the good thing was that I finished reading Down Under by Bill Bryson. I'm a real fan of Bill's writing and this book is no exception. It provides a wonderful and very humourous summary of his trips throughout a good chunk of Australia. Bill has a way of providing insights that is sorely lacking in most authors. I found myself laughing out loud many times while reading it. I also enjoyed the extra chapter at the end about his time growing up in the U.S. in the 1950s.

Highly recommended!

OT: Banks, security, IE7/Vista, password policies and CardSpace

I had to go through the signup procedure for direct account access for Bank of America today. It reminded me of how much nonsense gets perpetrated and how it's always done supposedly in the name of security. In the end, it usually achieves the exact opposite of what they are trying to achieve.

First, you need to generate a digital certificate to use. The bank has a sign-up site for this. After entering my details, I was greeted with:


I can't imagine anyone that would consider "Error 0x1AD generating certificate request" to be either friendly or helpful. Now I thought, hmm, this might be an IE7/Vista thing so I tried to run IE as an Administrator. No joy. Eventually I gave up and contacted support. So what was their recommended solution? You guessed it: install Firefox and use it instead !

I asked when the bank was going to support IE7 and Vista, given it was 2008 now and that was becoming a pretty common combination. They told me that there was a convoluted process that you had to go through to get it to work and that I might have to involve my IT department to get it to work. I'll take that as a "not real soon now" response.

So I gave in and installed Firefox. It was interesting to notice that my options for doing that included the GoogleToolbar. I suppose all those bundling and anti-trust rules should only apply to evil organisations like Microsoft 🙂

After doing that, I could then create the certificate, then back it up to a file and reimport it into IE7. So far so good. Then I needed to create a new password. I was a little surprised by this message:


I wonder which Einstein at the bank thinks that is a good idea. It reminded me of the Telstra Bigpond error message that told me my passwords couldn't be longer than 8 characters.

After removing the offending special character, I was then told I must have a number in the password. So I added a number only to be told that my password couldn't end in a number, unless it ended in two numbers. Heaven forbid they could have told me that in the first error message.

And so on and so on. I cannot imagine that policies such as this ensure anything except that I have to write down my new password as there's no chance I'll ever remember it. I'll bet they have a rule that says I can't do that either.

I had a related conversation with the Commonwealth Bank in Australia recently. They have a system where if you get the password wrong on a new online account three times, it *never* resets without you physically going into the bank. Again, this is done for security. I asked "but what if someone wrote a program to just try all the numbers? No new customer would be able to connect at all." They said "ah, no-one would do that surely".

It really is time for a system like CardSpace to become prevalent.


Sessions announced for CodeCampOz 2008

Sorry to all for the delay in working these out but we've now posted details of the sessions for CodeCampOz in 2008. You can view them on the "2008" tab at www.codecampoz.com.

There's a really great lineup of sessions. We also have two extra special guests: Jonas Folleso from Norway and Fernando Guerrero from Spain.

This year, there is also a precon event on Friday afternoon prior to the main event on Saturday and Sunday.

If you are planning on coming and have not already let us know, please email us at registration @ codecampoz.com to let us know.

SQL Server 2008: Interesting Full-Text Dynamic Management Function

I've been playing around with full text search in SQL Server 2008 and was intrigued by the DMF sys.dm_fts_parser(). It allows you to see the result of the word-breaking occurring within full text search. If you execute the query:

select * from sys.dm_fts_parser('"Hello Greg. How are you? I haven"t seen you for a while."',1033,0,0);

It returns the following data (along with some other columns):

occurrence  special_term     display_term
1           Exact Match      hello
2           Exact Match      greg
10          End Of Sentence  END OF FILE
11          Noise Word       how
12          Noise Word       are
13          Noise Word       you
21          End Of Sentence  END OF FILE
22          Noise Word       i
23          Exact Match      haven't
24          Exact Match      seen
25          Noise Word       you
26          Noise Word       for
27          Noise Word       a
28          Noise Word       while
36          End Of Sentence  END OF FILE

(15 row(s) affected)

That's really sweet as it lets you parse text into words. The 1033 was the locale I chose and the other two parameters were a stop word list (formerly called a noise word list) and whether or not it should be accent sensitive.

Arthur C Clarke made his final orbit of the Sun today

I was really sad to hear that Arthur C Clarke died today. For those that enjoyed his work, it's worth watching his "goodbye" video he made on the occasion of his birthday in December 2007. He knew at that time that the end was near. I particularly liked his choice of a Rudyard Kipling poem to read at the end. You'll find the video here: http://www.youtube.com/watch?v=eLXQ7rNgWwg

SQL Server 2008 – DMF: "On change – prevent" is the wrong name

One of the things I really don't like in the Declarative Management Framework is the term "On change – prevent" for the execution mode. Given we don't have INSTEAD OF DDL triggers and only AFTER DDL triggers, we can't *prevent* these things at all. All we can do is roll them back after they happen. I think the name should be changed to "On change – rollback" to reflect what's actually happening.
Also, if we ever get INSTEAD OF DDL triggers, we might then be able to prevent the actions. What then would the option be called?
And for some pressure on getting INSTEAD OF triggers for DDL, my previous item on that is here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299328
You know the drill: vote once, vote often 🙂

SQL Reporting Services: be very careful with using "c" formatting for currency

While on site this week, another common problem that I see everywhere arose again.

When you need to format currency, you use the "c" format right? It's in nearly every set of course materials I've ever seen.

In many cases, that's wrong!

Take a long hard think about whether you really want the monetary values that you display automatically converting to the client system's currency. If you are reporting financial figures, for example, is it really appropriate that they should mysteriously suddenly change from dollars to yen because the client is based in Zama City rather than in New York City? Not likely. A million dollars in sales might be good but a million yen in sales not quite so good 🙂

SQL Down Under show 35 – Roger Doherty – SQL Server 2008 for Database Developers

Hi Folks,


SQL Down Under show 35 with guest SQL Server DPE Evangelist Roger Doherty is now available for download from www.sqldownunder.com.


In the show, Roger discusses what developers are looking forward to in SQL Server 2008.







SQL Server Reporting Services: Avoid T-SQL in Reports

I spent time working with Reporting Services again today on a client site. The complexity of the reports we were working on reminded me that I really, really don't like seeing T-SQL scripts (or really any business logic) embedded in reports.

DBAs tend to be considered a conservative bunch. One thing they're usually conservative about is refactoring their databases. In many cases, this is because they have little idea what they will break when they make database changes. I've seen countless organisations that have allowed direct access to tables from client Access-based applications and have then lost control of the ability to ever change anything in the database because of the hundreds of little applications and reports that they might then break. I spend a lot of time talking to them about how to regain control of the situation.

Reporting Services is another area where this can happen. Fortunately, the reports are typically located in a single location. When a database change is needed, at least you don't then have to search the enterprise for reports that might break. However, life is much simpler if all the report does is call a stored procedure instead of having T-SQL embedded in it. I see the advantages of this approach as:

1. Refactoring the database is easier.

2. Unit testing of the code is much easier. You can easily build tests to just call the stored procedures. While possible via the web service interface, it's much harder to test the reports directly and requires a different skill set.

3. It allows the UI to be built by one person and the stored procedures to be built by another.

4. It's easy to deal with report permissions. Typically I create a Reports schema in the database and grant EXECUTE permission on that schema to the reporting users groups. That way, I don't have to manage individual permissions on the stored procedures and I don't have to grant permissions on the tables (or perhaps views).

5. I may well get benefits on procedure cache efficiency.

6. I can use the same stored procedure on multiple reports. I quite often find I have to write each report twice: once to look nice, the other to just dump the data into Excel format. Typically, when clients ask for Excel output, they just want the data, not the pretty formatting. I can use the same procedure for both versions. (Fortunately in SQL Server 2008, it looks like I'll have more flexible Excel rendering options).