TechMixer University Wrap-Up

I can’t believe it’s been a week already since TechMixer University! Wow what a great time. It was our first year, and the last unofficial total I heard was 426 attendees, plus probably another 75 or so in speakers, vendors, and event volunteers. If you attended, I would love to hear from you, especially about the selection of speakers and subjects since I served as chair of the education committee. Shoot me an e-mail at arcanecode at gmail.com. I’ll keep your feedback confidential, but that feedback (good, bad, or ugly) will help us with next years event.

After the event each volunteer was recognized and presented with nice award. I thought the coolest award tough came from my nine year old daughter Raven. She took one of the leftover flyers, cut it up and made her own newspaper article. Here it is for your enjoyment.

Thanks again to everyone who participated, speakers, organizers, contributors and sponsors, and most of all the attendees.

Ted Neward goes well with cheesy stuffed burritos

I had quite the adventure last week getting to DevLink. Enroute Thursday night part of the electrical system in my old pickup decided to implode, leaving me stranded on the side of the interstate. I got it towed and had to wait for a friend to come get me (thanks Ben!). The closest place to wait was the Mexican Phone Company (aka Taco Bell). I claimed the booth with the wall outlet, setup my laptop, and settled in for 3 hours of waiting. Naturally there was no wi-fi to be found.

Always looking for opportunities to be productive, I worked on editing a Camtasia video I had recorded recently at a Bug.Net meeting. After a bit I decided to take a break and eat. While munching on a cheesy stuffed burrito I watched some videos I had downloaded from the InformIT site. In the first video Ted Neward talked about functional coding and touches on F#, in the second he dives deeper into F#. Once the videos and my cheesy stuffed burritos were done I returned to editing.

So the next day I got my wife’s van out of the shop (transmission had gone out leaving CodeStock – these conferences are getting expensive!) hopped in and took off once again for DevLink, this time making it just in time to see the last session of the day – none other than a presentation by Ted Neward and Amanda Laucher on F#. And I’ll be dog gone if Ted wasn’t wearing the exact same t-shirt he had one while filming the videos I’d watched. Made the experience that much more surreal. Either that or the cheesy stuffed burritos were haunting me, one of the two.

I did at least get one whole day in at DevLink, still well worth going to. And I’m not just saying that because I won a copy of Vista Ultimate and Master Chiefs decapitated head. Oh and if you are looking to learn a little more on F#, Ted’s got a great article in the Sept/Oct 2008 issue of Code Magazine called F# 101. Good reading.

All in all, despite the vehicular issues DevLink was still a good value and I plan to make it an annual trip.

The Importance of a Good User Interface

Sure, everyone would like a good UI, but most developers simply have not had the training to implement a usable user interface. I’m not necessarily talking about something dazzling and good looking, no I’m talking about USABLE. Think user interfaces aren’t that important?

Last night on Twitter, @djuggler mentioned John Denver. My wife is a huge John Denver fan. Right after we got married, I took her to a John Denver concert. We got all dressed up, did the nice dinner then I surprised her with some great seats. We were on the front row, if you exclude the seats that were down in the orchestra pit. I was especially glad I spent the extra bucks as it turned out to be his last concert in Birmingham before he passed away.

@djuggler ‘s tweet made me curious what year he’d passed away. In reading the Wikipedia article I found an interesting link to an article on ASK TOG. It was titled “When Interfaces Kill: What Really Happened to John Denver”. I’ll let you read the article for the full details, but the short version is the fuel tank toggle switch was poorly designed, and in a location that forced the pilot to remove his hands from the controls. Doing so caused John Denver to lose control and, well, that was that.

I hope this brings to light how important a good UI can be. At the risk of this sounding like a lame lead in to a commercial, I will mention tonight’s BSDA meeting. Shannon Brooks-Hamilton is coming to speak to give us a User Experience Overview. From her extract:

“User Experience” is a person’s overall experience and satisfaction when using a product or service. This presentation will give an overview of the practices, methods, and outcomes of User Experience with an emphasis on tips for great interface designs. The benefits of User Experience include increased product usage, increased e-commerce sales, increased user productivity, decreased support costs, decreased training costs, and fanatical customer loyalty. Google, Apple, and IDEO are examples of companies that embrace User Experience.

