Adam Howitt's Blog

User does not have permission to run DBCC TRACEON

I ran into an issue with a Microsoft Query based Excel work sheet a month ago and again just yesterday so I thought I'd share the workaround/fix here for my own sake and anyone else who is butting their head against a wall! The exact error message is as follows:

The issue, as I understand it, is that Microsoft Query uses Microsoft Excel or Microsoft® Excel as the app name in the query string when it tries to connect which causes SQL Server to check for DBCC permissions (permission to run commands in the console). This request to turn TRACEON for a production server is actually frowned upon in favor of a system wide setting at startup so we need a workaround.

In Excel 2010 for PC you can edit the connection string by going to the data tab > connections > edit your connection and then the second tab of that dialog box lets you edit the connection string. Replace APP=Microsoft® Excel; with your own app name e.g. APP=Workaround; and hit OK and close the connection manager. Refresh your data, enter your password and Bob's yer Uncle.

Cisco VPN on Lion Workaround Update

After a few months of booting into 32 bit mode, that got tiresome too. Shutting everything down, rebooting and then doing the work I needed before rebooting to go back to 64 bit again. Yawn. So two updates for you.

Faster switching

The first is that there is an easier way to switch between 32 and 64 bit mode without sitting holding keys down. You'll need to create a file called 32bit.sh in your home folder containing the two lines: This simply tells your mac to set the boot architecture to 32 bit and then starts the reboot. To use this make it executable by typing in a terminal window when you're in your home directory. You'll want to add a corresponding 64bit.sh file too and, again, make it executable.

To go to 32 bit mode, make sure you've saved everything you have open and then at the terminal type

If all has gone to plan you'll see it flash the commands on the screen and then your machine will reboot in 32 bit mode so you can start your VPN. Once you're done reverse the operation by running the 64bit.sh script.

No switches necessary

This one is a little more complicated but if you're looking for info about VPN fixes/workarounds I'm certain you'll keep up. You'll need the VPN remote desktop workaround I mentioned in the last post about VPNs. Once you have that working and have tested using the VPN while logged in remotely on RDC come back here for the next steps.

Okay, still with me. Next you'll need to install Cygwin on the Windows box and include the openSSH option from the list of packages. Lifehacker has a great setup guide for that.

Once you've got thru that install, test logging into the machine from your mac terminal window. You'll need the local IP address of the box running cygwin and your username. If your username has a space in it, at the command line you'll need to add a backslash before the space as an escape character e.g.

If that works you're almost there.

The final step in connecting your mac to the VPN is to find out which ports on the VPN machines you're trying to hit. In my case, the client has MySQL and SQL Server databases so that's 3306 and 1433 respectively. To connect to SQL Server I created another shell script in my home directory on the mac called clientNameSQLServer.sh and made it executable. The contents

obviously replacing xxx.xxx.xxx.xxx with the IP Address for the remote VPN SQL Server, replacing "My\ Username" with your username and the local IP address with your own IP for the machine with Cygwin. This script forwards any requests on localhost:1433 to the remote VPN IP address you provided.

To fire it all up you'll remote desktop to the windows box. Start the VPN and reconnect to the remote desktop if it boots you. If you don't get back to the desktop, something wasn't right. As long as you see the remote desktop launch cygwin to make sure it's up and running. On your mac, go to the command line in terminal and execute the shell script

. Now you can use your local Mac SQL Server client of choice, or even Microsoft Excel / Query to connect to the server. The only change to the way you would expect is that instead of setting the servername of the SQL server in your tool to the actual remote IP you use localhost:1433.

You're welcome.

Run a Linux process on a remote server and disconnect terminal session

I've been writing some nifty python scripts lately as we (WalkJogRun) migrate our route data from XML files to Google Maps encoded polylines. I finally whipped the script into shape and started pulling our route data from Amazon S3, encoding as SQL update statements and then rinse and repeat for nearly 500,000 legacy XML files. Trouble is, 500,000 files on my local machine would take about 20 hours.

Amazon AWS to the rescue! I span up a new Amazon EC2 medium size instance (high CPU) and pushed my script up to the cloud. The beauty of this is that the bandwidth between EC2 and S3 is free whereas my local machine relied on a crappy network connection and incurs the bandwidth charge on our S3 account. Ordinarily S3 bandwidth is pretty cheap but the script running on our EC2 machine is pulling down 5gb of data per minute and processing 1,000 files per minute. In around 8 hours we should have every legacy file processed and converted to a SQL statement we can run against our MySQL database.