We’ll be meeting at 6:30 pm on August 14^th at the New Horizons Training Center in Homewood, hope you can make it!

SQL Server Compact Edition Connection Strings

In my recent presentation I talked about an important but subtle difference with connection strings when using SQL Server Compact Edition. It was so important I thought I’d make a special blog post out of it.

There are two methods for programmatically accessing data in SQL Server Compact Edition (SSCE). The first method is using the System.Data.SqlServerCe library. When you create an instance of the SqlCeEngine, you need to pass a connection string formatted like so:

DataSource=”mydatabasename.sdf”; Password=’mypassword’

This method is valid, by the way, for version 3.1 or 3.5 of SSCE. The second method, available with Visual Studio 2008 and the 3.5 version of SSCE is to use LINQ to SQL. When creating the DataContext object, you also need a connection string formatted like so:

Data Source=mydatabasename.sdf; Password=’mypassword’

Note very carefully the two differences. First, the name of the sdf file lacks the double quote marks in the LINQ to SQL version. Second, note the Data Source phrase has a space between the words in the LINQ version, where the SqlCeEngine version lacks the space.

It’s a small distinction, but it’ll drive you nuts if you don’t catch it. I drove myself nuts for quite a while because I didn’t notice the extra space in Data Source when I began experimenting with LINQ to SQL! Hopefully my pain will save others some hair pulling.

CodeStock

I was at CodeStock in Knoxville over this previous weekend. Great conference. It was run very well, everything went quite smoothly. I attended some really great sessions, and got to meet a ton of great folks.

The twitter get together the night before at the hotel was awesome. It was sort of weird, a lot of the folks I have known on twitter for several months now, but this was the first time we got to meet face to face. As I told my wife, “I got to see a lot of old friends for the very first time and catch up on old times.” We got into some really interesting discussions on development topics, user group activities, and just had a lot of fun in general.

I attended a lot of good sessions during the day, but want to point out a few in particular. The first was put on by Amanda Launcher and Joe O’Brien. They talked about alternative languages such as Ruby and F#, and why learning them was important. They pointed out that alternative languages help you think about different approaches to problem solving. Gave us a lot to think about, and has me eager to dive into those F# books I bought.

The other two sessions were on LINQ. Joe Kunk did a LINQ to SQL session. His stated goal was to give us enough info to return to the office and write our first LINQ app. This combined with Jim Wooley’s session on LINQ Migration Strategies did just that, I sat down Sunday and, I admit with the help of a few books, in a few hours had written my first LINQ code to talk to SQL Server Compact Edition.

I thought CodeStock was a great investment of my time, and am looking forward to returning next year.

Presenting Getting Started with SQL Server Compact Edition 3.5 at BUG.NET Meeting

Just wanted to let everyone know I’ll be doing a presentation this coming Tuesday night, August the 12th for the Birmingham .Net Users Group (BUG.NET). My topic, as you may have guessed from the title, will be using SQL Server Compact Edition.

While I will be using Visual Studio 2008, I will point out which pieces are 2005 compatible. I will also cover the use of both traditional coding techniques as well as how to use LinqToSQL to talk to the Compact Edition.

The meeting takes place at 6:30 pm at New Horizons Training Center in Homewood.

I also plan a new series of blog posts to start later this week on the subject, and will be creating a new Code Gallery site to hold my examples.

Also, don’t forget the regular BSDA meeting this coming Thursday night, the 14th. Also starting at 6:30 pm at New Horizons, Shannon Brooks-Hamilton, a software usability expert, will be there to talk about user interface design. Lots of good thought material on how we can make better UIs for our users.

SQL Server 2008 RTM!

It’s here it’s here! SQL Server 2008 RTM is now on MSDN. I’m downloading it now. Only downside, to use Visual Studio 2008 with it you need VS2008 SP1, and that hasn’t released yet. So be cautious if you install and desire to use VS2008 with it.

Arcane Review: Expert SQL Server 2005 Integration Services

If you recall my “Good Reads” post from June 25^th, you will remember I am a big believer in books as a learning medium. I like to employ a lot of different ways to learn: user groups, blogs, podcasts, videocasts, and magazines to name a few. But for really in depth coverage, it’s hard to beat a nice book in your hands. I got some good feedback from my mention last week of Andy Leonard’s new e-book on Data Dude, so I thought that I would continue by adding book reviews to the blog every so often.

For this review I thought I’d cover a book that seems to constantly be on my desk lately: Expert SQL Server 2005 Integration Services, by Brian Knight and Eric Veerman. This book does a really good job and is specifically targeted toward the data warehousing professional. One entire chapter is devoted to ETL for dimension tables; another chapter focuses on the fact tables. It was great to have coverage so focused on these topics.

Another favorite part of the book is the two chapters on deploying and managing SSIS packages. So often these topics are glossed over, especially the managing piece. The book does a great job in covering all the tools and practices around this subject. I’ll mention one more chapter, one that focuses on package reliability. They cover logging, auditing, event handling, checkpoint files, and even suggestions on testing error handling logic.

There are many more chapters in the book, such as migration from DTS (SQL Server 2000) and Scalability, for you to discover. The other thing I love about this book is the brevity. The authors cover an amazing amount of information in just 382 pages. As a busy, busy person I very much appreciate the conciseness they achieved without sacrificing any clarity.

I’ve met both authors, and have heard them speak. They are both very nice, knowledgeable individuals, and I highly encourage you to attend one of their presentations if you get the chance, or if not at least buy their book from your favorite retailer; you will find it a great investment.

Deep Fried Debugging

I was listening to the current episode of Deep Fried Bytes and was reminded of an important lesson. In case you haven’t heard of it, Deep Fried Bytes is a relatively new but very good development podcast. I highly recommend the podcast, it’s become a favorite on my Zune.

The hosts, Keith and Woody were interviewing members of the Microsoft.com support team. Yes, the guys who keep the actual Microsoft.com website up and running. ~~Keith~~ Woody asked them about a really challenging problem they hand, and one of the team recounted the tale of a site that had been in production about a year, when performance suddenly tanked. Naturally they went through the standard debugging questions, including “has anything changed in the code?” Since nothing had, they said “oh, well can’t possibly be the code” and went on to look at other things.

They went on to look at other things before finally, in desperation, coming back to the code. It turned out there was a scalability bug that had been there since day one, buried deep in a stored procedure. The select statement inside the stored proc caused a table scan. Not so bad when there were few records but after being up for a year the number of records was bogging down the stored proc.

I have been on many projects where a developer insisted the bug couldn’t possibly be in the code as it’s been running “perfect” and no recent changes have been made. The lesson to learn is never to rule out anything when looking for bugs. True, you should start with the most likely suspects, if no changes have been made to code then the probabilities of it being code are low as compared to say a hardware issue, but don’t rule it out completely. Get the entire team working in parallel. Let the developers look at the code, the DBAs at the database, admins at the server and network, and so on. Through teamwork, and being open to all possibilities you can achieve some deep fried debugging.

Getting Tagged by the Software Developer Meme

There’s a “meme” going around the net. A meme, for those unfamiliar, is defined as a unit of cultural information, such as a practice or idea, that gets transmitted verbally or by repeated action from one person to another. On the web, a meme is a theme, usually a series of questions that get passed from one person to another. After one person answers, he tags one or more other folks. Well, I got tagged!

StatisticsIO, better known as Jason Massie, got me. To keep his link chain alive, this has now gone from: Denis Gobo > Andy Leonard > Frank La Vigne > Peter Brown > Chad Campbell > Dan Rigsby > Michael Eaton > Sarah Dutkiewicz > Jeff Blankenburg > Josh Holmes > Larry Clarkin > Jason Massie > Me! So without further ado…

How old were you when you first started programming?

12 or 13, it was on a TRS-80 Model 1.

How did you get started in programming?

My dad had written a Star Wars game programming in Basic on the TRS-80. I hacked it so I could beat my sister most of the time, and the rest was history.

What was your first language?

BASIC, of course.

What was the first real program you wrote?