But wait. My terminal timed out. Doh. If I login to the server over terminal and start the python script it is connected to my shell so if the connection breaks (on my crappy internet connection) the process is terminated on the server. The batch isn't as smart as it could be so it doesn't gracefully restart where it left off so I called on my friendly linux whiz Scott Frazer for a solution.

Scott directed me to the NOHUP command and after a few minutes of reading, a little typing and restarting my first batch I was able to disconnect my session thanks to this helpful command:

Roughly, nohup says don't hangup when I do but because my script outputs to the terminal as it runs the rest of the output needs to be redirected to files instead of the console.

If you run this command, exit and log back in you'll see the PTS/1 replaced by ? when you run the ps -fe command. I'm able to track the progress of my script because each iteration/file writes a line to the sql file it generates and the handy wc -l command tells me how many lines are in that file.

So thanks to Scott I'll have all my new shiny encoded polylines for our database and we can start to eliminate the need for the legacy XML files. The background there is that Google Maps on the iPhone and in the browser both support polyline encoding such that instead of a large xml file of latitude and longitude coordinates (some as big as 40kb!) I get an encoded string I can insert into the database each no more than 1000 characters.

Ugly MySQL tools for Mac AKA I hate Navicat

Seriously. Why should I be handicapped for trying to write SQL on a mac client? I've tried Navicat and hate it with a passion. For an OS that prides itself on pretty interfaces the tools for working with databases I've seen are all really ugly. MySQLYog for PC ain't no oil painting either but it embodies the important pieces of a SQL tool. Here's what I want - if anyone has any tips on tools, paid or free - I don't care:

1. Connection and object browser on the left. Drill down into tables to see column names, indexes, foreign keys, triggers. Navicat inconveniently makes you right click on the table name to view any of these details. When I'm writing a query I don't want to have to open a new tab to show an ugly list of table columns I can't refer to while I'm writing code without switching tabs.

2. SQL Editor windows with a split results pane that doesn't vanish if I edit one piece of a query. Navicat hides the SQL results if you change a query. Seeing sample data as I work on a query REALLY helps, especially since Navicat won't simultaneously show column specs (ref earlier point).

3. Edit stored procedure or views like I just wrote them. In every other SQL client I've used I right click a view or stored procedure to see the SQL stored in the database, preferably formatted. Navicat presents views as one long string of unformatted text and for stored procedures it offers an edit window for the inner section of the procedure but hides the implicit create statement and/or drop statement.

4. Right click ANY object to see a SQL script representing the object.

Is this too much to ask? I've tried running SQLyog in a VM but hate hogging so much memory to run a SQL client, not to mention the fact I routinely have issues authenticating my VM and host machine simultaneously on Starbucks' wi-fi.

A New Twist for Hierarchical Data

If you've ever had to work with a database model where rows of data represent hierarchical relationships then SQL Server 2005 has a hidden gem you may be interested.  Here are some domain examples to help you spot the type of problem this can solve:

1. Tasks: where a task may be part of a larger task or can be made up of subtasks
2. Bill of Materials: in a manufacturing environment where an item is made up of many other component items
3. Family trees: Father has a Grandfather and Children but all are people
4. Company Org Chart: the company org chart where employee Y is managed by employee X and manages employees z1,z2 and z3

Many ways to skin a cat
Before I introduce the old approach here are a few ways you might have handled it.  

1. Joe Celko's nested set model: don't be surprised if you haven't heard of this approach - it's generally acknowledged as the fastest way to access hierarchical datasets but the complexity of implementing it scares many programmers away.  Using this approach requires you to re-architect the way the data is stored, accessed, inserted and updated so if your database already exists this would be a big undertaking.  If your data is both wide and deep, and you routinely access the complete dataset then read one of Joe's many articles on nested sets or buy his book "SQL for Smarties".
2. Recursive stored procedures or functions: write a stored procedure or function to cursor over the children of the node you are looking at and then recursively call itself to go deeper in the recordset to build a table (need to write a function) or process each row (need to use a stored proc).  

The new rock and roll: recursive queries
The new feature of SQL Server 2005 is that it supports recursive queries.  It replaces the need to write a recursive table valued user defined function with a construct to allow you to quickly access the recordset you need to work with.  This recordset could then be returned by the procedure or used to drive a cursor to iterate over each node in the sub-tree in a particular order.

Enough talk - here is a concrete example based on the family tree notion:

What is going on here?
The WITH table_name(column_list) AS (QUERY) construct allows you to define an inline view and can be used for non-recursive purposes too but in this case it defines a base case and then joins against itself using temporary tables until there are no more joins to be made or a criteria is met (we could limit it to WHERE dateDeceased IS NOT NULL to find only deceased generations).  The first generation is the base case and represents our starting points in the tree. Note that this example will grab everyone who has no parent but we could just as easily have asked for a particular ancestor and found descendants of that person.

Still no clearer?
If our table family members contained 3 rows:
We could represent this with SQL as shown below but this only works for the top 3 levels.  Clearly this would be unmanageable for more layers.  This is very similar to the way the recursive query works - it builds each recordset working it's way down adding rows to a temp table and then using that temp table to get the next set of rows to add.

Performance Counts
This approach will work as fast, if not slightly faster than any other recursive approach.  As Steve Nelson told me yesterday - there is no magic going on here.  What it does is give you a more convenient way to write and think about recursive queries.  If you are looking for < 10ms calculation times all the time at any cost then the Celko way is the way to go.  If you write some use cases for your application and test this approach it may be that you are filtering the dataset enough to get great execution times anyway.  For example, in the family example we are interested in just one ancestor, the query times shouldn't be much slower than the Celko approach but if you are routinely looking for all people with no parent_personid for vast datasets then you will see big performance problems. As always, test each approach yourself and find out what works for you.

Profitable Online Marketing with Google Analytics

After 9 years of working for other people I've decided to go solo and start my own consulting business.  The site is rudimentary right now as I only handed in my notice today.  As regular readers may be aware, I've evolved from an entry level programmer to a Senior Application Architect in my current role since getting my Masters in Software Engineering and developed an interest in implementing Google technologies.  In my spare time I've been fostering a worldwide smash-hit, WalkJogRun.net, with over 4,000 visitors per day and 100,000 routes worldwide.  I'm addicted to Google Analytics and have a white paper due out under my current employers, Duo Consulting, explaining how to monitor the effectiveness of your marketing campaigns and tune the content on your website.

The last three years at Duo has been a fantastic experience for me giving me the freedom to explore exciting new technologies and all under billable hours because our clients are willing to try new things.  I've load tested and refined the Chicago Park District programs website to support as many as 3,000 transactions in the first 3 minutes of registration every quarter, debugged and load tested third-party ColdFusion CMS tools and built a Cicada Emergence Google Mashup in the last year alone.  I've implemented Google Analytics for many of our clients resulting in important business changing results.  Clients have paused underperforming ad campaigns, monitored the performance of redesign work, reallocated advertising dollars and even redesigned their shopping cart checkout process on the strength of tell-tale numbers through my analysis.

The primary focus of my new business will be helping clients to install Google Analytics, establish their goals for the websites and then monitor the effectiveness of their sites and the advertising campaigns they create.  I'll help them identify areas of their sites underperforming and evaluate their ad campaigns to get the most out of every dollar.  I have colleagues who can help with content development, Duo will be my first referral as far as deeper restructuring work and I can do development work too.  

The secondary focus for my business will be to load test web applications and either help developers benchmark the sites or discover the bottlenecks and resolve them.  I see this as a huge opportunity as the web continues to grow and web applications surpass the lowly traffic expectations they were initially conceived to handle.  In some cases the trouble will be at the database level, sometimes it is the JVM, sometimes it's an innocuous ColdFusion funciton like createUUID that hit the OS behind the scenes and crash JRun every 10 concurrent visitors.  
If you have read any of my posts in the past and value the expertise you have found, or if you have read this description and think you or someone you know would value from this kind of service, please pass on the link or get in touch.  I can be reached at http://www.adamhowitt.com.  Thanks for reading and stay tuned for more of the same tips, tricks and commentary.

See Howitt Run

I have 2 weeks left to raise the remainder of my $3,900 goal for Leukemia and Lymphoma and I need your help. I'm in Week 15 of the Team in Training plan to run the San Diego marathon on June 4th.  Saturday was an 18 mile run out in Darien, IL and my legs are still tight from the challenge.  It's a distinct change from the flat course along Chicago's lakefront.  We've got a 10 mile run this weekend before the next jump to 20 miles the following week.

I was initially running in memory of my Aunty Moreen who passed away in 2004 after a 6 year struggle with bowel cancer.  The weekend after I started my training we discovered that my wife's 3 year old niece Haidyn had been diagnosed with ALL (Acute Lymphoblastic Leukemia).  Over the past 12 weeks she has been a tough little girl fighting infections caused by the drugs reducing her immune response and dealing with the loss of hair caused by the chemo.  At mile 16 on Saturday as I felt like my legs couldn't keep going I remembered the pain her and her family have experienced and it pushed me to the end.

If you have ever read one of my posts on SQL, AJAX, ColdFusion or even my most popular post on how to fix a bug on your Motorola DVR, please take time to donate.  I appreciate anything you can spare.

When a NULL is not a NULL

I've just come across a little logic flaw in a SQL construct I have relied on for some time.  Needless to say I have some work ahead of me to find places I have used it and fixed it. 

To keep my stored procedures tidy but still allow optional parameters (like CF can deliver by using CFIF around the extra WHERE clause), I have been using this type of thing:

This code is supposed to work as follows - give me all rows where field_id in the row matches the passed in parameter @field_id but if the @field_id wasn't passed in was passed in as a NULL show all rows. It shows all rows because in all cases the field_id in the row should match itself, correct?
WRONG!  If field_id is actually a NULL value then the row becomes WHERE NULL = NULL.  I just ran some tests and was baffled but then remembered the rules of three valued LOGIC (where there are three possible values of true, false or NULL) - NULL compared to anything is always an unknown value.  This article gives it a good wrap:

After realizing my mistake the code now looks like this to achieve the same effect but correctly:

Hope that helps someone.

ColdFusion Development

Despite the bottomless pit of information available in Google, I find myself falling back on a core set of sites for the answers to my questions.  Through the magic of Google Co-op I have created a ColdFusion Development Search Engine.  It is a hand made list of sites I trust for answers to my ColdFusion questions.  I've also included and tagged JavaScript, SQL, Fusebox and AJAX resources with the idea that anything related to CF that I use on a daily basis to do my job is fair game.

If you can't find what you are looking for, let me know which site provided your answer and I can add it to the list after reviewing it.   

UPDATE: The URL for the search is http://www.webdevref.com 

Help with OO Problem

I've studied OO at University in the shape of Eiffel, I've programmed OO somewhat lightly in Java and developed OO in CFCs but there are some classes of problem I just don't get.  If anyone reading this could offer insight from their own experience that would be great!  I'll offer problem 1 for now and dependent on the response I might hit you up for problem 2.

"is related to"

I've called this "is related to" because I'm talking about the modelling of relationships between classes.  This comes in the context of how to use objects in a Content Management System.

For example, lets say I have a website with an about us section and in that section, an programmer list with a list of the key skills of each programmer.  There is also a "key skills list" which has a list of programmers who are in posession of the skill.  Programmers own skills but they aren't necessarily composed of those skills e.g. "has a" and this isn't inheritance since programmers aren't a type of ColdFusion.

So, to make it real, under the list of programmers I have Ben Forta, DHH, Adam Howitt, Sean Corfield and Cameron Childress.  If you view the detail for each programmer you will see that all but DHH have ColdFusion experience.  All have MySQL experience and lastly Adam and DHH have dabbled in Rails.  If I navigate to the ColdFusion page I see a list: Adam, Ben, Cameron, Sean with a brief snippet of the full bio under each.

On the ColdFusion page I would fetch the ColdFusion object and dutifully write out each attribute before getting the related programmers:

My question is how do you implement getProgrammers and what do the classes look like?  My initial thought is that there is a Programmer class and a Skill class.  Now for the chicken and the egg: does a programmer has a struct of skills (order isn't important) or does the skill have a struct of programmers (again, order not important).  Or both?  What do I store? The struct of class instances (programmer objects) or the struct of ids?  If I use a struct of objects, the call getProgrammers has all the information it needs to print this page.

If I store the ids of the objects then I increase cohesiveness by requiring the method to call a function to instantiate the skill class for each id. Storing the ids creates a new issue - I have to load each skill one by one, making multiple database calls for each page request instead of a single database call to load all related skills.  In the current system, I load all skilll attributes with one query, all related programmers with another then display them to the screen.  

The biggest issue I am having trouble understanding is what gets stored in memory.  We don't really have a Programmer or a Skills page, but model the employee structure and skills profiles of clients in the service industry.  Some clients have over a thousand employees with experience or skills selected from a list of over a hundred.  Adding up the potential number of objects, some clients may have over 5,000 content objects which may be called by any number of pages at a given time.  The current procedural approach works but we're trying to see if there is a better OO design to fit the purpose.