As I recall, it was a character generator for Dungeons and Dragons back on the TRS-80. Involved a lot of random number generation and printing.

What languages have you used since you started programming?

In no particular order: BASIC, Pascal, Quick Basic, Visual Basic, COBOL, C, C++, C#, Delphi, Fortran, dBase, FoxPro, RPG III, a little assembler, probably some more I can’t recall. Working on learning Powershell and F# now.

What was your first professional programming gig?

I guess it depends. A friend of mine and I co-wrote an inventory system for someone who wanted to start a company. It was written using compiled BASIC 1.0, and the software and DOS had to fit on one floppy disk, then the inventory for the store had to fit on a second floppy. Unfortunately they went under before we could get paid. I then went on to write a dBase II system for a lawyer to organize some charity or other, that was the first system I actually got paid for.

If you knew then what you knew now, would you have started programming / DBAing?

Oh yes, love it! There’s something rather intoxicating about making the computer sing and dance to your whim.

If there one thing you learned along the way that you would tell new developers, what would it be?

Can’t decide on one, so there are two things I’d share. First, as much fun as coding is, never forget you are there to solve a problem. Ultimately it’s not about you but about the user experience. Don’t be afraid to subjugate your ego to the success of the project, ultimately it’ll pay.

Second, take time for the peripheral skills. Communications, business, etc. These will make you far more valuable as a professional developer than technical skills alone.

What’s the most fun you’ve ever had … programming?

Hmm, tough call. Believe it or not I used to work for a prophylactic factory. It was a lot of fun coding the interfaces between the machine that printed the serial number on each one, and the production database.

Either that or right after we got married I spent close to two years working from home. My wife would sometimes sit in my lap and snuggle up while I was able to reach around her and keep coding. Distracting perhaps, but the question was about fun not productivity!

Who are you calling out?

Hmm, let’s see, that’s a tough one since this meme’s been around a while. Let’s annoy…

MaggiePlusPlus

Rachel Appel

Amanda Launcher (AKA Pandamonial)

Michael Neel (ViNull)

Dougal Campbell

Paul Waters

Wow, looks like there are still some ~~victims~~ developers left after all…

Interview with Andy Warren on his Birmingham Visit

After the SQL Saturday event Inbetween the TechEds I had a moment to interview SQL Server guru Andy Warren about his upcoming trip to Birmingham Alabama. Andy will be speaking at the Steel City SQL group on June 17th and holding an all day class on on Performance Tuning on June 18th. You can find my interview with him, in MP3 format, here:

Andy Warren Interview

Note the audio is raw and uncut, but will give you all of the information you need.

SQL Server Migration Assistant

At work we have an Oracle based system we’ll be retiring sometime next year. We want to keep the data around for reporting, but don’t have the manpower or funds to properly flatten the database into a true data warehouse schema. We are leaning then to copy the data into our SQL warehouse database in almost a direct copy of the legacy’s schema with just a few minor tweaks.

In looking around for an efficient way to handle this, I found a tool called the SQL Server Migration Assistant for Oracle. This handy tool will copy everything, tables, views, stored procedures, triggers, just about everything you’d want.

In my case, all I really want is the table layouts. I will be making a minor tweak to the table layouts so I can combine four databases into a single one. In just a few hours, I was able to use SSMA to generate create table scripts for several hundred tables. I then brought the script into my favorite text editor, UltraEdit, and used it’s regular expression capability to add a source database field as the first field in every create table script. Saved ’em, ran it and in short order have a complete schema ready to hold my data.

Even using the SQL Server Migration Assistant in such a limited fashion, I was still able to save myself weeks of coding and trying to figure out how to manually map Oracle datatypes to SQL Server. If you are looking at any kind of data conversion take a look at this tool. You can find more info on the SSMA site at:

http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx

Birmingham Tech News and Events

There are some big doings going on in the event community over the next few weeks.

First off, this Thursday May 8th at 6:30 pm the Birmingham Software Developers Association (BSDA) will be having “The Variety Show”. Join us for a variety of 15 minute presentations by various club members on what they’ve been working on lately–there should be something for everyone and the floor is open for anyone who’d like to do a short presentation.

On Friday, May 9th the IPSA group will meet during lunch at the McWane center, the topic will be The Social Media Toolbox.

Next week, the Birmingham .Net User Group (Bug.Net) will be having it’s regular meeting on May 13th at 6:30 pm. Stay tuned to their website for speaker and details.

Then, on Wednesday, May 14th at 6:30 pm the BSDA and Bug.Net are pleased to co-present a special event. Regional speaker Michael Neel will be here to talk on DataSets:

DataSets are Evil. They will hog your CPU, steal your RAM, and rob your home. This is the story surrounding DataSets, but what is fact and what is myth? In this session we will look at DataSets and the tools that go with them to see how they can save you development time while not crashing the server. We’ll also dive into DataSets in 2008 with LINQ to DataSets and Unit Testing with DataSets.

Learn more about Michael at vinull.com/profile

Finally, beginning at 5:30 pm on May 20th the Steel City SQL Group will meet. MVP Kevin Bowles will be here to talk about SQL Server 2008 Development. Kevin is a great speaker, his sessions are always loaded with useful information.

With the exception of the IPSA meeting, all of the other events will be held at the New Horizons training center in Homewood. A special thanks to the folks at New Horizons for making their facilities open to the Birmingham user group community!

SQL Server Full Text Search – The ObjectPropertyEx Function

It can be quite useful to have a query that will list all of the tables in your database that currently hold full text indexes. Fortunately there is a function we can use, ObjectPropertyEx. We’ll combine it with a system view named sys.tables which gives us a list of all the tables in the current database.

Note, for todays example I will continue to use the AdventureWorks database, AdvWorksCatalog, and index I created in yesterday’s post on the FullTextCatalogProperty function.

ObjectPropertyEx takes two parameters. The first is the object ID of the table we want to work with. That’s why using sys.tables view is so nice, it already containts both the name of the table and it’s object ID. The second parameter is the name of the property we want to get, in this case TableHasActiveFulltextIndex will tell us if the table has an FTS (Full Text Search) index or not. Here’s a query that will list all of the tables with a full text index:

select t.[Name] as TableName
    , ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) as IsFullTextIndexed 
from sys.tables t 
where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1
order by t.[Name]

TableName IsFullTextIndexed
——————– ——————
ProductDescription 1

This returns a list of the table name, and the value of 1 indicating the table does have a full text index on it. To get a list of all tables, simply omit the where clause. Tables without an full text index will have a 0 for the IsFullTextIndexed value.

We can further extend this function by using a second property, TableFulltextItemCount. This will tell us how many rows exist in the full text index for this table.

select t.[Name] as TableName
    , ObjectPropertyEx(t.[object_id], ‘TableFulltextItemCount’) as NumberOfRows 
from sys.tables t 
where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1
order by t.[Name]

TableName NumberOfRows
——————- ————-
ProductDescription 762

In this case the number of rows matches the number of rows in the table.

This is because I have change tracking set to auto, and have a speedy system. Be aware there are times when this number won’t match the row count. This would primarily be when you have change tracking set to manual, have inserted new rows in to the source and have not issued the command to force the update of the full text index.

By comparing the TableFulltextItemCount to the number of actual rows in the source table, you can monitor your system health and activity. If you see the numbers grow ouside the normal boundaries, it can be an indication something is wrong. You can also use it for adjusting the frequency of manual update commands. If the numbers grow apart too much for your comfort, you may wish to make the updates occur more frequently. Conversely, if most of the time the numbers match you may wish to reduce the frequency of manual updates.

It’s not absolutely required to go to the sys.tables in order to get the count. If you already have the name of the table, you can use it in combination with the object_id function to get the count for that one table.

select ‘Production.ProductDescription’ as TableName
    , ObjectPropertyEx(object_id(‘Production.ProductDescription’)
                        , ‘TableFulltextItemCount’) as NumberOfRows

TableName NumberOfRows
——————- ————-
ProductDescription 762

Using the above logic you could easily create a function to pass in the name of a single table and get the number of rows in the full text index.

As you can see, using ObjectPropertyEx with its two simple full text search related properties will give you some useful functionality for monitoring your system.